Measuring bloat honestly · PostgreSQL

Bloat is discussed more than it is measured, and the number most people cite for it is measuring something else.

Dead tuples are not bloat

n_dead_tup counts versions vacuum has not yet reclaimed. It measures outstanding work.

Bloat is space inside the relation that holds no live data. It measures what the table is costing you.

The two are related and they are not the same, which the following pair of measurements makes unarguable. Both tables have had every dead tuple successfully removed by vacuum.

Read-only / Safea table with zero dead tuples that is half empty— Captured on 18.6. vac_scatter had 50% of its rows deleted scatter-wise, then a successful VACUUM.

$ psql -U postgres -x -c "SELECT * FROM pgstattuple('vac_scatter')"

table_len          | 48193536
tuple_count        | 100000
tuple_len          | 23200000
tuple_percent      | 48.14
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 24075876
free_percent       | 49.96

Read-only / Safea healthy table, for comparison— Captured on 18.6. Loaded once and never updated.

$ psql -U postgres -x -c "SELECT * FROM pgstattuple('narrow')"

table_len          | 3629056
tuple_count        | 100000
tuple_len          | 3200000
tuple_percent      | 88.18
dead_tuple_count   | 0
dead_tuple_len     | 0
free_percent       | 0.51

Zero dead tuples and 49.96% free space. A monitoring system that alerts on n_dead_tup reports the first table as perfectly healthy while it occupies twice the space it needs, reads twice as many pages per scan, and consumes twice its share of shared buffers.

Question	Metric
Is vacuum keeping up?	`n_dead_tup`, `last_autovacuum`
How much space is the table wasting?	`pgstattuple.free_percent`

Watch both. They fail independently.

Measuring it

pgstattuple is exact, and reads the entire relation. On a 500 GB table that is 500 GB of I/O, so it is a deliberate action, not something to put on a five-minute dashboard.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');           -- exact, full scan
SELECT * FROM pgstattuple_approx('orders');    -- sampled, much cheaper
SELECT * FROM pgstatindex('orders_pkey');      -- index-specific

pgstattuple_approx samples and uses the visibility map to skip pages it can trust, which makes it usable on large tables at the cost of precision. Use it for routine checks and the exact version to confirm before acting.

Estimation queries based on pg_class and pg_stats avoid reading the table at all, computing an expected size from column widths and row counts and comparing it against the actual size. They are what most monitoring systems use, and they are genuinely useful for finding candidates.

They are also estimates, and they are wrong in predictable directions: they do not know about fillfactor, they handle TOAST and alignment padding approximately, and they can report substantial bloat on a table that has none. Treat an estimation query as a way to decide which tables deserve a pgstattuple run, never as grounds for taking a lock.

Free space is not automatically a problem

Before treating free_percent as damage, ask what the table does.

A table under steady churn needs free space. It is the working set the free space map hands out for new versions, and it is what makes HOT updates possible. A table at 30% free with a stable size that has been running for months is at equilibrium, and rewriting it will only cause it to grow back to the same place — while costing you a lock.

The cases that genuinely warrant action:

A one-off event. A bulk delete or a migration that removed a large fraction of the rows, and the table will not refill.
Growth that is not levelling off. The size climbs month over month and free_percent climbs with it. Something is wrong upstream, and rewriting treats the symptom.
The free space is not reachable. Rows are wide enough that the free fragments cannot hold them.

For anything else, the honest answer is often that a 30%-free table is working correctly and the reporting threshold is what needs adjusting.

What VACUUM FULL actually costs

VACUUM FULL rewrites the table into a fresh file with no free space, then swaps it in and drops the old one. It works, and the numbers are worth knowing before you schedule one.

Read-only / Safethe result on the half-empty table— Captured on 18.6. Same table as the first measurement in this lesson.

$ psql -U postgres -c "VACUUM FULL vac_scatter" -x -c "SELECT * FROM pgstattuple('vac_scatter')"

Time: 64.011 ms

table_len          | 24100864     (was 48193536)
tuple_percent      | 96.26        (was 48.14)
free_percent       | 1.79         (was 49.96)

Now the cost. A 4,100 MB table; VACUUM FULL started, and one second later a plain SELECT count(*) was issued from another session with a four second lock_timeout.

Destructivea plain SELECT during VACUUM FULL— Real error from 18.6. Not a write. A read.

$ psql -U postgres -c "SET lock_timeout='4s'; SELECT count(*) FROM big_bloat"

ERROR:  canceling statement due to lock timeout
Time: 4000.665 ms (00:04.001)

-- meanwhile, in the other session:
VACUUM FULL big_bloat;
Time: 5707.921 ms (00:05.708)
-- size: 4100 MB -> 2733 MB

Read-only / Safethe locks it holds while running— Captured on 18.6 from a third session during the rewrite.

