How To Dev

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...

Introduction In SQL and specifically in PostgreSQL, there are 4 types of Joins as follows: INNER JOIN: Only retrieves records that have a match in both tables. LEFT JOIN: Retrieves all records from the left table, and if there is no match in the right table, the values are set to NULL. RIGHT JOIN: The opposite of LEFT JOIN, rarely used because it can be rewritten in reverse using LEFT JOIN. FULL OUTER JOIN: Retrieves all records from both tables, filling with NULL where there is no match. Nested Loop Join When executing a join, the Postgres Optimizer automatically performs an analysis based on the datasets of the 2 tables to select the most efficient and suitable algorithm for the current situation First, let us look at the Nested Loop Join. This is the most basic join algorithm, and its operational mechanism is very straightforward: it takes each item from one table to compare it with every item from the other table, acting exactly like two nested for loops in programming Use cases Th...