02. Storage Engine & On-Disk Layout

PostgreSQL organizes relational data into fixed-size 8KB pages (blocks). Every table and index on disk maps directly to an array of pages, segmented into 1GB files to prevent filesystem file-size limitations. Understanding this binary format is essential for diagnosing bloat, optimizing column order, and tuning table storage.

---

1. Filesystem Anatomy & Segment Slicing

Within $PGDATA, relational data is partitioned by database OID:

$PGDATA/
├── base/
│   ├── 16384/                 # Database OID
│   │   ├── 24576              # Relation main fork (Segment 0, up to 1GB)
│   │   ├── 24576.1            # Segment 1 (1GB to 2GB)
│   │   ├── 24576_fsm          # Free Space Map fork
│   │   ├── 24576_vm           # Visibility Map fork
│   │   └── 24576_init         # Initialization fork (unlogged tables only)

The Four Relation Forks

1. _main (Heap / Index Data): The primary storage fork containing table tuples or index nodes.
2. _fsm (Free Space Map): A binary tree structure tracking available free bytes per page in categorical buckets (0 to 255). Allows INSERT operations to locate a page with sufficient space in \(O(\log N)\) time without scanning the heap.
3. _vm (Visibility Map): A lightweight two-bit bitmap per 8KB page:
4. Bit 0 (All-Visible): Set if all tuples in the page are visible to all current and future transactions. Allows Index-Only Scans to skip visiting the heap.
5. Bit 1 (All-Frozen): Set if all tuples have been frozen by VACUUM FREEZE. Allows vacuuming routines to skip the page entirely.
6. _init: For UNLOGGED tables, stores an empty template used to restore the table upon post-crash restart.

---

2. Binary Layout of an 8KB Page

A page is 8192 bytes. It is structured from top-to-bottom and bottom-to-top to maximize contiguous allocation flexibility:

+----------------------------------------------------------------+
| PageHeaderData (24 bytes)                                      |
|   pd_lsn (8B) | pd_checksum (2B) | pd_flags (2B)               |
|   pd_lower (2B) | pd_upper (2B) | pd_special (2B) | pagesize (2B) |
+----------------------------------------------------------------+
| Line Pointers (ItemIdData Array, grows downwards)             |
|   [Item 1: 4B] [Item 2: 4B] [Item 3: 4B] ...                   |
|   (lp_offset: 15b, lp_flags: 2b, lp_len: 15b)                  |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
|                      Free Space Hole                           |
|               (between pd_lower and pd_upper)                  |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
| Tuple Data (grows upwards from pd_upper)                       |
|   ... [Tuple 3] [Tuple 2] [Tuple 1]                            |
+----------------------------------------------------------------+
| Special Space (Index-specific metadata, e.g. B-Tree links)     |
+----------------------------------------------------------------+

The Slotted Page Mechanism

• pd_lower: Byte offset from page start to the end of the line pointer array.
• pd_upper: Byte offset from page start to the beginning of the newest tuple data.
• Free Space Calculation: pd_upper - pd_lower.
• Stable Identifiers (ItemPointerData / ctid): A tuple's physical address is a tuple (BlockNumber, OffsetNumber). Foreign keys and indexes point to the Line Pointer (OffsetNumber), never to the raw byte offset. If PostgreSQL defragments the page, only lp_offset changes; indexes remain unaffected!

---

3. Tuple Header & Alignment Padding Waste

Every row on disk begins with a 23-byte header (HeapTupleHeaderData), rounded up to 24 bytes for 8-byte CPU alignment:

HeapTupleHeaderData (24 bytes):
├── t_xmin (4B)       : Transaction ID of inserting transaction
├── t_xmax (4B)       : Transaction ID of deleting/locking transaction (or 0)
├── t_cid / t_xvac (4B): Command identifier within the transaction
├── t_ctid (6B)       : Physical pointer (block, offset) to self, or next version
├── t_infomask2 (2B)  : Number of attributes (11 bits) + HOT flags
├── t_infomask (2B)   : Status flags (COMMITTED, INVALID, HASNULL, HASTOAST)
├── t_hoff (1B)       : Offset to actual user data (includes Null Bitmap)
└── Null Bitmap       : 1 bit per column, present if HEAP_HASNULL is set

Alignment Padding Waste

CPUs access 8-byte types (bigint, timestamptz, double precision, pointers) on 8-byte memory addresses, and 4-byte types (integer, date) on 4-byte addresses. If columns are ordered inefficiently, PostgreSQL inserts invisible padding bytes into every row:

-- BAD ORDER: 16 bytes user data + 8 bytes padding = 24 bytes data (+ 24B header = 48B total)
CREATE TABLE orders_bad (
    id          smallint,   -- 2 bytes (+ 6 bytes padding) -> aligned to 8
    created_at  timestamptz,-- 8 bytes
    status_id   smallint,   -- 2 bytes (+ 6 bytes padding) -> aligned to 8
    total_cents bigint      -- 8 bytes
);

-- OPTIMAL ORDER: Descending alignment width (8B -> 4B -> 2B -> 1B)
CREATE TABLE orders_good (
    created_at  timestamptz,-- 8 bytes
    total_cents bigint,     -- 8 bytes
    id          smallint,   -- 2 bytes
    status_id   smallint    -- 2 bytes (+ 4 bytes padding to end of row)
);
-- Saves 8 bytes per tuple! At 100M rows, this eliminates 800 MB of pure disk & memory bloat.

---

4. The TOAST Subsystem (The Oversized-Attribute Storage Technique)

PostgreSQL pages are 8KB and do not span across block boundaries. When a tuple exceeds the target threshold (TOAST_TUPLE_THRESHOLD, approximately 2KB), PostgreSQL invokes TOAST:

1. Step 1 (Compress): Compresses variable-width fields (pglz or lz4) with EXTENDED or MAIN storage strategy.
2. Step 2 (Out-of-line): Moves large attributes out of the main heap into an auxiliary TOAST table (pg_toast_<oid>), leaving behind an 18-byte TOAST pointer in the heap tuple.
3. Chunking: The TOAST table chunks the large payload into 2KB slices (va_chunk_data), indexed by (chunk_id, chunk_seq).

-- Alter compression algorithm to modern LZ4 on high-throughput columns
ALTER TABLE article_revisions 
ALTER COLUMN body_text SET COMPRESSION lz4;

---

5. Inspecting On-Disk Pages with pageinspect

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Inspect page header
SELECT * FROM page_header(get_raw_page('users', 0));

-- Inspect raw line pointers and tuple headers in block 0
SELECT 
    lp,
    lp_off,
    lp_flags,
    lp_len,
    t_xmin,
    t_xmax,
    t_field3 as t_cid,
    t_ctid,
    t_infomask,
    t_infomask2
FROM heap_page_items(get_raw_page('users', 0))
LIMIT 5;