How To Dev

Introduction Aggregate Functions are functions that perform a calculation on a set of values (multiple rows) and return a single value. They are often used with the GROUP BY clause to group data, or combined with OVER to become Window Functions to calculate without losing detailed rows. Postgres natively supports Aggregate Functions which can be divided into the following groups: Basic Aggregate Functions SUM(column) : Calculates the sum of all numeric values in the column (ignores NULL). AVG(column) : Calculates the average value of the column (ignores NULL). COUNT : Counts the number of rows COUNT(*) counts all rows, including rows with NULL values COUNT(column) only counts rows with non-NULL values. MAX(column) : Finds the maximum value in the column, works with numbers, strings and dates. MIN(column) : Finds the minimum value in the column. NULL Handling Functions COALESCE(val1, val2, ..., valN) : Returns the first non- NULL value in the input list. This function is often pair...

Introduction In Postgres , OLTP and OLAP are two completely different database system design philosophies serving distinct purposes. Postgres itself is an extremely powerful relational database management system (RDBMS). By default, it is highly optimized for OLTP  and thanks to its rich ecosystem of extensions, Postgres can also fully support OLAP workloads. Here is the detailed difference between these two concepts: OLTP Online Transaction Processing : focuses on fast, accurate and secure processing of a large number of continuous financial or operational transactions from end users. Data characteristics: Data changes constantly ( Insert, Update, Delete continuously). Query Pattern : Read/write statements acting on one or a few specific data rows (for example, finding info of a specific customer with WHERE id = 123 ). Advantages Data integrity ( ACID ): Postgres guarantees absolute transaction integrity without errors or data loss, thanks to its locking mechanisms and MVCC ...