PGSimCity: Deconstructing PostgreSQL Engine Architecture from Heap Pages to MVCC

Introduction: The Database Engine as a Micro-Metropolis

When developers issue a query like SELECT * FROM users WHERE id = 42;, PostgreSQL feels like a simple black box that returns rows in milliseconds. Underneath that clean declarative SQL interface, however, lies one of the most sophisticated industrial systems in modern software engineering. Much like a sprawling city with zoning laws, transit corridors, sanitation systems, and emergency services, PostgreSQL operates through tightly coordinated subsystem components.

To build software that scales reliably under intense concurrent workloads, you cannot treat the relational database as a magic storage layer. You need to understand its lower-level mechanics. In this article, we will dissect the architecture of PostgreSQL from the low-level 8KB disk page layout to Multi-Version Concurrency Control (MVCC), the shared buffer pool, and the Write-Ahead Log (WAL).

Anatomy of a Postgres Page: The 8KB Physical Grid

At the foundational physical level, PostgreSQL does not store rows as loose, variable-length text streams. Every table (heap) and index is broken down into fixed-size physical blocks called Pages, which default to exactly 8192 bytes (8 KB).

When a table grows, PostgreSQL simply allocates additional 8KB pages to the file on disk. Understanding how data is arranged inside an 8KB page clarifies why certain table schemas trigger massive write amplification and poor cache utilization.

+-------------------------------------------------------------------+
| PageHeaderData (24 bytes)                                         |
+-------------------------------------------------------------------+
| ItemIdData[0] | ItemIdData[1] | ItemIdData[2] | ... (Line Pointers)|
| -------> (Grows Downward)                                         |
+-------------------------------------------------------------------+
|                       <--- Free Space --->                        |
+-------------------------------------------------------------------+
| ... (Tuple Storage) | HeapTupleData[1] | HeapTupleData[0]         |
|                     (Grows Upward <-------)                       |
+-------------------------------------------------------------------+
| Special Space (Index-specific metadata, e.g., B-Tree links)       |
+-------------------------------------------------------------------+

Key Components of a Page Header

PageHeaderData (24 Bytes): Contains internal metadata such as pd_lsn (Log Sequence Number for crash recovery), pd_lower (byte offset where line pointers end), pd_upper (byte offset where tuple storage starts), and pd_special (used for index structures like B-Trees).
Line Pointers (ItemIdData): An array of 4-byte pointers starting immediately after the 24-byte header. These pointers grow downward toward the end of the page. Each pointer stores an offset to the actual tuple data and its length.
Tuple Data (HeapTupleData): The physical record contents, stored from the bottom of the page growing upward.
Free Space: The unallocated byte region squeezed between the downward-growing line pointers and the upward-growing tuple records.

Because line pointers remain at static array indices while physical tuples can be shifted around during page compaction, Postgres internal pointers (termed ItemPointer or ctid) consist of a tuple containing (BlockNumber, OffsetNumber). This indirection ensures that pointer stability is maintained without re-indexing external data structures when a page is defragmented.

Multi-Version Concurrency Control (MVCC): Parallel Timelines

One of PostgreSQL's standout features is its implementation of Multi-Version Concurrency Control (MVCC). Unlike database engines that rely heavily on read/write locks that block readers during active updates, PostgreSQL ensures that readers never block writers, and writers never block readers.

It achieves this isolation by maintaining multiple physical versions of the same logical row simultaneously.

The Tuple Header Overhead

Every raw tuple inserted into a Postgres table carries a hidden header structure called HeapTupleHeaderData, consuming 23 bytes of overhead per record before accounting for your actual column payload. Crucial fields inside this header control tuple visibility:

t_xmin: The Transaction ID (XID) of the transaction that inserted (created) this version of the row.
t_xmax: The Transaction ID (XID) of the transaction that deleted or updated this tuple. If the row is active and un-deleted, t_xmax is 0 (invalid).
t_cid: The Command Identifier within the transaction, preventing intra-transaction visibility anomalies.
t_ctid: The physical location (page, offset) of this tuple or the newer version of this tuple if it was updated.

Update Workflows in Action

When you execute an UPDATE command in PostgreSQL:

PostgreSQL does not overwrite the existing bytes on disk in-place.
It marks the existing tuple's t_xmax with the current transaction ID (effectively deleting it from future transaction snapshots).
It writes a brand new tuple with its t_xmin set to the current transaction ID and updates the old tuple's t_ctid to point directly to the new physical tuple.

