No bloat measurement for PostgreSQL: we collect the whole cause chain and none of the damage

[erikdarlingdata]

Part of #2530.

The gap

We collect autovacuum lag (PgAutovacuumStatsCollector), wraparound risk (PgWraparoundStatsCollector) and the xmin horizon (PgXminHorizonCollector) — the entire cause chain for bloat — and nothing that measures the damage. A user can watch vacuum fall behind and has no way to see what it has cost them, which is the question they ask immediately afterwards.

The measurement decision, which is the substance

• pgstattuple is the canonical answer and gives exact live/dead tuple counts and free space. It performs a full relation scan, so on a large table it is far too expensive to run on a collection cadence. It is also an extension, so availability on managed PostgreSQL has to be checked rather than assumed.
• Estimate queries (the well-known pg_class.reltuples / relpages versus expected-size calculations) are cheap enough to run regularly and are approximate — they can be badly wrong on tables with unusual column widths or after a schema change.
• pgstattuple_approx sits between the two.

Picking one is the work. My inclination is the estimate on cadence with pgstattuple available on demand for a single table, so the routine path is cheap and the precise answer is reachable when someone is actually investigating — but that should be decided against a measurement on a real table, not on reasoning.

Note

Whatever ships must not present an estimate as though it were exact. A bloat number that is quietly ±40% will be used to justify a VACUUM FULL on a production table, and the difference between "estimated" and "measured" needs to survive into the read's payload rather than living only in a doc.

Source: PostgreSQL Wiki — Monitoring (names pgstattuple as the bloat tool).

[erikdarlingdata]

Shipped in #2560. Three corrections to the framing here, all of them things the measurement changed rather than confirmed.

1. "pgstattuple available on demand for a single table" is not implementable as a tool, and that is architectural rather than an omission. The MCP surface is strictly read-only over collected rows — there is no ad-hoc query path back to a monitored server, by design. So the routine/precise split proposed above cannot be built as "the estimate on cadence plus a pgstattuple verb". What ships instead is the reachable form of the same idea: every row records whether pgstattuple is installed on that database, and carries the exact command that would settle it (SELECT * FROM pgstattuple('schema.table');, or CREATE EXTENSION pgstattuple; first where it is absent). The escalation is a printed command a human runs, not a tool call.

2. The decision went the way you guessed, but the margin is worth recording. Measured rather than reasoned:

option	cost
pgstattuple	11.8 ms on a 54 MB fully-cached table (~4.6 GB/s), 860 ms over 2,001 tables
pgstattuple_approx	0.58 ms on the same table
statistics estimate	44 ms over the same 2,001 tables / 854 MB

The 19× is a floor on the ratio — those tables were tiny and entirely in shared buffers, and the gap widens with every gigabyte that is not. Note also that pgstattuple_approx's 0.58 ms is its best case and is misleading: it skips all-visible pages via the visibility map, so the pages it must still read are exactly the recently-written ones — on the churning table you actually care about, the visibility map is cold and it degrades toward the full scan. It shares the availability problem without solving the cost one, which is why it was not a middle option in practice.

3. The note about "quietly ±40%" understates it, and the reason is not the one I would have expected. With current statistics the estimate was within ~2 percentage points of pgstattuple on every table tested (75.14 vs 74.82; 60.73 vs 59.52; 0.58 vs 0.50). The danger is not the routine error, it is two specific states:

• Stale column-width statistics. Two byte-identical 8,998-page tables with identical true bloat of 10.93%: the one whose pg_stats.avg_width predated a widening UPDATE estimated 92.64%, the one analyzed afterwards estimated 11.01%. 81.7 percentage points, with nothing in the arithmetic to show it. mods_since_analyze is the signal that separates them (300,000 against 150,000 live rows vs 0), and it is a sound proxy rather than a coincidence — width statistics can only go stale through modifications.

