Measuring and Fixing Bloat

Bloat is the gap between the space a relation occupies and the space its live rows need. A little of it is the system working: the free room a HOT update lands in, the space the next insert will take. A lot of it is a tax on every read, because the same rows now span five or fifty times as many pages and every sequential scan pays for all of them.

There are two skills here, and they are usually confused with each other. One is measuring bloat honestly, which is harder than it sounds because the well-known estimate is only an estimate. The other is choosing between the three ways to remove it, which differ mainly in what they lock and how much disk they need. Both come before the more important question, which is whether removing it fixes anything at all.

Estimating Versus Measuring

The bloat queries that circulate on blogs and in monitoring tools derive a number from the catalogue: the page count and row estimate in pg_class, and the average column widths ANALYZE recorded in pg_statistic. Multiply the widths by the row estimate, add per-tuple overhead, compare against the actual page count, and the difference is called bloat. It costs nothing to run, it can be sampled every minute across every table, and it is the right tool for a dashboard and an alert.

It is also wrong more often than its two decimal places suggest. It leans on estimates that are themselves stale between analyzes, and it misjudges tables with many NULLs, with widely varying value widths, or with TOASTed columns whose stored size has nothing to do with the declared type. A 30% reading from an estimate query is a reason to look closer. It is not a reason to take an outage.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len, tuple_count, dead_tuple_count,
       round(tuple_percent::numeric,      2) AS live_pct,
       round(dead_tuple_percent::numeric, 2) AS dead_pct,
       round(free_percent::numeric,       2) AS free_pct
  FROM pgstattuple('inventory');

 table_len | tuple_count | dead_tuple_count | live_pct | dead_pct | free_pct
-----------+-------------+------------------+----------+----------+----------
 943718400 |       12000 |             5104 |     0.05 |     0.02 |    99.79

pgstattuple is a contrib extension that reads the relation and counts what is actually there — exact live and dead tuple counts, exact free space, no inference. The price is the scan, which on a large table is real I/O, and the fact that the answer is a moving average across a running system rather than an instant. Execution is restricted to superusers and members of pg_stat_scan_tables. Where the scan is too expensive, pgstattuple_approx skips the pages the visibility map marks all-visible: its live figures are estimates, its dead figures are still exact, and it reports what fraction of the table it managed to read.

The reading on inventory is the whole chapter in one row. Twelve thousand live rows accounting for 0.05% of a 900 MB file. Five thousand dead versions, under one percent, because autovacuum has been keeping up perfectly since the previous two topics. Nothing is wrong with this table any more; it is simply nine hundred megabytes wide and never going to shrink on its own. The last column reads 99.79% free.

Index Bloat Is a Separate Number

Indexes bloat on their own schedule and vacuum never shrinks them. A B-tree leaf page that fills up splits into two roughly half-full pages, and Postgres does not merge them back when the keys are later deleted — only a wholly empty page is recycled. Insert keys in random order, which is what a version-4 UUID or a high update rate does, and the index settles at a leaf density well below what a fresh build would produce. Every plan that uses it then reads more pages for the same rows.

SELECT * FROM pgstatindex('inventory_pkey');

 version | tree_level | index_size | leaf_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+------------+------------------+--------------------
       4 |          1 |     753664 |         86 |            41.63 |              28.94

pgstatindex, from the same extension, reports the structure of a B-tree: how deep it is, how many leaf pages it has, and the two numbers that matter — avg_leaf_density, the average fill of those leaf pages, and leaf_fragmentation, how far their physical order has drifted from their key order. A freshly built index sits near its fillfactor of 90. This one is at 41.6%, which means it is carrying roughly twice the pages it needs, and no amount of vacuuming will change that number. Rebuilding is the only cure, and Chapter 8 covers how to do it on a live table without blocking writes.

VACUUM FULL

VACUUM FULL is the honest sledgehammer. It writes the entire contents of the table into a new file with no wasted space, rebuilds every index as part of the same operation, and then swaps them in and drops the originals. Because the old copy is not released until the new one is complete, the operation needs free disk for a second copy of the largest relation involved. And it holds an ACCESS EXCLUSIVE lock for the whole rewrite, which means no reads, no writes, and, because of how the lock queue works, everything arriving behind it waiting too.

SET lock_timeout = '5s';      -- fail fast rather than stall checkout
VACUUM (FULL, ANALYZE, VERBOSE) inventory;

-- before: 900 MB     after: 616 kB    lock held: 71 seconds

Under half a megabyte of live rows came out of a 900 MB file and was written back at fillfactor 85 across 77 pages, which is where the 616 kB comes from — the 15% held back on every page is deliberate, and it is what keeps the HOT updates from the previous topic working. Setting lock_timeout first is not optional on a live system: this rewrite forms the same queue Chapter 6 described behind any ACCESS EXCLUSIVE request, so one long-running reader can stall the whole table with a rewrite that never even started. Failing after five seconds and retrying is cheaper than explaining the alternative.

pg_repack

pg_repack does the same job while the table stays online. It builds a copy in the background, uses triggers to capture the changes that arrive while it works, applies them, and then takes a brief exclusive lock only for the final swap. The disk requirement is the same second copy, and it adds two things VACUUM FULL does not have: moving parts that can fail partway and leave temporary objects behind, and a dependency on an extension being installed, which is a question you have to ask your managed provider before you plan around it. For a system that has no window in which a table can be unavailable for a minute, it is the right answer, and Chapter 14 covers what a provider does and does not let you install.

Rebuilding Indexes

When the table is fine and the indexes are not, the whole rewrite is the wrong tool. REINDEX CONCURRENTLY builds a fresh copy of one index while reads and writes carry on, then swaps it in — the routine answer for the density number pgstatindex reports. It needs room for both copies at once, and a failed run can leave an invalid index behind that has to be cleaned up by hand. Chapter 8 owns the procedure and its failure modes; the point here is that it is a separate operation with a separate cost, and doing the table without the indexes leaves the plans exactly as slow as they were.

Fixing the Generator

Every rewrite on this page removes a symptom. Four upstream causes produce it, and if none of them is addressed the same rewrite goes back on the calendar for next quarter. Per-table autovacuum settings that actually match the table's size and rate. A schema that lets updates be HOT — the right columns indexed and room reserved on the page. No transaction, replication slot or prepared transaction sitting on the vacuum horizon for hours, which Chapter 6 covers in detail. And detaching a partition instead of deleting ten million rows from a live table, which Chapter 11 covers when delivery_events gets partitioned.

A VACUUM FULL that has to be repeated on a schedule is a note in the calendar saying one of those four was never fixed. Cartwheel's inventory needed exactly one rewrite, and it needed it after the settings and the schema were corrected, because a rewrite performed first would have been 900 MB again inside a month.

The last thing to say about bloat is that a great deal of it should be left alone. A table sitting at a stable 20% with a write pattern that keeps reusing the same free space is not a problem; the free space is doing its job. Everything else is an outage bought with a dashboard. The number worth acting on is one that is both large and still growing, on a table whose reads you can show are paying for it.