How To Dev

Introduction How to handle data changes Postgres uses the MVCC mechanism, meaning when data changes occur, Postgres still retains old data versions and INSERT s new data DELETE : when deleting rows, Postgres only adds a flag to that row called Dead Tuple indicating that this row is no longer used This means that row still exists on disk and is not completely deleted, so it will need cleaning up When querying, it will be automatically ignored and will not return results INSERT : when adding a row Postgres will search for empty space in existing pages to INSERT new rows Or create a new page, if empty pages are exhausted Or override data of Dead Tuple rows according to the Free Space Map UPDATE : when an UPDATE is needed, it will DELETE the old row and INSERT a row with new data TRUNCATE is an operation belonging to the DDL (Data Definition Language) group, not DML like DELETE . Instead of marking Dead Tuple for each row, Postgres will completely delete the table's data fi...

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