-- Logical view of tuple versions during an UPDATE transaction
Tuple V1: [xmin: 101, xmax: 102, ctid: (0, 2), data: "Alice"]
Tuple V2: [xmin: 102, xmax:   0, ctid: (0, 2), data: "Alice Smith"]

If Transaction 103 queries the table, PostgreSQL reads the active snapshot, checks whether Transaction 102 committed, and compares t_xmin and t_xmax against the transaction snapshot rules. Transaction 103 sees Tuple V2 while ignoring Tuple V1.

HOT (Heap-Only Tuple) Optimization: Eliminating Index Bloat

Because every UPDATE creates a new tuple, updating a column that participates in secondary indexes traditionally requires updating every single index attached to the table. This results in severe index bloat and high write amplification.

To solve this, PostgreSQL implements Heap-Only Tuple (HOT) Optimization.

A HOT update occurs if:

The UPDATE statement does not modification any indexed columns.
The new version of the tuple fits into the exact same 8KB page as the old version.

When these conditions are met, Postgres avoids inserting new index entries altogether. Instead, it marks the line pointer of the old tuple with a HOT_UPDATED flag. When an index scan lands on the old line pointer, the engine follows a direct internal pointer chain inside the page to locate the new version without consulting secondary B-Tree indexes again.

The Shared Buffer Pool and the Clock Sweep Algorithm

Direct disk I/O is orders of magnitude slower than system RAM. To achieve high performance, PostgreSQL manages an internal cache known as the Shared Buffer Pool (configured via shared_buffers).

When a backend process needs a tuple:

It calculates the page hash and checks the Shared Buffer Lookup Table.
If present (a buffer hit), it pins the buffer frame and reads the tuple.
If missing (a buffer miss), it allocates a free buffer descriptor, reads the 8KB page from disk into RAM, and proceeds.

Buffer Replacement: Clock Sweep Algorithm

When all buffer frames are filled, Postgres must evict a page using a variation of the Clock Sweep (Second-Chance) eviction algorithm.

Each buffer descriptor holds a usage counter (ranging from 0 to 5):

               +---> [ Buffer 1 | Usage: 3 ]
               |     [ Buffer 2 | Usage: 0 ] <--- Clock Hand Points Here
   Clock Sweep |     [ Buffer 3 | Usage: 1 ]      (Evicts Buffer 2!)
   Mechanism   |     [ Buffer 4 | Usage: 5 ]
               +---------------------------+

The clock hand iterates over the array of buffer descriptors.
If a buffer has a usage count greater than 0, its counter is decremented by 1 (giving it a second chance).
If a buffer counter is 0 and it is unpinned, it is selected for eviction.
If the chosen buffer is "dirty" (modified in memory but not on disk), the process triggers a flush to disk before overwriting the buffer with the new page.

Crash Recovery and the Write-Ahead Log (WAL)

Writing 8KB pages to disk randomly on every transaction commit would cause devastating disk thrashing. Instead, PostgreSQL uses Write-Ahead Logging (WAL) based on the ARIES recovery algorithm model.

The core rule of WAL is absolute: No data page can be written to non-volatile disk storage until the log record describing the change has been flushed to persistent media (fsync).

When a transaction issues a COMMIT:

The changes are recorded sequentially into the WAL buffer in memory.
The WAL buffer flushes sequentially to disk (a fast operation compared to random 8KB page writes).
The backend returns a success message to the client.
The actual modified pages in the Shared Buffer Pool remain marked as "dirty" and are flushed asynchronously later by the Background Writer or Checkpointer processes.

If the power fails or the server crashes, PostgreSQL inspects the last checkpoint LSN and replays the sequential WAL records starting from that checkpoint forward, restoring disk state to absolute consistency.

Practical Lessons for Database Engineering

Understanding PostgreSQL internals yields practical architectural rules:

Prevent Table Bloat: Regularly monitor long-running transactions. An open transaction prevents VACUUM from removing dead tuples created after its xmin snapshot horizon.
Leverage FillFactor for Update-Heavy Workloads: For tables undergoing frequent updates, lower the FILLFACTOR setting from 100 to 80-90. This reserves empty space within each 8KB page, maximizing HOT updates and eliminating unnecessary index writes.
Avoid Unnecessary Indexes: Every added index degrades non-HOT update performance because new pointers must be pushed into every index structure.

PostgreSQL's battle-tested reliability is the direct product of these elegant structural designs. By mapping out how pages, tuples, buffers, and logs interact under the hood, you gain the clarity needed to design highly efficient schemas and diagnose complex system bottlenecks.