What Postgres Actually Does With Your Data

Most people learn Postgres as a language: SELECT, WHERE, JOIN, a mental model where a table is a grid and a row is a cell in it that gets overwritten in place. That model is good enough until it isn’t — until a table that should have 2 million rows is 900 MB bloated, until an index you built is quietly never used, until a SELECT count(*) takes twelve seconds on a table you were sure was small. All three of those have the same root cause: Postgres does not store your data the way the mental model says it does.

This is what actually happens underneath, in four parts: how a row survives being updated, how a transaction survives a crash, how the planner decides whether your index is worth using, and what all of that leaves behind on disk.

An UPDATE does not update anything

Say that literally, and it stops being surprising later. UPDATE accounts SET balance = 350 WHERE id = 42 does not find the row and change the number in place. It writes an entirely new tuple — a full new row version, all columns, not a diff — and leaves the old one exactly where it was, only now marked as superseded. The table on disk after that update has two versions of row 42, not one.

This is Postgres’s implementation of MVCC — multi-version concurrency control — and it is the reason a reader never blocks a writer and a writer never blocks a reader. A long running report query and a hot path of updates can run against the same table at the same instant because they are, in a real sense, looking at different data: whichever tuple version was correct as of the moment their transaction’s snapshot was taken.

Every tuple carries two hidden columns that make this work: xmin, the id of the transaction that created it, and xmax, the id of the transaction that superseded it — unset until something does. Visibility is a comparison, not a lookup: a version is visible to your transaction if its creator committed before your snapshot started, and it has not yet been superseded from your point of view.

Nobody locks anything to answer that question. It is arithmetic on two integers stamped on the tuple, which is why Postgres can let readers and writers run concurrently without either one waiting on the other’s row lock — they are not contending for the same bytes, because there is more than one copy of the row on the page.

The cost of this is the part people find out the hard way, three sections from now: every update leaves a corpse behind, and nothing about the UPDATE statement itself cleans it up.

COMMIT is a promise about a log, not about your table

The second piece of folklore worth breaking: when Postgres acknowledges a COMMIT, it has not necessarily written your change into the actual table file yet. What it has done is fsync a record describing that change into the write-ahead log, a strictly append-only file that is cheap to write sequentially and safe to trust — the WAL record exists, on durable storage, before the client is told the transaction succeeded. The heap file — the actual table data — gets updated later, in the background, in whatever order is convenient for the OS’s page cache.

This ordering — log first, data file later — is the entire durability guarantee. If the process dies or the machine loses power between the WAL fsync and the heap file update, nothing is lost: on restart, Postgres replays the WAL records that hadn’t made it to the heap yet and ends up in the same state it would have reached anyway. That replay is called REDO, and it is the reason startup after a crash is not instant — it has log to get through.

A checkpoint bounds how much log that replay has to cover. At a checkpoint, Postgres forces every change up to that point out to the actual heap files, then records the checkpoint’s position in the log. Recovery never needs to look earlier than the most recent checkpoint, because everything before it is already durable outside the log.

This is a genuine dial, not a fixed cost: checkpoint_timeout and max_wal_size control how often checkpoints happen. Frequent checkpoints mean fast recovery and more constant background I/O; infrequent ones mean cheaper steady-state writes and a longer wait the one time you actually crash. Neither answer is correct in general — it is a real production trade-off, not a setting with an obviously right default.

Why your index sometimes gets ignored

Postgres decides how to execute a query before it runs it, using a cost-based planner — not a rule-based one that always prefers an index because indexes sound faster. It estimates a cost for every plan it can construct and picks the cheapest one, and “cheap” is not “fewer rows touched”, it is closer to “least I/O”, with two very different prices for I/O depending on its pattern.

Reading a table sequentially — a seq scan — is cheap per page: the disk head, or the storage controller’s read-ahead, is already moving in the right direction. Reading it via an index is a different shape entirely: find each matching entry in the B-tree, then jump to wherever that row happens to live on the heap, in whatever order the index happens to return matches. That jump is a random read, and Postgres’s own default cost model prices a random page read at four times a sequential one — random_page_cost = 4 against seq_page_cost = 1, straight out of postgresql.conf.

Put those two numbers together and the behavior that looks like a bug stops looking like one: below some selectivity, matching few enough rows that the random-read tax is small in total, the index wins comfortably. Above it, matching so many rows that the sum of all those random jumps costs more than one cheap pass over everything, the planner switches to a seq scan — correctly. An index that is never used on a low-selectivity column is not a broken index. It is the planner doing exactly its job.

This is also why EXPLAIN ANALYZE, not EXPLAIN, is the tool for “why didn’t my index get used”: the first shows you the planner’s estimate, the second shows you what actually happened, and the two disagreeing is usually stale statistics — run ANALYZE — rather than a planner bug.

What all of this leaves on disk

Put the first and third parts together and the shape of the problem is obvious before you even see a number: every UPDATE leaves a dead tuple, and nothing about running an UPDATE removes it. Run enough of them and a table that should be a few hundred megabytes is a few gigabytes, mostly dead weight the planner still has to estimate around and the query executor still has to skip past on every scan. That’s bloat.

VACUUM is the process that gets it back — but not in the way “cleanup” usually implies. Plain VACUUM marks dead tuples’ space as free for Postgres to reuse on future inserts and updates. It does not shrink the file on disk; the table’s footprint in du stays exactly where the bloat left it, because giving space back to the filesystem requires rewriting the file, and Postgres does not do that for free. VACUUM FULL does rewrite it — genuinely reclaiming disk space — at the cost of an ACCESS EXCLUSIVE lock for the duration, which blocks every read and write on the table until it finishes. That trade is why routine maintenance runs plain VACUUM, and VACUUM FULL is reserved for a scheduled window on a table that has bloated past the point plain vacuuming will fix.

autovacuum exists so you rarely have to think about any of this — it runs plain VACUUM automatically once a table’s dead-tuple fraction crosses a threshold, 20% by default. The practical failure mode is not autovacuum being absent, it is autovacuum being starved: a long-running transaction holds open a snapshot from before a batch of updates, which means those dead tuples can’t be reclaimed yet — they might still be visible to that old snapshot — and bloat keeps climbing until the transaction finally ends. A five-minute analytics query left open against a busy OLTP table is a more common cause of runaway bloat than any vacuum misconfiguration.

Where this actually bites you

Four symptoms, and the internals that explain each one:

A table’s row count looks right but count(*) is slow. MVCC means there is no single stored row count — every row’s visibility has to be checked against your transaction’s snapshot, which means a full scan under the hood no matter how the query looks. This is inherent to the storage model, not a missing index.

Disk usage keeps climbing on a table whose row count is stable. Bloat from an update- heavy workload, usually with autovacuum starved by a long-running transaction somewhere else in the system. Check pg_stat_activity for anything old before reaching for VACUUM FULL.

An index you built is never used. Either the selectivity doesn’t clear the crossover this post just walked through, or the planner’s statistics are stale — ANALYZE the table and check EXPLAIN ANALYZE against EXPLAIN before concluding the index itself is wrong.

Recovery after a restart takes longer than expected. checkpoint_timeout and max_wal_size are set too loose for how much write traffic the table actually sees — tighten them, and accept the slightly higher steady-state I/O that buys.

None of these are exotic. They are the direct, mechanical consequences of choices Postgres made on purpose — MVCC over locking, WAL over synchronous heap writes, cost-based planning over rule-based — and once the shape of those choices is visible, the symptoms stop looking random.