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#

An UPDATE writes a new tuple version rather than overwriting the old one. The old version is not immediately reclaimable: an earlier snapshot may still need it, and the update may roll back. MVCC lets ordinary readers use visible versions without taking a row lock against a writer; writers and explicit locking operations still use locks.

HOT is eligible when the new version fits on the old page and the update changes no column referenced by a non-summarizing index. Since PostgreSQL 16, summarizing indexes such as BRIN no longer block HOT, although their summaries may still need maintenance. HOT avoids adding new ordinary index entries; the existing entry can lead through a same-page version chain. It does not overwrite the version an older snapshot may need.

When either HOT 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.

Once no transaction can need an old version, pruning or vacuum can reclaim its storage for reuse. HOT permits some chain cleanup during ordinary page access as well as vacuum. Reusable space remains inside the table’s pages; it does not imply that the operating-system file shrinks. The counters below are estimates, not a byte-by-byte measurement of bloat.

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.

A transaction or snapshot that keeps a relevant visibility horizon old can prevent removal of versions it might still need. Not every open transaction pins every old version, and vacuum can still reclaim other eligible versions. Replica feedback, replication slots and prepared transactions can also affect cleanup; a long-lived session is one candidate to inspect, not proof of the cause.

Those symptoms justify investigating the visibility horizon and vacuum’s actual progress, not automatically terminating the oldest session. Inspect snapshot/XID age, vacuum diagnostics, replica feedback and slots, and update rate. Estimated tuple counts and a session’s wall-clock age alone do not prove what is holding removal back.

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. Diagnosing retained versions and ordinary vacuum capacity is a useful first move before planning a disruptive rewrite.

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.