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 These are Query Tuning techniques based on common errors encountered when querying data and the process you can apply: Avoid Using SELECT * When you use queries like this SELECT * FROM users WHERE id = 100 SELECT username FROM users WHERE id = 100 If there is no Index , Postgres will have to use Heap Scan to load the corresponding entire data page into RAM At this point, whether you use SELECT * or SELECT name , Postgres handles it the same way But the difference starts here, if you only SELECT the necessary columns, Postgres only needs to process those columns without spending additional CPU cost to process all columns as with SELECT * If any of the requested columns are too large and stored in the TOAST Table , it incurs additional I/O and data decompression costs At the same time, if you deploy a Web Server to respond with this data to FE , larger amounts of data will consume more network bandwidth, RAM to parse JSON and take longer to process Solu...