pg_index_bloat has never returned a row: pgstatindex reads every page of 1,517 indexes in one statement

[erikdarlingdata]

Found by dogfooding v99 on the Aurora target. pg_index_bloat has never returned a single row since it shipped — rows_ever=0 — and it fails like this:

2026-08-25 18:14:21 | ERROR | Exception while reading from stream

That is an Npgsql connection-level failure, not a classified miss, so it also does not degrade to the named non-fatal skip every other PostgreSQL collector uses.

Cause: I bounded the wrong dimension

pgstatindex reads every page of the index — that is what makes it a measurement rather than an estimate, and it is why #2561 chose it over the ioguix estimator, which returns nothing under pg_monitor.

The collector carries MeasureCeilingBytes = 20 GB, which bounds any single index and records skipped_reason for the ones above it. Nothing bounds the total. On the target:

indexes_tracked = 1517
total_bytes     = 461 GB

So one collection reads up to 461 GB of index pages in a single statement, on the default command timeout, against a production cluster. It never finishes, the connection drops mid-read, and the cycle records an ERROR.

A per-index ceiling and a total-work ceiling are different things and I only built the first.

Why the local rig did not catch it

Two indexes on one table. The measurement was correct and the design question — how much work is this in total — never arose, because the answer was "none". This is the failure mode a container cannot show you.

Fix

Bound the total work, not just each index:

1. Rank candidates by size and take the top N per cycle, largest first — bloat that matters is concentrated in big indexes, and a small index at 40% density is worth kilobytes.
2. Record what was left unmeasured. skipped_reason already exists for exactly this and must say not measured this cycle (work budget) rather than leaving the reader to think the index is fine.
3. Add a CommandTimeoutSecondsOverride so a slow single index yields a classified timeout rather than a dropped connection — index_object_stats already does this for the same reason ([BUG] index_object_stats collector times out (30s) as a single all‑or‑nothing cross‑database sweep - yields no index data #1135).

Rotating the window across cycles is tempting but should wait: a fixed top-N by size is predictable, and the retention on this table is 90 days, so a large index measured daily is plenty of history.

This also means the exact-bloat claim on #2561 is unverified in production. It was verified on a rig with two indexes; on a real cluster the collector has produced nothing at all.

[erikdarlingdata]

Fix merged (#2618) and the pgmonitor box is running a build that carries it — hash 9321077789a8… verified, config unchanged.

Not yet verified against the real cluster, and I am not claiming it is. pg_index_bloat is a daily collector whose last run was the failing one at 18:14, so its next run is tomorrow ~18:14. Forcing it would mean writing to the store's schedule config on a live box, which is not a trade worth making to get the answer a few hours earlier.

What IS verified: the query against 212 real btree indexes — 200 measured, 12 returned carrying the work-budget reason, none dropped.

What tomorrow's run has to show:

• status = SUCCESS with a non-zero row count, where every previous run in this collector's life was ERROR / rows=0
• 1,517 rows stored — the budget caps what is MEASURED, not what is returned, so every index should still appear
• ~200 with avg_leaf_density populated, the rest carrying not measured this cycle (work budget)
• the measured ones being the LARGEST, since that is the whole justification for ranking by size

If the row count comes back near 200 rather than 1,517, the budget is filtering instead of labelling and the fix is wrong in the direction that hides indexes.

[erikdarlingdata]

Closing — the 200-index work budget and the 300s timeout shipped, and pg_index_bloat now returns rows against a real target for the first time: 160 indexes measured on the self-hosted rig in 56ms.

Worth recording why it looked fixed-but-silent for a day afterwards: the collector was ALSO carrying the payload-arity bug (#2622) and the partial-failure hole (#2623), so even with the budget in place it reported SUCCESS with zero rows. Three independent defects stacked on one collector, each of which made the next invisible.