• Permissions, which is the finding I did not expect and the one that would have shipped a tool telling everyone to VACUUM FULL everything. pg_stats is filtered by has_column_privilege(..., 'select'), and pg_monitor — the one grant the PostgreSQL runbook says Darling needs — confers no SELECT on user tables. Measured against a pg_monitor-only role on a live PostgreSQL 16 target: zero pg_stats rows visible, and the estimator did not fail. It returned 88.59% for a table whose true bloat is 0.50%, 95.03% for one that is really 74.82%, and 22.57% for one that is really 0.46%. Every row an argument for rewriting a production table. Adding pg_read_all_data (PostgreSQL 14+; the role does not exist on 13, where explicit GRANT SELECT is the only route — both verified) restored byte-identical agreement with a superuser's numbers.

So the "must not present an estimate as exact" requirement is implemented as suppression, not captioning: estimate_unavailable is load-bearing, the number is withheld rather than annotated, and what survives is the dead-tuple fraction from the server's own counters — narrower, but measured and needing no width model, so a permissions gap degrades the answer instead of removing it. The runbook now carries that grant with the reasoning.

One deliberate narrowing: the estimate is heap only, with TOAST reported beside it as a measured size rather than folded in. The classic estimator models TOAST as toasttuples / 4, which is a guess standing next to arithmetic, and pg_class.relpages on a TOAST relation is 0 until that relation is itself vacuumed — so the fold-in is both crude and frequently zero. Keeping the arithmetic to the heap is also what makes the accuracy figures above meaningful, since pgstattuple on a relation measures its heap and not its TOAST.

Index bloat is not covered and is filed as #2561 with the mechanism and the measurements that should decide it — REINDEX INDEX CONCURRENTLY is a far safer remedy than VACUUM FULL, so it is arguably the more actionable half.

[erikdarlingdata]

Shipped in #2560 (merged to dev): pg_table_bloat_stats at schema v85, hourly, per-database, writers only, plus get_pg_table_bloat and a Storage tab on both UIs.

The estimate is suppressed, not captioned, when its inputs cannot be trusted — and the measurements are why that call was made rather than shipping a disclaimer:

what was measured	result
Estimator vs pgstattuple, current stats	agrees within ~2 pp (75.14 vs 74.82; 60.73 vs 59.52; 0.58 vs 0.50)
Estimator vs pgstattuple, stale stats	two byte-identical 8,998-page tables, true bloat 10.93% — estimated 92.64% and 11.01%, i.e. 81.7 pp apart
Cost	44 ms vs pgstattuple's 860 ms over 2,001 tables — and that ratio is a floor

An estimator that is accurate to 2 pp on good input and off by 81 points on stale input is not a number to caption with a warning; it is a number to withhold. Hence suppression, with the reason given.

The permissions finding is the one I would not have predicted: a role with pg_monitor alone sees zero rows in pg_stats, and the estimator then returned 88.59% for a table genuinely at 0.50% — silently, with no error. Granting pg_read_all_data (PG14+, absent on 13) restored byte-identical agreement. A monitoring role that looks correctly least-privileged produces confident garbage.

One correction to this issue, mine: I proposed "pgstattuple on demand for a single table" as a tool. That is not implementable — the MCP surface is strictly read-only over already-collected rows and has no ad-hoc path to a monitored server. Each row now ships the exact command to run instead.

Two real bugs were caught in review, both introduced by me: a table analyzed while empty and then written to published an estimate anchored to a stale zero row count with no suppression (LiveTuples == 0 rather than >= 0), and the sort key covered only one of three suppression reasons, so a stale-stats row estimating 94.28% could take the top of the LIMIT.

Index bloat is deferred to #2561 with the mechanism and the measurements that should decide it — arguably the more actionable half, since REINDEX CONCURRENTLY is a safer remedy than anything table bloat offers.

Not verified: no live Aurora run, and bloat accuracy comes from one instance's fixtures — the direction held everywhere, but the magnitude is not calibrated across workloads.