Phase 6Intermediate7 of 18 in Database Design

PostgreSQL Internals and Storage Engines

Pages, tuples, TOAST and HOT updates; shared buffers and the write path; WAL, checkpoints and point-in-time recovery; VACUUM, freezing and wraparound; and B-tree vs LSM-tree storage engines.

You can't tune what you can't picture. This phase follows a row from the application to the disk and back, so that bloat, checkpoint spikes, replication lag and wraparound warnings all make sense.

0/5 · 0%

5 topics ~43 min 9 code blocks & diagrams

Start with the first topic

6.1

Storage Layout: Pages, Tuples, TOAST and HOT

PostgreSQL stores each table as a heap of 8 KB pages; each page holds a header, an array of line pointers and tuples growing from the end. Values over ~2 KB are compressed and moved out-of-line to a TOAST table. HOT (heap-only tuple) updates keep new versions on the same page and skip index updates when no indexed column changed.

9 min 2 code practice

6.2

Memory and the Write Path

Writes change pages in shared_buffers and append WAL records; COMMIT flushes WAL, not data pages. Dirty pages reach disk later via the background writer and checkpoints, and reads come from shared buffers, then the OS page cache, then disk. work_mem and maintenance_work_mem size per-operation memory for sorts, hashes, index builds and VACUUM.

9 min 1 diagram 1 code practice

6.3

WAL, Durability, Archiving and PITR

The write-ahead log is an ordered stream of changes identified by LSNs, stored in 16 MB segment files. It gives crash recovery, streaming replication (replicas replay it) and point-in-time recovery (restore a base backup and replay archived WAL to any moment). synchronous_commit and fsync settings trade latency against the risk of losing commits.

8 min 2 code practice

6.4

VACUUM, Autovacuum, Bloat, Freezing and Wraparound

VACUUM removes dead tuples so their space can be reused, updates the visibility map (enabling index-only scans) and freezes old tuples. Autovacuum runs it automatically based on dead-tuple thresholds. Neglect leads to bloat, and ultimately transaction ID wraparound, where PostgreSQL stops accepting writes to protect data.

8 min 1 code practice

6.5

B-Trees vs LSM Trees: How Storage Engines Trade Reads for Writes

B-trees keep sorted keys in fixed-size pages updated in place: log-scale lookups with a height of 3–4 levels for billions of keys, at the cost of random writes and page splits. LSM trees buffer writes in memory, flush sorted immutable files and merge them by compaction: fast sequential writes, with read amplification mitigated by Bloom filters. The choice is a trade between read, write and space amplification.