How To Dev

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

Introduction This article will provide some basic concepts regarding the internal mechanics of PostgreSQL as a foundation for explanations in future articles. Heap First of all, it is necessary to distinguish the Heap in Postgres from Max-Heap or Min-Heap in data structures and algorithms, as well as Heap memory in RAM, they share the same name but are completely different. The meaning of the name Heap in Postgres signifies a jumble or a lack of order, because row data (called tuples) are stacked on top of each other randomly. In PostgreSQL, the Heap is the main storage area containing all the actual data of a table. When you create a table and INSERT rows of data, Postgres writes those rows into the Heap. Regarding storage, the Heap is simply one or more binary files created by Postgres when you insert data into a table. This Heap file is stored on disk and has a default limit of 1GB per file, if there is more data in the table, it will be split into multiple files for storage. Wherev...