Skip to content

Phase 05: Storage & Memory Internals

Chapter 21: Deep Dive: The 8KB Page Layout

21.1 Physical Page Anatomy (PageHeaderData)

Every table, index, and sequence in PostgreSQL is physically subdivided into 8,192-byte blocks (8KB pages). The page structure is engineered to allow variable-length tuples while maintaining fixed-size pointer indexing.

+-------------------------------------------------------------------------------+
| PageHeaderData (24 bytes)                                                     |
| pd_lsn (8B) | pd_checksum (2B) | pd_flags (2B) | pd_lower (2B) | pd_upper (2B)|
+-------------------------------------------------------------------------------+
| ItemIdData Array (Line Pointers)                                              |
| [ ItemId 1 (4B) ] [ ItemId 2 (4B) ] [ ItemId 3 (4B) ] ... (grows downwards)   |
+-------------------------------------------------------------------------------+
|                                                                               |
|                             FREE SPACE GAP                                    |
|                   (Available for new Line Pointers & Tuples)                  |
|                                                                               |
+-------------------------------------------------------------------------------+
| Tuple Heap Data (grows upwards from the bottom of the page)                   |
| ... [ Tuple 3 Payload ] [ Tuple 2 Payload ] [ Tuple 1 Payload ]               |
+-------------------------------------------------------------------------------+
| Special Space (Index-specific metadata, e.g. B-Tree High Key) (0 to N bytes)  |
+-------------------------------------------------------------------------------+

Page Header Fields:

  • pd_lsn: 64-bit Log Sequence Number of the last WAL byte that modified this page.
  • pd_checksum: 16-bit CRC checksum used for detecting bit rot and silent disk corruption.
  • pd_lower: Byte offset marking the end of the line pointers array.
  • pd_upper: Byte offset marking the beginning of the newest tuple data.
  • The Free Space Gap: Free space is simply pd_upper - pd_lower. When pd_lower == pd_upper, the page is 100% full.

21.2 Item Pointers (ItemIdData / CTID)

Every row is physically addressed by a 6-byte identifier called a CTID: ctid = (block_number, line_pointer_index) e.g., (45, 3) means Block 45, ItemId 3. * Index pointers point to the ItemId line pointer, NEVER directly to the tuple bytes. * If a tuple is moved around inside the page during defragmentation, only the 4-byte ItemId is updated. The index pointers remain completely untouched!

Chapter 22: Memory Architecture & The Buffer Manager

22.1 The Clock Sweep Buffer Eviction Algorithm

shared_buffers is managed as an array of buffer descriptors with an associated Hash Table mapping (DatabaseID, RelationID, BlockNumber) -> BufferDescriptorID. When shared_buffers is full and a new page must be loaded from disk, Postgres uses a Clock Sweep algorithm:

        Buffer 0 [Usage Count: 2]
               \
 Buffer 7 [3] -- [CLOCK HAND] -- Buffer 1 [0] -> EVICT!
               /
        Buffer 6 [1]
  1. The clock hand sweeps across buffer headers.
  2. If a buffer has usage_count > 0, the sweeper decrements it by 1 and advances to the next buffer.
  3. If a buffer has usage_count == 0, and is not pinned by an active query, it is chosen for eviction.
  4. If the evicted buffer is "dirty" (modified in RAM), it is flushed to disk before the new page is read.

22.2 Double Buffering: Postgres vs The OS Page Cache

When Postgres reads a block: 1. Postgres checks shared_buffers. 2. If missed, it issues an OS read() system call. 3. The Linux/macOS kernel checks its own Page Cache. If cached, it copies the 8KB page from OS RAM into shared_buffers. 4. If missed, the kernel reads the page from physical NVMe/SSD into OS Page Cache, then copies it to shared_buffers.

Disk (NVMe) ---> OS Page Cache (RAM) ---> Postgres shared_buffers (RAM)
* Why shared_buffers should be 25% of RAM: If you allocate 90% of RAM to shared_buffers, both Postgres and the OS kernel will cache duplicate copies of the same hot data blocks, wasting memory that could otherwise hold dirty write buffers or private sort space (work_mem).

22.3 work_mem Sort Mechanics (Quicksort vs External Merge Sort)

When executing ORDER BY: 1. If data size \(< work\_mem\): Postgres executes an in-memory Quicksort. Lightning fast. 2. If data size \(\ge work\_mem\): Postgres runs out of RAM. It partitions the dataset into sorted runs and writes them to temporary files on disk (pgsql_tmp). It then performs an External Merge Sort, streaming runs from disk. * Inspection: Look at Sort Method in EXPLAIN ANALYZE: * Sort Method: quicksort Memory: 25kB (Good) * Sort Method: external merge Disk: 4520kB (Spill to disk, needs work_mem tuning or query refactoring).

Chapter 23: TOAST Tables (Oversized-Attribute Storage Technique)

23.1 The 8KB Limitation & The TOAST Threshold

Because Postgres cannot split a tuple across multiple pages, any tuple that exceeds TOAST_TUPLE_THRESHOLD (approx. 2KB, 1/4th of an 8KB page) triggers the TOAST mechanism.

The 4 TOAST Strategies per Column:

  1. PLAIN: Disallows compression and out-of-line storage (e.g., integer, boolean). Fixed size.
  2. EXTENDED (Default for text, varchar, jsonb, bytea):
  3. Step 1: Compresses data inline using LZ4 or pglz.
  4. Step 2: If still \(> 2KB\), moves the compressed data out-of-line into a hidden TOAST table (pg_toast_XXXXX).
  5. EXTERNAL: Stores out-of-line without compression. Best for already compressed data (e.g., JPEG images, zipped archives).
  6. MAIN: Allows compression inline; only moves out-of-line as a last resort.

23.2 The Hidden Performance Trap of SELECT *

  • When large data is TOASTed, the main table row only stores an 18-byte TOAST pointer containing (toast_relid, chunk_id, logical_size).
  • If you run SELECT id, status FROM users, Postgres never reads the TOAST table. It processes only the compact 8KB heap pages.
  • If you run SELECT * FROM users (or an ORM selects all columns), Postgres must:
  • Read the main heap page.
  • Perform secondary random I/O reads on the pg_toast table.
  • Decompress LZ4/pglz chunks in CPU memory.
  • Allocate megabytes of RAM per query session.
  • Result: SELECT * transforms a 2ms query into a 400ms CPU-and-I/O bottleneck.

Chapter 24: Disk I/O & Storage Subsystems

24.1 Filesystem Selection: XFS vs ext4 vs ZFS

  • XFS (Production Standard for Linux Postgres):
  • Excels at parallel write allocation and large sequential file extents.
  • Better handling of direct I/O and checkpoint write bursts.
  • Recommended mount options: noatime,nodiratime,allocsize=64M.
  • ext4: Reliable, but can suffer from jbd2 journaling lock contention under heavy write concurrency.
  • macOS (APFS): Modern copy-on-write filesystem; excellent for local development and snapshotting sandbox data.

24.2 Direct I/O and the Future of Postgres

Historically, Postgres relied entirely on buffered I/O (read(), write(), fsync()). * In active development for modern PostgreSQL: Native Direct I/O (O_DIRECT) and asynchronous I/O (io_uring), bypassing the OS Page Cache entirely to eliminate double buffering and provide deterministic I/O scheduling similar to enterprise storage engines.