Pages, heaps and tuples

You have hit table bloat, or run a vacuum that reported success and returned nothing to the operating system, or watched an index grow faster than the table it indexes — and every explanation you found used the words heap, page and tuple as though they were self-evident. They describe something physical: what a row actually is on disk, and what has to happen to it before it counts as read or written.

A row is a tuple inside a page#

Start from the outside. A table’s own storage is called its heap, and the heap is nothing more than a sequence of pages, each one the same size, chosen when Postgres is compiled and the same for every page thereafter. The page, not the row, is the unit Postgres actually reads and writes: touching a single byte inside a page means pulling the whole page into memory, and writing a single byte back means writing the whole page out again. A row is a tuple, and a tuple lives inside exactly one page at a time.

Inside a page, three things share the same fixed space, and they do not meet in the middle by accident. A short header sits at the front, holding bookkeeping about the page itself. Immediately after it come the line pointers, and they grow forward, toward the end of the page, one small entry added per row stored. The tuples themselves fill from the opposite direction: each new one is placed as far back as the current free space allows, so they grow backward, toward the header. Between the two is free space, shrinking from both sides as rows are added and line pointers accumulate to track them.

The line pointer is the piece that makes everything after this section possible, so it is worth being precise about what it buys. A line pointer is a small, fixed entry recording where its tuple currently sits in the page. Anything that needs to find a row — most importantly, an index — does not address a byte offset directly. It addresses a page number and a line pointer slot within that page. The page’s own line pointer is what resolves that slot to wherever the tuple actually is right now. That one level of indirection means a tuple can be moved around within its page — to compact free space, say — without a single index entry needing to change, because every index still points at the same slot; only what that slot resolves to has moved.

What an update actually does#

Postgres does not edit a row in place. An UPDATE writes a whole new tuple version and marks the old version dead; the two coexist in the heap until something cleans up. This is not an implementation shortcut — it is how Postgres gives every concurrent transaction a consistent view of the data without taking a lock to do it, and it has two consequences that matter for what a table looks like on disk.

The first consequence is the good case. If the new tuple version fits inside the same page as the old one, and the update did not change any column that an index covers, the new version can be written right there and the old line pointer can be made to lead to it. No index has to be touched at all, because every index still points at the same page and slot it always did. This is the heap-only-tuple path, and it depends on both conditions holding — enough free space in the page, and no indexed column touched — not either one alone.

The second consequence is what happens when either condition fails. The new version goes wherever there is room, often a different page, and every index on the table gains a fresh entry pointing at it. A table with several indexes and a workload that keeps changing an indexed column pays that cost on every single update, which is why a heavily updated table’s indexes can grow out of proportion to the table itself.

Either way, the old version does not disappear when the transaction commits. It sits in its page, invisible to new queries but still occupying space, until vacuum visits that page, confirms no transaction can still need the old version, and marks its space reusable. Reusable inside the page is the operative phrase — that space can be handed to the next tuple written into the same page, but the file on disk does not get smaller because of it. The query below is what that looks like from the outside.

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'sessions';

A live count that stays flat while the dead count climbs is exactly the shape a heavily updated table makes: rows are being replaced, not added, and the dead versions are outrunning whatever vacuum has processed so far.

Further along this path, B-tree vs LSM-tree works in pages too, and compares two engines that make opposite choices about when to pay for a write. This article sits ahead of it, because it is where the persistence path starts — the roadmap that puts what a row physically is before anything the rest of it builds on top of that fact.

Vacuum runs and reclaims nothing#

The recognisable shape is a table whose row count never changes and whose file size keeps climbing anyway. A row updated constantly — a counter, a status column, a last_seen timestamp — produces a fresh dead version on every update. Nothing about SELECT count(*) reveals it, because the row count is exactly what stayed the same.

The failure that makes this hard rather than routine is a long-running or idle-in-transaction session somewhere in the system. Vacuum cannot remove a dead version until it is certain no open transaction could still need to see it, so a transaction that has been open a long time holds that horizon back for every table it might touch — vacuum keeps running, keeps reporting that it finished, and reclaims nothing, because the versions it would remove are still potentially visible to that one session. Nothing in the bloated table’s own workload explains what is happening to it; the session responsible is usually elsewhere entirely — a connection pool holding a transaction open between requests, a client that opened a transaction and stalled, a script that never called commit.

The tell is a specific combination, not any one symptom alone: vacuum runs on schedule, the dead-tuple count in pg_stat_user_tables does not fall afterward, and somewhere there is a transaction that has been open far longer than any request should take. Any one of those alone is ordinary. Together they point at the same cause.

Not all bloat is a problem worth acting on. A table with a modest amount of steady free space simply reuses it as new tuples arrive, which is the system working as intended, not a leak. And the fix people reach for first — rewriting the whole table to compact it — takes a lock strong enough that a table still serving traffic often cannot give it up long enough to finish. Finding the transaction holding the horizon back is almost always the more useful first move.

None of this survives a crash on its own. The reason a half-written page does not cost you the table is the write-ahead log, which is written before the page it describes.