Inside PostgreSQL Heap Storage: A Visual Deep Dive into How Data Is Physically Stored and Retrieved
When you create a table in PostgreSQL, it is natural to imagine data organized cleanly on disk in relational rows and columns. However, under the hood, PostgreSQL manages table data using an append-only, unstructured format known as Heap Storage. [1, 2]
Understanding how the heap works exposes the internal design powering fast query execution, indexing behaviors, and multi-version concurrency control (MVCC).
1. What Exactly is Heap Storage?
Instead of forcing rows into a rigid physical order, PostgreSQL deposits new data into a flexible structure called a heap file. Think of the heap like a massive parking lot where incoming cars park in the first available open space they find.
PostgreSQL chooses this heap layout because it offers distinct operational advantages:
- Sub-millisecond Inserts: The database engine writes incoming data to the first open block on disk without spending CPU cycles sorting rows.
- Write-Friendly Scaling: High-volume transaction streams can write concurrently to separate areas of the heap file.
- MVCC Alignment: The disorganized nature of the heap provides an ideal canvas for maintaining multiple simultaneous row versions. [1, 2]
2. The Core Mechanics: MVCC & The “Copy-on-Write” Lifecycle
Because PostgreSQL prioritizes concurrent performance, it avoids modifying existing rows in place. When updates or deletes occur, the engine triggers an architectural sequence designed for data safety: [1]
[Row Update Triggered]
│
▼
┌──────────────────────────────────────────────┐
│ 1. Duplicate row version created in Heap │
├──────────────────────────────────────────────┤
│ 2. Old row version marked as "Invisible" │
├──────────────────────────────────────────────┤
│ 3. Active concurrent transactions read old │
│ data while new transaction commits new │
└──────────────────────────────────────────────┘
│
▼
[VACUUM background process cleans dead tuples later]This elegant isolation pattern means readers never block writers, and writers never block readers. The accumulated “dead tuples” are eventually swept away by the background VACUUM process, liberating those data slots for future inserts.
3. Inside the Page: The Anatomy of Database I/O
The heap is not a single, continuous, monolithic block of disk space. It is divided into fixed-size physical blocks called Pages, which act as the fundamental unit of I/O in PostgreSQL. By default, every page is precisely 8 Kilobytes (8KB).
Get Asif Raza’s stories in your inbox
Join Medium for free to get updates from this writer.
A single 8KB page contains four highly organized regions:
| Page Region | Growth Direction | Purpose & Contents |
| -------------------------- | ---------------- | ------------------------------------------------------------------------------------------------------ |
| Page Header | Static (Top) | Stores page metadata, including checksums, transaction visibility information, and page identifiers. |
| Item Pointers (Slot Array) | Grows Downward ↓ | An array of 4-byte pointers that reference the exact byte offsets of row (tuple) data within the page. |
| Free Space | Dynamic (Middle) | The available space between item pointers and tuple data, consumed as new rows and pointers are added. |
| Tuple Data (Actual Rows) | Grows Upward ↑ | Stores the actual row data (tuples), growing upward from the bottom of the page. |4. Why PostgreSQL Prefers Secondary (Unclustered) Indexing
Unlike engines like MySQL’s InnoDB — which use a Clustered Index to force the physical rows on disk to follow the primary key order — PostgreSQL treats every index as a Secondary Index. [1, 2]
┌─────────────────────────────────┐
│ B-Tree Index Block │
├─────────────────────────────────┤
│ [Key: 101] -> Tuple ID: (0, 1) │──┐
│ [Key: 102] -> Tuple ID: (0, 2) │ │
└─────────────────────────────────┘ │ (Points via TID)
▼
┌────────────────────────────────────────────────────────┐
│ PostgreSQL Heap File (Page 0) │
├────────────────────────────────────────────────────────┤
│ [Tuple Pointer Array] │
│ ├── Pointer 1 ───────────────────────────────┐ │
│ └── Pointer 2 ─────────────────┐ │ │
│ ▼ ▼ │
│ [Row Data Area] [Row ID 102] [Row ID 101] │
└────────────────────────────────────────────────────────┘In PostgreSQL, indexes (including primary keys, foreign keys, and composite indexes) are stored completely separate from the actual table data. The index structure contains a sorted list of keys mapped to Tuple IDs (TIDs). A TID acts as a direct coordinate: (Page Number, Item Pointer Index).
Because the index doesn’t dictate physical disk arrangement, you can append data rapidly, while multiple indexes simultaneously point directly to the stable heap pages.
💡 Can you physically order a Postgres table? Yes, using the
CLUSTERcommand (e.g.,CLUSTER users USING idx_users_age;). This matches the table's physical order to the index once, but subsequent updates and inserts will not maintain that order.
5. The Multi-File Footprint of a Single Table
Because PostgreSQL maps database objects directly onto the host operating system’s filesystem, a single table requires multiple files to optimize performance and lookups.
When you create a single table with two indexes, PostgreSQL creates a total of 9 separate files under the hood:
- Main Table Data: 1 file containing the raw heap pages.
- Indexes: 2 files (one dedicated file for each index structure).
- Free Space Maps (FSM): 3 files (one for the table and one per index) that tell the engine exactly which pages have room for new data.
- Visibility Maps (VM): 3 files (one for the table and one per index) that flag which pages contain only visible, committed data, allowing operations like index-only scans to bypass the heap file entirely. [1]
This modular, file-per-object architecture makes operations highly predictable and allows cloud database infrastructure to scale storage subsystems efficiently. By mastering the boundaries of pages, heap slots, and independent secondary indexes, database engineers can design high-throughput systems that fully leverage the structural strengths of PostgreSQL.
