I was always curious about where and how PostgreSQL stores data in files, and today I spent some time exploring the internals … ⚡
Physical files (present in the PGDATA directory) are called Forks and PostgreSQL splits the data into multiple forks to manage and optimize different aspects of data storage and retrieval. The three types of forks are -
- Main - primary fork where the actual table data is stored
- Free Space Map - keeps track of the free space within the main fork
- Visibility Map - records which pages in the main fork contain only tuples that are visible to all active transactions.
The file grows over time, and when its size reaches 1GB, another file of this fork (called segment) is created and the sequence number is added to the end of its filename. The limit can be changed while building PostgreSQL.
Each row is stored in a data page (~8 KB in size but configurable), and these pages are linked together to form the complete table. When inserting new data, PostgreSQL first consults the FSM fork to find pages with enough free space. It then writes the new row into the appropriate page in the main fork and updates the FSM.
Note: In PostgreSQL, the physical order of rows on the disk can differ from the logical order defined by the primary key. To physically arrange rows on a disk according to the order of an index (such as the primary key), PostgreSQL offers the CLUSTER command.
Updates are treated as a combination of insert and delete operations. PostgreSQL inserts the new version of the row into the main fork and marks the old version as obsolete. The FSM and VM forks are updated to reflect these changes.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
youtube.com/c/ArpitBhayani