How a Disabled Autovacuum Flag Left Postgres Blind to 14 Million Rows for 10 Days

09:02 UTC. PagerDuty: pick-and-pack-api p99 > 10s. The dashboard that's usually a flat green line at 180ms is a cliff. Primary Postgres CPU is pinned at 100% and climbing. No deploy in the last two hours, no traffic spike in the graphs, nothing in the error budget that would explain it. Warehouse ops is already in the incident channel asking why the pick-and-pack screens have stopped refreshing.

the setup

Ten days earlier, the fulfillment team opened a third regional warehouse and added a column to route orders to it: orders.fulfillment_region, nullable, backfilled onto all 42 million existing rows in batches overnight to avoid holding a long lock. Before kicking it off, whoever ran the backfill turned autovacuum off on the table first, reasonably, since a multi-hour batched UPDATE and a concurrent autovacuum fighting over the same pages tends to make both slower:

ALTER TABLE orders SET (autovacuum_enabled = false);

-- batched UPDATE, 50k rows per transaction, ~4 hours total
-- (orchestrating script, not shown)

-- intended follow-up, never run:
-- ALTER TABLE orders RESET (autovacuum_enabled);

The backfill itself went fine. 14.2 million of the 42 million orders got a non-null fulfillment_region; the rest predate the multi-warehouse model and stay NULL on purpose. The re-enable step was a comment in the runbook, not a line in the script, and it fell through a shift handover. For ten days nothing cared: no query filtered on fulfillment_region at any real volume, so nobody felt the planner was flying on stats from before the backfill. Then the new pick-and-pack routing feature shipped at 08:55 UTC, and its very first query did exactly that.

the scramble

First theory: the 08:55 deploy. It's the obvious suspect, it's two minutes before the alert. The route handler's query is a straight lookup joined against warehouse_routes, same shape as the version that ran fine against a staging copy with 200k rows. Diffing the deploy against the previous commit turns up nothing in the query path that would explain a 200x latency jump.

Second theory: connection pool exhaustion. CPU pegged at 100% looks like a thundering herd. pg_stat_activity shows 22 of 40 pooled connections in use, comfortably under the cap, and the 22 are mostly idle or waiting on locks rather than burning CPU themselves. Not a pool problem.

Third theory: the backfill, still running somehow, is clashing with the new feature. The backfill's own job logs show it completed cleanly ten days ago and the orchestrator isn't in the process list. That theory dies in under a minute, but it's the one that finally points someone at ALTER TABLE orders SET (autovacuum_enabled = false); still sitting, unreverted, in the table's reloptions.

the hunt

pg_stat_activity has the actual slow query, still running. EXPLAIN (ANALYZE, BUFFERS) against the same query, same parameters, is where the mismatch shows up:

Nested Loop  (cost=0.43..912.47 rows=1 width=96)
            (actual time=0.071..31,842.219 rows=14,218,114 loops=1)
  ->  Index Scan using warehouse_routes_pkey on warehouse_routes wr
        (cost=0.14..8.16 rows=1 width=24) (actual time=0.019..0.024 rows=1 loops=1)
  ->  Seq Scan on orders o
        (cost=0.00..890.12 rows=3 width=72)
        (actual time=0.031..29,901.558 rows=14,218,114 loops=1)
        Filter: (fulfillment_region = 'west'::text)
        Rows Removed by Filter: 27,781,886
Planning Time: 0.412 ms
Execution Time: 31,844.901 ms

rows=3 estimated, rows=14,218,114 actual, on the inner side of a nested loop. The planner picked a nested loop because it expected the fulfillment_region = 'west' filter to return almost nothing, cheap enough to probe per outer row. It returned a third of the table. pg_stats confirms why:

null_frac       | 1.0
n_distinct      | 0
most_common_vals|

SELECT relname, last_autoanalyze, last_analyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname='orders';

relname: orders
last_autoanalyze:    (null)
last_analyze:        2026-09-17 22:04:11+00   -- the night before the backfill started
n_mod_since_analyze: 14,391,220

null_frac = 1.0: as far as the planner knew, every row in the column was NULL, which was true the last time anything analyzed the table, thirteen days earlier. 14.4 million row modifications had piled up since with nothing to trigger a re-analyze, because autovacuum_enabled = false blocks both autovacuum and autoanalyze on that table, not just the vacuum half.

the find

Disabling autovacuum on a table to protect a backfill is a reasonable, common move. The bug was never remembering to undo it. Every day after the backfill that the table kept autovacuum_enabled = false, its planner statistics were frozen at the exact moment before 14 million rows changed meaning. The gap was silent for ten days purely because nothing queried the table in a way that depended on fulfillment_region's real distribution. The pick-and-pack feature was the first query to ask the planner "how many orders are in the west region," and the planner answered with a number from before the west region had any orders in it at all.

the fix

Immediate: re-enable autovacuum and force a manual analyze, which is cheap and doesn't need a table lock:

ALTER TABLE orders RESET (autovacuum_enabled);
ANALYZE orders;

ANALYZE took 38 seconds on the 42-million-row table. Re-running the same EXPLAIN immediately after showed the plan flip back to an index scan on fulfillment_region with a row estimate within 4% of actual, and p99 back to 190ms by 09:29 UTC.

The backfill runner now ends with ANALYZE on any table it touches, as a script step rather than a runbook comment, and the step that disables autovacuum writes its own re-enable into a deferred job rather than relying on the same person remembering at the end of a four-hour overnight run:

psql -c "ALTER TABLE ${TABLE} SET (autovacuum_enabled = false);"
trap 'psql -c "ALTER TABLE ${TABLE} RESET (autovacuum_enabled); ANALYZE ${TABLE};"' EXIT
run_batched_update "${TABLE}"

Using trap ... EXIT means the re-enable and analyze run even if the batched update fails partway through, not just on a clean exit. A weekly check was also added against pg_class.reloptions across all tables, flagging any with autovacuum_enabled=false still set with no active backfill job to justify it.

the aftermath

The backfill did exactly what it was asked to do. The table even stayed fast the entire time autovacuum was off. The cost just didn't show up until something finally asked the planner a question whose honest answer had changed ten days earlier.