$ psql -U postgres -c "SELECT l.pid, l.mode, l.granted, c.relname FROM pg_locks l JOIN pg_class c ON c.oid=l.relation WHERE c.relname LIKE 'big_bloat%'"

 pid | mode                | granted | relname
-----+---------------------+---------+----------------
245 | AccessExclusiveLock | t       | big_bloat_pkey
245 | ShareLock           | t       | big_bloat
245 | AccessExclusiveLock | t       | big_bloat
247 | ShareLock           | t       | big_bloat
247 | AccessExclusiveLock | t       | big_bloat_pkey
(5 rows)

Three things to take from that.

It blocks reads. ACCESS EXCLUSIVE conflicts with everything, including SELECT. For the whole duration, the table does not exist as far as the application is concerned.

It needs disk for a second complete copy. 4,100 MB and 2,733 MB had to exist simultaneously. A VACUUM FULL on a table larger than the remaining free space fails partway through — and it tends to be run precisely when disk is short, which is what makes this trap effective.

The duration scales with the table. 5.7 seconds for 4 GB on this hardware. Measure your own rate on a copy before committing to a window; do not extrapolate from someone else’s number.

The alternatives, in order of preference

Do nothing. Establish first that the free space is not the working set of a churning table. Most reported bloat is this.

Fix the cause. If the table is bloating because vacuum cannot keep up, rewriting it buys a few weeks. Lesson VIII-04’s six modes come first.

REINDEX CONCURRENTLY. If the bloat is in indexes rather than the heap — which pgstatindex will tell you — this rebuilds them without a long exclusive lock. Often the whole answer, because index bloat is common and heap bloat is over-diagnosed.

An online rewrite tool. pg_repack and similar rebuild the table while it stays available, taking a brief lock at the start and end rather than throughout. This is the standard answer for a large table on a system that cannot take the outage. It costs disk for the second copy just as VACUUM FULL does, and it is an external dependency to install, test and keep current — treat it as such rather than as a command.

VACUUM FULL. Correct when you have a maintenance window long enough, disk for a second copy, and a table that will not simply re-bloat. It is not the first answer and it is not never the answer.

Partition and drop. For time-series data, the cheapest way to reclaim space is DROP TABLE on an old partition. No rewrite, no lock on live data, near-instant. If the bloat problem is really a retention problem, this is the structural fix.

Index bloat behaves differently, and REINDEX CONCURRENTLY is not free either

Heap bloat and index bloat have different mechanics and are worth separating.

A B-tree page is only reclaimed when it becomes completely empty. A page holding one live entry out of a former four hundred stays in the tree, so an index under a delete-heavy or update-heavy workload accumulates sparse pages that vacuum cannot remove. This is visible in the vacuum output from lesson VIII-01: the tail-delete case removed 271 index pages, and the scattered-delete case removed zero, despite deleting the same number of rows.

pgstatindex measures it:

SELECT * FROM pgstatindex('orders_pkey');
-- avg_leaf_density is the number to read; a healthy B-tree sits near 90

REINDEX CONCURRENTLY builds a replacement index alongside the original and swaps it in. It avoids the long lock, and it has costs of its own:

It takes brief ACCESS EXCLUSIVE locks at the start and end. On a busy table, brief still means a queue forms behind it.
It needs disk for both copies simultaneously.
It can fail and leave an invalid index behind, named with an _ccnew suffix. That index is not used by the planner but is maintained on every write, so it costs write throughput while providing nothing. Check for them afterwards:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
  FROM pg_index WHERE NOT indisvalid;

It cannot be run inside a transaction block, and on a partitioned table it must be applied per partition.

None of that makes it a bad tool. It is the right first move for index bloat. It is worth knowing that “concurrently” means “without a long lock”, not “without consequences”.

What to take from this

n_dead_tup measures vacuum’s backlog. pgstattuple.free_percent measures bloat. A table can be perfect on one and terrible on the other.
Estimation queries find candidates. pgstattuple decides.
Free space in a churning table is the working set, not damage.
VACUUM FULL blocks reads, needs disk for a second copy, and takes time proportional to the table.
REINDEX CONCURRENTLY first, an online rewrite tool second, VACUUM FULL when the window exists. Dropping a partition beats all of them when the problem is retention.

Cross-course references

Linux for Production Sysadmins — Part XIV (Filesystems) covers why the space a rewrite needs is not the space it eventually returns, and Part XLI (Storage Performance) covers measuring the cost of the rewrite itself.
Observability for Production Sysadmins — Part XXVII (Dashboard anti-patterns) covers why a single bloat estimate on a dashboard is worse than no number at all.

Dead tuples are not bloat

Measuring it

What VACUUM FULL actually costs

What to take from this

Cross-course references

Quiz