No index bloat estimate for PostgreSQL, where the remedy is the safe one

Part of #2530. Deferred out of #2542 rather than dropped — the mechanism, the options and the measurement that should decide are below.

The gap

#2542 ships a heap bloat estimate (pg_table_bloat_stats, schema v85). Index bloat is not covered, and on PostgreSQL it is arguably the more actionable half:

The remedy is much safer. REINDEX INDEX CONCURRENTLY rebuilds an index without blocking reads or writes. VACUUM FULL on a heap takes an ACCESS EXCLUSIVE lock for the entire rewrite. So a bloated index is a finding somebody can act on in business hours, and a bloated heap usually is not.
B-tree indexes bloat differently from heaps and often worse. A heap reuses free space through the free space map; a btree page that empties is only reclaimed when the whole page is empty, so a churning index on a monotonically-increasing key can hold a large share of dead space indefinitely without the heap looking bad at all.
pg_index_usage_stats (No index usage collection for PostgreSQL, where an unused index costs more than on SQL Server #2541, v84) already collects pg_relation_size(indexrelid) per index, so the measured side is already stored. What is missing is the expected size to compare it against.

The mechanism

The btree estimator is a different query from the heap one, not a parameterisation of it. It needs, per index:

pg_index.indexrelid → the indexed columns (indkey, plus pg_get_expr(indexprs, ...) for expression indexes)
pg_stats.avg_width and null_frac for each of those columns on the parent table
the btree tuple overhead: an index tuple header plus the item pointer, and the fill factor (reloptions, default 90 for btree, not 100)
pg_class.relpages for the index itself

...producing an expected leaf-page count to compare against relpages. The published version of this is ioguix's btree_bloat.sql, which is the sibling of the table query #2542 adapted.

What has to be decided by measurement, not reasoning

#2542 established that this class of estimator can be catastrophically wrong in ways the arithmetic cannot see, so the same evidence is needed here before anything ships:

Accuracy against ground truth. pgstatindex() (from pgstattuple) reports avg_leaf_density and leaf_fragmentation for a btree. Compare the estimate against it across index shapes: narrow int keys, wide text keys, multi-column, partial, expression, and a DESC/NULLS FIRST ordering. The estimate must be measured on a REINDEXed index too — an index that has just been rebuilt is the known-good case, and an estimator that reports 30% bloat on a freshly-reindexed index is unusable at any accuracy.
The same permissions trap. pg_stats is filtered by SELECT privilege and pg_monitor does not confer it, so a pg_monitor-only role sees no column widths and this estimator will return confident nonsense exactly as the heap one did (measured in No bloat measurement for PostgreSQL: we collect the whole cause chain and none of the damage #2542: 88.59% reported for a table whose true bloat is 0.50%). Whatever ships needs the same estimate_unavailable suppression, not a caption.
Expression indexes may be unmodellable. pg_stats carries widths for an expression index's own statistics under the INDEX's name rather than the table's, and only if the index has been analyzed. Worth measuring whether that is reliably present; if not, expression indexes should be explicitly excluded rather than estimated badly.
Fan-out cost. This would be per-index on top of an existing per-index collector. The cheapest shape is almost certainly columns on pg_index_usage_stats rather than a new table — the collector is already reading pg_index and already paying the fan-out — but that adds a rung and widens a table that is already 24 payload columns, so it is a decision rather than an obvious win.

Notes

Cadence should probably follow pg_index_usage_stats (daily) rather than pg_table_bloat_stats (hourly): index bloat accumulates over days, and the two would then share one query.
avg_leaf_density from pgstatindex is a density, not a bloat percentage — 100 - avg_leaf_density is not directly comparable to the page-count ratio the estimator produces, and conflating them would produce a wrong accuracy claim. Whoever does this should state which quantity they are comparing.
Whatever ships must not recommend REINDEX on the strength of an unconfirmed estimate any more than No bloat measurement for PostgreSQL: we collect the whole cause chain and none of the damage #2542 recommends VACUUM FULL — same rule, same reason.

Measured against pgstatindex ground truth on PostgreSQL 17. This is the evidence this issue said had to exist before anything ships, and it changes what should be built.

Rig: six real btrees with genuine churn (delete 60-80% of rows, then VACUUM), one of them freshly REINDEXed as the known-good control.

Accuracy, by index shape

index	shape	`pgstatindex` free %	estimator	verdict
`ix_narrow_int`	narrow int	69.79	66.67	within ~3pp
`ix_wide_text`	wide text	76.44	76.17	within 0.3pp
`ix_desc`	`DESC NULLS FIRST`	81.77	79.85	within ~2pp
`ix_multi`	freshly REINDEXed	10.49	6.90	passes the bar
`ix_partial`	partial	10.50	-56.14	broken
`ix_expr`	expression	81.46	-295.84	broken

The REINDEXed control passes. This issue set the bar at "an estimator that reports 30% bloat on a freshly-reindexed index is unusable"; it reports 6.9%. Worth noting why that is not zero and should not be: fillfactor is 90, so a fresh btree is ~90% dense and ~10% free is normal. Any read built on this must not present that 10% as bloat.

Partial indexes are unmodellable as written. The estimate uses pg_class.reltuples for the parent table, but a partial index contains only the rows matching its predicate — so expected pages are computed for 200k rows when the index holds 20k. Result: -56%.

Expression indexes are worse, and for a different reason. pg_stats carries no row for lower(b) under the table's column names, so avg_width falls back to the default and expected pages explode. Result: -296%. This confirms the suspicion in the issue body: they should be excluded explicitly, not estimated badly.

Both failures present as NEGATIVE bloat — the estimator claiming the index is smaller than it should be, which is nonsense on its face and is the cheap signal for suppression.

The permission trap, confirmed for indexes too

Same shape as #2542 measured on the heap estimator, and if anything starker:

pg_stats rows visible to a pg_monitor-only role : 0
pg_stats rows visible to superuser              : 8

Every estimate for that role becomes garbage, and confidently so:

ix_desc        -937.39
ix_multi      -4498.28
ix_partial    -9928.07
ix_wide_text   -134.46

pg_monitor does not confer the SELECT privilege pg_stats filters on. So a least-privilege monitoring login gets no column widths and the estimator produces numbers rather than an error — exactly the failure #2542 hit at 88.59% for a table whose true bloat was 0.50%.

What this means for the build

Ship the estimate only for plain btrees on scalar columns. Exclude partial (indpred IS NOT NULL) and expression (indexprs IS NOT NULL) indexes by predicate rather than trying to model them.
Suppress on missing statistics, do not caption them. If any indexed column has no pg_stats row, the answer is estimate_unavailable with the grant to fix it — the same suppression No bloat measurement for PostgreSQL: we collect the whole cause chain and none of the damage #2542 shipped. A negative result is a second, independent tripwire and should also suppress.
Do not present the fillfactor headroom as bloat. ~10% free on a fresh btree is the design, and a read that flags it will cry wolf on every healthy index.
Cadence daily, sharing pg_index_usage_stats' query, as the issue suggests — nothing measured here argues against it.

What I have not done

I stopped at the measurement rather than shipping the collector. The accuracy question this issue gated on is now answered, and the answer is "yes, but only for a specific subset, and only when statistics are visible" — which is a materially different collector from the one the issue imagined, so it is worth being explicit before building it rather than after.

Rig teardown done; nothing left running.

Ran all four measurements this issue says must decide it. Three confirm what it suspected; the fourth inverts the design.

Rig: PostgreSQL 17, a 400k-row table with seven index shapes (int key, wide text, multi-column, partial, expression, DESC NULLS FIRST, and the pkey), churned with updates and deletes, plus a 3M-row table for cost.

1. Ground truth, and the density trap this issue flagged in its notes

Freshly built, zero bloat by construction:

index        avg_leaf_density  leaf_fragmentation  pages
ix_narrow          91.48              0             338
ix_partial         90.00              0             551
t_pkey             90.08              0            1099
ix_wide            90.82              0            8382

So 100 - avg_leaf_density is ≈ 10% on a PERFECT index. The note was right to warn about it, and now there is a number: conflating density with bloat gives every index in the fleet a ~10% false bloat floor. It is also not a constant — after REINDEX the same indexes landed between 87.07 and 90.81, so "subtract from 90" is not a fix either.

After churn, the same indexes: density 38.8–51.6, fragmentation ~50, and t_pkey at 2196 pages against 943 after REINDEX — a true bloat of 57%. That is the ground truth any estimator has to be measured against.

2. The permissions trap reproduces exactly

superuser         : 7 rows in pg_stats for the table
pg_monitor only   : 0 rows

Confirmed for the parent table AND for the expression index's own stats row. Meanwhile pg_class.relpages stays readable. So the estimator keeps its denominator and loses its numerator, which is precisely the shape that produced #2542's 88.59%-against-a-true-0.50%.

It is cleanly detectable, which is what matters for suppression: has_table_privilege('t','SELECT') returns false and the stats-row count is 0, both readable by the monitoring role itself. So estimate_unavailable is implementable without guessing.

3. Expression indexes ARE modellable — this issue's suspicion does not hold

pg_stats does carry the width, under the index's name rather than the table's:

tablename | attname | avg_width
ix_expr   | lower   | 132

and it was present after an ordinary ANALYZE of the parent table, with no separate analyze of the index. So they should be included rather than excluded — subject to §2, since that row is hidden by the same privilege filter.

4. The finding that changes the design: `pgstatindex` works for `pg_monitor`

pg_monitor-only role, no SELECT on the table:
  pg_stats rows            : 0
  pgstatindex('ix_narrow') : 90.81   <- the true answer

pg_monitor includes pg_stat_scan_tables, and pgstattuple grants EXECUTE on its functions to that role. So under exactly the permission set where the estimator goes blind, the exact function still works.

That reframes the whole issue. The estimator exists to avoid a full index scan — but it is unusable for a pg_monitor role, and a pg_monitor role is what we run as.

The cost, measured deterministically rather than by clock

pgstatindex on a 491 MB / 62,840-page index touched exactly 62,840 blocks — measured as a pg_statio_all_indexes delta after pg_stat_reset(). It reads every page of the index, once, and that is the honest cost statement.

I am deliberately not quoting the wall-clock numbers I also collected: they ranged 33–115 ms across runs in a direction that made no sense (the "cold" run after a restart was faster than the warm one), because the host page cache survives a container restart. The block count is exact; the timings from this rig are not evidence.

Recommendation

Prefer pgstatindex where pgstattuple is present; estimate only where it is not. Concretely:

Exact path — pgstatindex, giving avg_leaf_density and leaf_fragmentation. Works under our permissions, needs no pg_stats, and models partial/expression/multi-column indexes without special cases because it counts real pages rather than modelling them.
Bounded by size and cadence. Cost is one full read of the index, so it wants a size ceiling (skip indexes above some GB threshold and say so, rather than silently) and the daily cadence this issue already suggests — sharing the pg_index_usage_stats query, as proposed.
Estimator as the fallback only, when pgstattuple is absent AND pg_stats is readable. When neither holds, emit the miss rather than a number.
Store the density, not a derived bloat percentage. Given §1, a stored "bloat %" would bake in the 10% floor; storing what the server reported keeps the row honest and lets the interpretation change without a migration — same rule the plan-readiness collector follows for GUC text.
Never recommend REINDEX off an estimate, per this issue's own last note. Off pgstatindex it is a defensible recommendation, because that is a measurement.

One thing I could not check

Whether pgstattuple is available on Aurora — the AWS SSO session expired mid-session and re-authenticating is interactive. That check decides whether the exact path is the primary or the fallback on this fleet, so it should happen before anyone writes code. Authenticate to AWS, then read the extension list for aurora-postgresql.

Merge pull request #2598 from erikdarlingdata/feature/2561-pg-index-b…

Shipped in #2598 as schema v94 — measured, not estimated, which inverts what this issue proposed and for a reason worth recording.

The issue's plan was to port the ioguix btree estimator off pg_stats column widths. Measured, that is the one route that cannot work for the role this product runs as: pg_stats returns ZERO rows to a pg_monitor-only login, because the view filters on has_column_privilege — the same trap that produced #2542's 88.59% against a true 0.50%. Meanwhile pgstatindex does run for pg_monitor, because pgstattuple grants EXECUTE to pg_stat_scan_tables. So under our exact permissions the estimator is blind and the exact function works.

Answering this issue's four measurement questions directly:

Accuracy vs ground truth — moot, because we now use the ground truth. But the freshly-REINDEXed baseline the issue asked for was measured and it settles the notes' warning: a zero-bloat index reads 89.98–91.48, and 87.07–90.81 after REINDEX. So 100 - avg_leaf_density invents ~10 points of bloat on a perfect index, and there is no constant to subtract. Density is stored raw; the reclaimable estimate is computed against a 90% floor and shown beside it, never instead of it.
The permissions trap — reproduces exactly, and it is what decided the design rather than something to suppress around.
Expression indexes — not the blocker the issue expected. pgstatindex counts real pages, so partial, expression, multi-column and DESC/NULLS FIRST indexes need no special handling at all. What does need handling is the index method: it RAISES on GIN, BRIN and hash, so one of those would fail the whole collection every cycle. Btrees only, filtered behind an OFFSET 0 fence.
Fan-out cost — it reads every page (exactly relpages blocks; 62,840 for a 491 MB index), so daily cadence and a 20 GB ceiling. Nothing is silently capped: an oversized index still gets a row with a stated reason and sorts first, because unknown is not zero and it is the likeliest big win.

Also taking the issue's own last note seriously — the read ranks by reclaimable bytes, never density, because a 64 KB index at 20% tops a density-ranked list and is worth 50 KB next to a 10 GB index at 45% worth 5.37 GB.

Availability is now a first-class answer rather than a guess: #2588's pg_extension_availability reports whether pgstattuple is installed, available-and-one-command-away, or absent.

Availability confirmed on real Aurora, which upgrades this from a defensive design to the primary path on this fleet.

The collector was written to fail gracefully if pgstattuple were absent. Measured on the aurora-target-a Aurora cluster via #2588's extension collector:

pgstattuple   available   1.5

So exact index bloat is reachable on Aurora with one CREATE EXTENSION — it is not a self-hosted-only nicety, and the estimator fallback stays the fallback rather than becoming the real answer here.

Nothing to change; recording it so nobody re-opens the availability question later.

Correcting my earlier comment on this issue, and it is good news.

I reported pgstattuple as available on the Aurora target — installable, one CREATE EXTENSION away. That came from pg_extension_availability, which reads pg_extension from a single connection. pg_table_bloat_stats, which runs per database, reports pgstattuple_available = true for all 361 tables it measured — i.e. pg_extension has a pgstattuple row in the database those tables live in.

So pgstattuple is already installed in the application database on that cluster. The exact-measure path in this collector works there today with no install step; it is the primary path, not a fallback needing provisioning first.

The disagreement between those two readings is itself a defect — filed as #2599. pg_extension is per-database and pg_extension_availability records no database_name, so its installed/available answer depends on which database the target is configured for and nothing in the row says which. Until #2599 lands, trust the per-database collectors' extension readings over pg_extension_availability's for any "can I use this here" question.

Correct the index-bloat grant claims: there is no statistics-based es…

Document the PostgreSQL grant and extensions that decide whether a co…

Retire the census prose everywhere, not just where the diff touched

Estimate PostgreSQL index bloat from statistics instead of walking ev…

Navigation Menu

Repository navigation

No index bloat estimate for PostgreSQL, where the remedy is the safe one #2561

The gap

The mechanism

What has to be decided by measurement, not reasoning

Notes

Accuracy, by index shape

The permission trap, confirmed for indexes too

What this means for the build

What I have not done

1. Ground truth, and the density trap this issue flagged in its notes

2. The permissions trap reproduces exactly

3. Expression indexes ARE modellable — this issue's suspicion does not hold

4. The finding that changes the design: `pgstatindex` works for `pg_monitor`

The cost, measured deterministically rather than by clock

Recommendation

One thing I could not check

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

No index bloat estimate for PostgreSQL, where the remedy is the safe one #2561

Description

The gap

The mechanism

What has to be decided by measurement, not reasoning

Notes

Activity

erikdarlingdata commented on Aug 24, 2026

Accuracy, by index shape

The permission trap, confirmed for indexes too

What this means for the build

What I have not done

erikdarlingdata commented on Aug 25, 2026

1. Ground truth, and the density trap this issue flagged in its notes

2. The permissions trap reproduces exactly

3. Expression indexes ARE modellable — this issue's suspicion does not hold

4. The finding that changes the design: pgstatindex works for pg_monitor

The cost, measured deterministically rather than by clock

Recommendation

One thing I could not check

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 25, 2026

erikdarlingdata commented on Aug 25, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions

4. The finding that changes the design: `pgstatindex` works for `pg_monitor`