How Postgres Stores Data and MVCC
12 min read
Everything Is a Heap
Postgres stores table rows in heap files — pages of 8KB each. Rows are not stored in any particular order. Understanding this helps you understand why indexes matter and why sequential scans exist.
Pages and Tuples
- Page — the fundamental unit of storage; 8KB by default
- Tuple — a single row version within a page
- ItemId — a pointer array at the top of each page pointing to tuples
- TOAST — oversized values (>2KB) are compressed and stored in a separate table
MVCC: Multi-Version Concurrency Control
Postgres never overwrites a row in place. Instead, it creates a new version of the row for each UPDATE, and marks the old version as dead. This allows readers to see consistent snapshots without blocking writers.
- xmin — the transaction ID that created this row version
- xmax — the transaction ID that deleted or updated this row version (0 if current)
- Readers use their snapshot to determine which row versions are visible to them
- Dead row versions accumulate over time — this is why VACUUM exists
MVCC is like a document editor with version history. Instead of erasing a change, every edit creates a new version. Readers see the version that was current when they opened the document.
Transaction Isolation Levels
- READ COMMITTED (default) — each query sees rows committed before it began
- REPEATABLE READ — the whole transaction sees the same snapshot from its start
- SERIALIZABLE — full isolation; behaves as if transactions ran one after the other
Use READ COMMITTED for most web app queries. Use REPEATABLE READ for transactions that do multiple reads and need a consistent view. Use SERIALIZABLE only when correctness is critical and you can handle serialisation failures.