Repository navigation
pg_index_bloat has never collected a row on its PostgreSQL target — 13 consecutive client-side deadline expiries, and the error text says to shrink the work rather than raise the deadline #3109
Description
pg_index_bloat has never successfully collected on its PostgreSQL target. get_pg_index_bloat over 168 hours returns status: empty, and the collector is now 13 consecutive ERROR attempts.
The collector's own error message states the cause and prescribes the fix
Quoted rather than paraphrased, because it is better than a summary:
"
pg_index_bloaton database '' hit its CLIENT-SIDE command deadline after 300,107 ms — its command timeout expired and Npgsql cancelled the read mid-stream, which is why the transport reports 'Exception while reading from stream' with no SQLSTATE. The statement was still running when it was cut off, so the work asked for does not fit the deadline; raising the deadline is the wrong half of that and shrinking the work is the right one. Nothing was collected this cycle: this is NOT 'there was nothing to collect'."
So this is not a transient failure, a permissions problem, or an empty source. The collector asks for more work than fits in 300 s on this target, every time.
Why it is worth an issue rather than a watch
- Nothing has ever been collected, so there is no partial series to reason from and no degradation to trend — the surface has been
emptyfor as long as it has been asked. - The pre-registered acceptance criteria from the earlier issue that closed on this (pg_index_bloat has failed every run since #2618 shipped: the 200-index budget admits 286GB on target-a, and the timeout it dies on cannot be classified #2997's own quotation of pg_index_bloat has never returned a row: pgstatindex reads every page of 1,517 indexes in one statement #2617) were ~1,517 rows with ~200 carrying
avg_leaf_density, largest first. Observed: 0 rows, 0 with density, in seven days. - The schedule is confirmed, so the failure is reproducible on a predictable clock. The attempt drifts, and a pre-registered prediction of ~19:10:39Z landed at 19:10:38.997Z. The next attempt is therefore predictable to the second, which makes this cheap to verify a fix against.
Suggested direction — the message's own, and the ordering matters
- Shrink the work per attempt. Fewer indexes measured per cycle, or a smaller per-database batch, so an attempt completes inside the existing deadline. The measured-are-largest ordering in the acceptance criteria suggests the collector already has a notion of priority to batch along.
- Do NOT raise the deadline. The error text rejects this explicitly, and it is right: a longer deadline on a statement that does not fit converts a failed attempt into a longer failed attempt, and on a shared store it holds a connection and adds load for the duration.
Acceptance criterion: an attempt completes with SUCCESS and a non-zero row count inside the current deadline — verifiable on the next drifted attempt, whose time is predictable.
One convention note for anyone re-deriving the schedule
The collection_log row is written at attempt completion, and the error reports the deadline expiring after 300,107 ms — so this attempt launched at ~19:05:39 and completed at ~19:10:39. Every prior attempt hits the same 300 s deadline, so the series is uniformly launch-plus-300 s and comparisons are like-for-like. But a prediction stated against the completion instant is not the same as one stated against the launch instant; pick one and say which.
Related
- pg_index_bloat has failed every run since #2618 shipped: the 200-index budget admits 286GB on target-a, and the timeout it dies on cannot be classified #2997 — closed, and its stated real-cluster acceptance criteria are the ones failed here.
- Collector store writes fail in a pattern that correlates with continuous-aggregate refresh windows, and a failed write silently costs one sample on collectors with no watermark #3099 — the same
Exception while reading from streamstring, and this collector's error text is what establishes that the string means a client-side deadline expiry rather than a server-side cancellation. That decomposition is recorded there.
Activity
erikdarlingdata commentedon Sep 6, 2026
The schedule prediction has a contingency, and the fix breaks it
The 3 ms accuracy of the drift prediction (~19:10:39Z predicted, 19:10:38.997Z observed) rests on a property that a successful fix removes.
Every attempt dies on the same 300 s deadline, so completion tracks launch by a fixed offset and a drift measured on completion instants is also a drift on launch instants. That is what makes the extrapolation exact.
The first attempt that does not die on the deadline breaks it. A successful — or differently-failing — attempt completes at launch-plus-actual-duration, so its completion instant no longer sits on the drift line, and a prediction extrapolated through it will be wrong by however much the duration changed.
Practical consequences:
collection_logshould note the row is written at completion and state which convention they used, since the two coincide only while the failure is deadline-shaped.Execute the pg_index_bloat work budget against live PostgreSQL
Route the live test's teardown through LiveStoreCleanup and install p…
Quote the measured index-size figures with the ceiling they were meas…
Bound pg_index_bloat by what pgstatindex is applied to, not by a join…
erikdarlingdata commentedon Sep 7, 2026
Claude posting for Erik Darling
The fix is merged (#3114) and this issue stays OPEN, on the #3030 precedent: its claim is a production measurement — zero rows in 168 hours — and no live target has run the fixed query.
The defect was not the deadline
All three work bounds — the size ceiling, #2617's count budget and #2997's byte budget — were quals on a
LEFT JOIN LATERALtopgstatindex. That placement bounds nothing: the planner cannot skip an inner side it has not evaluated, so the function ran once per candidate row and the qual only chose whether to keep an answer already paid for. Every attempt read every b-tree index on the instance while everyskipped_reasonin the output was correct.So the suggested direction in this issue — shrink the work, do not raise the deadline — was right about the deadline and wrong about why nothing had worked. The work had never been shrunk, in any of three shapes.
That also answers a question this issue could not: why #2617's and #2997's budgets both shipped pinned and inert. Their pins were source-text assertions, and no text assertion can distinguish a bounding qual from a non-bounding one. The new pin reads
Actual Loopsoff the query's ownEXPLAIN ANALYZEinstead — measured on PostgreSQL 17.11, the pre-fix query callspgstatindex215 times to report 200 indexes; the fixed query calls it 200, with byte-identical output.The pre-registered acceptance criteria no longer apply, and ~200 is now a FALSIFIER
This issue quotes #2997 quoting #2617: ~1,517 rows with ~200 carrying
avg_leaf_density, largest first. That bar was set against a 20 GB per-index ceiling. The fix uses 2 GB.SUM(index_bytes) WHERE avg_leaf_density IS NOT NULL <= 2147483648, read off the stored rows — that is the PR's actual claim.Three outcomes that must not read as success: zero rows carrying density; measured bytes over budget; duration anywhere near 300,000 ms.
Read
collection_logand a direct row count, not onlyget_pg_index_bloat— per #3121, a successful collection can still present as a broken read through that surface.Accepted coverage change, stated because it is not a defect
At a 2 GB ceiling, indexes at or above it are reported at their size with the ceiling's reason and are never measured — on a large target that includes the biggest and most reclaimable indexes on the instance. That is disclosed in the rows rather than hidden, and rotation of the measured set remains absent, so the measured set is the same one every cycle. Both are real gaps and neither blocks a first success.
erikdarlingdata commentedon Sep 7, 2026
Claude posting for Erik Darling
The defect this issue names is fixed, verified on a live target — and the collector's throughput is a separate question, now on #3153
Two findings, and neither should be read as qualifying the other.
pg_index_bloatcollected rows for the first timeAt 19:15:06Z on 2026-09-07, launch ≈19:10:54Z: SUCCESS, 252,940 ms, 2,500 rows. First ever on this target. #3114's fix is what made it possible, and it had never executed in production before today — it merged at
06:47:09Zwhile every box was running a build cut roughly 24 hours earlier, so tonight's run is its first execution. That was verified by ancestry with a negative control, not by timestamps alone: the merge commit is not an ancestor of the previously-installed build's source.Confirmed read-only from the store:
(database, schema, table, index)The round number is innocent. There is no
LIMITin the query, no runner-level row cap, and no literal2500anywhere in non-test code — the collector's only bounds are byte-based (a per-index measurement ceiling and a per-cycle work budget), and every bound selects the function's input relation rather than filtering its output. So 2,500 is the candidate census on the one readable database, not a ceiling.The pre-registered ~1,517-row expectation does not apply and should not be treated as a miss. That figure is this collector's own record of an unbounded statement on a different target — 1,517 indexes totalling 461 GB in one statement, which never finished and dropped the connection. It was a cautionary measurement, not a prediction.
Also worth stating plainly, because it was previously misattributed: this was never blocked on a missing extension. 49 of the ~50 targets on this store genuinely lack
pgstatindexand fail with42883in ≤82 ms; this target has never once returned42883and ran the function for five minutes. A 42883 fails at parse time in tens of milliseconds, so a long run is positive proof the function resolved. An earlier reading of mine applied that store-wide count to the one target it does not describe, and concluded aCREATE EXTENSIONwas owed to another team. That was wrong and is withdrawn.And the collector measured exactly one index
Of the 2,500 rows, 1 carries a measurement:
skipped_reasonSo a 252.9-second run produced one measurement and 2,499 placeholders. Nothing is silently skipped — every unmeasured index is recorded at its size with an explicit reason, which is exactly what this collector's design promises and is why the result is legible at all rather than an unexplained empty table.
The mechanism is structural: the per-cycle work budget equals the per-index measurement ceiling, and the query measures largest-first, so a single index anywhere near the ceiling consumes the whole cycle by itself. The collector's own doc explains correctly why the budget may never sit below the ceiling — the band between them would be legitimate candidates that earn no "too large" reason and still exceed the cycle on their first row. At equality that band is empty, which is right. Equality also makes one-index-per-cycle the expected outcome rather than an anomaly.
That question lives on #3153, filed separately with the geometry, the 43 permanently unmeasurable indexes, and the observation that the 200-index count budget never binds at this geometry. It is not a defect in #3114 and it is not a reason to reopen anything here.
Disposition
This issue's criterion is met: the collector no longer fails to collect, the transport works, the parse works, and the result is self-describing. The margin is worth recording rather than celebrating — 252,940 ms against a 300,000 ms deadline is 47 seconds of headroom, and that headroom is paying for the scan of 2,499 candidates it then declines to measure, not for coverage.
n=1. A second successful run would make this a regime rather than an event.
erikdarlingdata commentedon Sep 7, 2026
Claude posting for Erik Darling
Closing: the claim in the title is no longer true. 2,500 rows collected at 19:15:06Z, grain verified clean, on the first production execution of #3114's fix.
Closing on n=1, and the standard is the same one applied to #3030 earlier today: one fully-populated, independently-verified result against a claim of never. The row count, the distinct-index count, the database and server counts and the
skipped_reasonbreakdown were all read from the store rather than taken from the run's own report. If it fails to repeat, that is a regression and a new issue rather than a reason to hold this one open.The throughput question — one index measured of 2,457 candidates, because the cycle work budget equals the per-index measurement ceiling — is #3153, and it is not a defect in #3114.
Closing by hand; closing keywords do not fire here, since merges land on
devwhile the default branch ismain.erikdarlingdata commentedon Sep 7, 2026
Claude posting for Erik Darling
Correction to my own comment above: the 1,517 figure is this target's other census, not a different target
I wrote that the ~1,517-row expectation was "this collector's own record of an unbounded statement on a different target." The "different target" part is wrong, and it was an assumption rather than a measurement.
Verified read-only, same
server_id, same day:pg_index_bloatpg_index_usage_statsThe two collectors are identical on total bytes, identical on the over-ceiling set, and identical index-for-index at 1 MB and above. They diverge by 983 rows entirely inside the sub-1 MB tail.
The collector doc's "1,517 indexes totalling 461 GB" matches
pg_index_usage_stats' census of this target exactly — byte total and count both. So it is not a cautionary reading from elsewhere; it is the number the sibling collector reports here, today.What this does and does not change.
It does not change this issue's disposition. 2,500 is still not a cap — there is no
LIMIT, no row limit, and no literal2500in non-test code, and the grain is clean at 2,500 distinct(database, schema, table, index)tuples. The agreement on bytes and on the entire ≥ 1 MB population is stronger evidence for that than my original argument was.It does mean one of the two censuses is wrong, and the direction is counter-intuitive: the btree-only collector returns 983 more rows than the all-access-method one. Candidates worth checking rather than guessing between: a usage/scan-activity filter or a size floor on
pg_index_usage_stats, differing database coverage, or differing treatment of catalog indexes. No byte-level conclusion on this issue moves either way, since the disagreement is worth ~313 MB between 983 objects.And a note on how I got it wrong, because it is the same shape twice in one evening. I attributed a figure to a population without checking which population it described — the identical error I had already made and withdrawn on this very issue, where I applied a store-wide
42883count to the one target it did not describe. First time the number was real and the population assumed; this time the number was real and its source assumed. My first attempt to verify it then repeated the shape a third time: I compared this single-server census againstpg_index_usage_statsacross all 49 servers (3,733 rows, 5,953 GB) and had to throw the result away.The 2,500-versus-1,517 census disagreement is recorded for follow-up and is not a defect in #3114.
Let a zero-output collector's own notes answer for its zero, instead …