How PostgreSQL Stores Your Data

Postgres does not store rows. It stores 8 KB pages containing tuples, and almost everything surprising about its behaviour — why your table grows after a DELETE, why UPDATE is slower than you think, why VACUUM exists at all — follows from that one fact. Understanding storage is the fastest way to move from treating Postgres as a black box that sometimes misbehaves to knowing why it misbehaves and what the query to prove it looks like.

The write-ahead log comes first

Before a page changes, the intent to change it is written to the write-ahead log and flushed to durable storage. This is the rule that makes everything else work: a transaction is only durable once its WAL record reaches disk, not once the heap page is written. The data page itself can be left dirty and flushed later, because crash recovery can always redo the change from the log. Durability is a property of the log, not of the table file.

That one design decision pays for itself several times over. Because the WAL is a sequential stream, one fsync can make many concurrent commits durable at once. And because every committed change is in the log, replication, point-in-time recovery and crash recovery are all just readers of the same file: a standby streams the WAL and replays it; a recovery replays it from the last checkpoint. When a standby flushes the commit record to its own disk, a commit can even be acknowledged synchronously to the standby — giving you durability across two machines for the price of one extra hop.

There is a subtlety worth knowing: the first time a page changes after a checkpoint, Postgres writes the entire 8 KB page image into the WAL. A crash mid-write can tear a page — leave it half old and half new — and row-level records alone cannot reconstruct it. That is what full_page_writes protects against, and it is why longer checkpoint intervals can reduce WAL volume: fewer first-changes means fewer full-page images.

MVCC means UPDATE is INSERT plus tombstone

Postgres never updates rows in place. Every tuple carries two transaction identifiers in its header: xmin, the transaction that created it, and xmax, the transaction that deleted or superseded it. When you UPDATE a row, Postgres stamps the old version's xmax and writes an entirely new version elsewhere on the page with a fresh xmin. A DELETE does even less: it just stamps xmax. The bytes stay on disk until something reclaims them.

• An UPDATE writes a new tuple version and marks the old one dead.
• A DELETE only marks dead; the bytes stay until vacuum reclaims them.
• Long-running transactions block reclamation and cause table bloat.

The payoff is that readers never block writers and writers never block readers: a reader with an older snapshot simply follows the version chain and reads the version that was current when its snapshot was taken. The cost is that every write leaves tombstones behind. Under sustained UPDATE traffic, the same physical row accrues version after version. If no transaction can prove it is finished with a version, vacuum cannot reclaim it — which is why a single idle transaction left open for hours can quietly pin gigabytes of dead rows and make your table bloat past all expectations.

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If that query returns large numbers on a table you write to constantly, you do not have a Postgres problem. You have a vacuum-tuning problem — or a stuck long-running transaction, which you can find in pg_stat_activity by looking for sessions that sit in 'idle in transaction' for a long time. Autovacuum will eventually fire, but it only recycles space inside the table file; it does not return the file to the operating system. Only VACUUM FULL rewrites the table and shrinks it on disk, at the price of locking it.

Indexes store pointers, not rows

A B-tree index stores keys and pointers to heap tuples — each entry is a key plus a (page, offset) location. That is why reads and writes behave so differently. A read that hits the index still has to visit the heap page to get the row, unless the visibility map says the page is all-visible and an index-only scan can skip the fetch. A write, by contrast, must maintain every index that touches the changed column: an UPDATE writes a new heap version and, unless the update qualifies as a heap-only update, new index entries too. That is why updating an indexed column is dramatically more expensive than updating one that is not indexed.

Heap-only updates — where the new version fits on the same page and no indexed column changed — skip the index entirely, which is why they are dramatically cheaper and why fillfactor below 100 helps write-heavy tables: it leaves free space on each page so hot updates can succeed more often. Index writes also deduplicate: repeated keys on the same page are merged into a single posting list, which is why heavily duplicated columns can shrink indexes substantially.

Big values go somewhere else entirely

A tuple cannot span pages, so Postgres cannot store an arbitrarily large value inline. When a row exceeds a couple of kilobytes, the large values are compressed and, if needed, split into chunks stored in a separate TOAST table. The row keeps an 18-byte pointer instead. This is why a column holding JSON or image blobs does not blow up your page layout, and why most rows that contain one large field are actually small on disk — the fat is parked elsewhere.

Reading the vitals

Postgres ships its own diagnostics, and the practical skill is knowing which view answers which fear. pg_stat_user_tables tells you whether a table is bloating: a high n_dead_tup that does not shrink even after an autovacuum runs means a long transaction is pinning old snapshots. pg_stat_activity shows you the offenders — sessions that sit in 'idle in transaction' for hours are almost always the root cause of runaway bloat, because they hold back the reclamation of everything they could see. pg_stat_replication shows replication lag from the primary's side as a gap between what the WAL has reached and what the standby has replayed; a growing gap is an alert, not an afterthought. And EXPLAIN ANALYZE, with the BUFFERS option, shows you where each query's time and page reads actually go — the difference between a plan that uses an index and one that scans a table is usually visible in a single row of output.

The mental model collapses to this: the log is the truth, pages are a cache, and MVCC is a debt collector that only reaps when transactions end. Nearly every 'Postgres mystery' — a table that grows after delete, an update that is inexplicably slow, a backup that is huge, a replica that is always behind — resolves into one of those three facts if you look at the right system view. There is no magic in it. There is only the discipline of knowing which layer you are looking at.

The buffer cache hides all of this

All of these pages pass through a shared buffer cache that every backend shares. A read checks the cache first; only a miss touches disk. That is the practical lever behind most performance tuning: the working set that fits in shared_buffers never goes near the storage layer. The counterintuitive tuning advice follows — Postgres expects the OS page cache to act as a second level beneath it, so setting shared_buffers far too high can hurt by crowding out the kernel's own caching and doubling the work.

The useful mental model is short: log first, pages later; never overwrite, always version; indexes point, they do not contain; big fields are exiled; and everything funnels through a cache you can inspect with EXPLAIN ANALYZE and pg_stat_user_tables. Once those five facts are internalised, almost every Postgres mystery — bloat, slow updates, surprise disk growth, unkillable table locks — becomes a known quantity you can query into the open.