how a stale `reltuples` actually happens, and how it mostly does not
- Upgrade
- 26.0.0 → 26.7.1
- Scale
- 100,002 users, partialImport provenance
- Database
- Postgres 16 stock
- Condition
- routine — no adverse condition applied
Why this run existed
Run A showed that Keycloak decides whether to build an index from
pg_class.reltuples, and forced the interesting case by writing to the catalogue
by hand. That proves a mechanism. It does not prove anyone hits it, and a finding
published on the strength of a hand-edited catalogue row deserves the scepticism
it would get.
This run is the attempt to reach the same state without touching pg_class.
Two of the three attempts failed, and saying so is the point.
Environment
baseline-26.0.0-typical-100k restored (100,002 users, partialImport
provenance), then extended with bin/seed-sql.sh. Postgres 16 stock — in
particular autovacuum on, autovacuum_naptime 60s. Hetzner CCX33, single
container, JVM -Xms1g -Xmx4g, official image, start with an external DB.
Outcome
- Both negative attempts reproduced the correct behaviour: index skipped, 211 rows, model 26.7.1
- Attempt 3 reached ready with the index built and no warning
-
bin/seed-sql.sh --no-analyzeadded, so the field's loader can be modelled
Verification
| Claim | Primary source | Checked |
|---|---|---|
seed-sql.sh analyzes after loading | source line 141, and last_analyze set in the run | ☑ |
| With stock autovacuum, a 73s load is corrected before the upgrade | last_autoanalyze 01:33:30, mid-load; reltuples accurate after | ☑ |
With autovacuum_enabled=false and no ANALYZE, the estimate stays stale | reltuples 100,002 against 1,100,003 true rows | ☑ |
| Keycloak then builds the index above the threshold | index present, EXECUTED, 0 warnings, 1,100,003 rows | ☑ |
| How long stock autovacuum takes to correct a load of this size | not isolated — it fired during the load, so the correction latency was never measured | ☐ |
| Whether a load large enough to outrun autovacuum exists | not tested — 1M rows did not | ☐ |
| Whether throttled (rather than disabled) autovacuum is enough | not tested — autovacuum_vacuum_cost_delay untouched | ☐ |