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 Covering Index is used to enable Index-Only Scan with Non-key Columns , initialized using the INCLUDE clause PostgreSQL supports three types of indexes for this feature: B-Tree (Most common), GiST and SP-GiST All other index types ( GIN, BRIN, Hash ) do NOT support the INCLUDE clause. How it works When you create an index with INCLUDE as follows: CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_amount, status) Main column (user_id): Located at the upper nodes and leaf nodes of the B-Tree. Used for searching, filtering ( WHERE ) and sorting ( ORDER BY ). Payload columns (total_amount, status): ONLY stored at Leaf nodes. They do not participate in tree sorting or searching operations. The main purpose is to help Postgres perform an Index-Only Scan , meaning Postgres retrieves data for total_amount and status directly from the index without reading the Heap Page , making the SELECT query execution extremely fast. Advantages Avoiding placing those colu...