How To Dev

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

Introduction As mentioned previously regarding storing data into HEAP, Postgres enforces a strict rule where a Row/Tuple must fit entirely within a single Page (8KB) and cannot overflow into another Page. The maximum size of a Row ranges from approximately 2KB to 8KB. If you intentionally insert a very long TEXT value or a file of several MBs into a row, an 8KB Page cannot accommodate such large data, prompting Postgres to trigger a mechanism called TOAST (The Oversized-Attribute Storage Technique). When you insert a data row whose size exceeds the allowed threshold (typically around 2KB), Postgres will not insert the entire row into the main HEAP. It executes the following three steps: Data compression: First, Postgres attempts to compress the oversized data to see if it fits within the 8KB Page. If successful, it is still inserted into the main HEAP. Chunking and moving to TOAST: If the data remains too large after compression, Postgres splits that 5MB data into multiple small chunks...