Autovacuum and Plan Stability: Why Plans Drift Between Vacuums #

A query that has run in 12 ms for six months suddenly takes four seconds, nothing was deployed, and the plan looks different. The usual culprit is not the query and not the data volume — it is the maintenance layer underneath. Autovacuum decides when statistics are refreshed, when dead tuples stop inflating relation sizes, and when heap pages become eligible for index-only scans. Every one of those feeds directly into planner decisions.

This page connects vacuum behavior to plan shape, and shows the per-table settings that keep plans steady. It is the maintenance-facing branch of the runtime environment story, alongside pooling and session state.

The Three Ways Vacuum Changes a Plan #

Vacuum is usually explained as space reclamation, which undersells its planning role. Three distinct outputs of the vacuum and analyze machinery reach the optimizer:

What vacuum and analyze hand to the planner On the left, the autovacuum daemon runs ANALYZE and VACUUM. Three arrows carry column statistics, relation size metadata, and visibility map bits into the planner's cost model on the right, which then chooses selectivity, scan cost, and index-only eligibility. autovacuum launcher + workers ANALYZE · VACUUM pg_stats MCVs · histogram · n_distinct pg_class relpages · reltuples visibility map all-visible page bits cost model selectivity scan cost index-only eligibility every plan flip that "came out of nowhere" traces back to one of these three arrows

That is why plan instability is so often a maintenance problem: nothing about the query changed, but one of the three inputs did.

When Autovacuum Decides to Run #

Autovacuum is threshold-driven, and the formula is worth memorising because it explains why big tables get neglected:

vacuum threshold  = autovacuum_vacuum_threshold  + autovacuum_vacuum_scale_factor  × reltuples
analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples

defaults: vacuum_threshold = 50,  vacuum_scale_factor  = 0.2
          analyze_threshold = 50, analyze_scale_factor = 0.1

For a 10,000-row table, analyze triggers after roughly 1,050 changes — frequent enough that statistics stay fresh. For a 500-million-row table it waits for 50 million modified rows. In between those two runs, the planner is working from a sample that may describe a different table than the one on disk.

Query the current state directly rather than guessing:

SELECT relname,
       n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio
  FROM pg_stat_user_tables
 WHERE n_dead_tup > 10000
 ORDER BY n_dead_tup DESC
 LIMIT 20;

A last_autoanalyze timestamp measured in days, on a table receiving continuous writes, is the signature of a scale factor that is too coarse. The fix is per table, not global:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor  = 0.02,   -- 2% instead of 20%
  autovacuum_analyze_scale_factor = 0.01,   -- 1% instead of 10%
  autovacuum_vacuum_cost_limit    = 2000    -- let the worker actually keep up
);

Reading Stale-Statistics Damage in a Plan #

Stale statistics have a distinctive plan fingerprint: the estimate is confident and wrong in the same direction everywhere, and the error compounds upward through the tree.

-- Statistics captured before a bulk load added 4M rows for tenant 77
Nested Loop  (cost=0.86..612.44 rows=12 width=64)
             (actual time=0.09..8123.66 rows=3941022 loops=1)
  ->  Index Scan using events_tenant_idx on events e  (cost=0.43..48.9 rows=6 width=32)
        Index Cond: (tenant_id = 77)
        (actual time=0.04..940.2 rows=3941022 loops=1)     -- 6 estimated, 3.9M actual
  ->  Index Scan using sessions_pkey on sessions s  (loops=3941022)  -- 3.9M lookups
Execution Time: 8241.9 ms

The planner chose a nested loop because it expected six rows from the outer side. It got 3.9 million, so the inner index scan ran 3.9 million times. The join method was reasonable given the estimate; the estimate was six months old for that tenant value.

After a single ANALYZE events:

Hash Join  (actual time=612.1..2104.8 rows=3941022 loops=1)
  ->  Seq Scan on events e  (actual rows=3941022 loops=1)
  ->  Hash  (actual rows=180422 loops=1)  Batches: 1
Execution Time: 2210.4 ms

No index was added, no query was rewritten. The estimate became honest and the planner picked a hash join — the shape it would have picked all along. Recognising this pattern quickly is the core skill in spotting row estimate explosions.

Estimate accuracy decays between ANALYZE runs A sawtooth line showing estimation error rising steadily as writes accumulate and dropping to near zero at each ANALYZE. A bulk load in the middle causes a much steeper rise, crossing the threshold where the planner flips to a bad plan before the next analyze corrects it. time → estimate error plan-flip threshold ANALYZE ANALYZE ANALYZE ANALYZE ANALYZE bulk load bad plan window

Visibility-Map Lag and Index-Only Scans #

An Index Only Scan is only “only” when the heap can be skipped, and that requires the visibility map to mark the page all-visible. Vacuum sets those bits; between vacuums, they decay with every update.

-- Right after VACUUM
Index Only Scan using orders_customer_total_idx on orders  (actual time=0.03..2.11 rows=8412 loops=1)
  Heap Fetches: 0                            -- fully covered, no heap access

-- Same query after 40 minutes of update traffic
Index Only Scan using orders_customer_total_idx on orders  (actual time=0.04..38.62 rows=8412 loops=1)
  Heap Fetches: 7891                         -- almost every row now needs the heap

The plan is identical. The performance is not, because 7,891 random heap lookups appeared. This is not an indexing bug and no rewrite helps — the answer is more frequent vacuuming of that table, which is exactly what a lowered autovacuum_vacuum_scale_factor delivers.

Step-by-Step Stability Workflow #

  1. Confirm the plan actually changed rather than the data volume. Capture the current plan and compare node shapes against a known-good baseline.

  2. Check when statistics were last refreshed for every table in the plan:

    SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
  FROM pg_stat_user_tables WHERE relname IN ('events','sessions');

    n_mod_since_analyze is the count the threshold formula compares against.

  3. Run ANALYZE on the suspect table and re-plan. If the plan snaps back, statistics were the cause and the remaining work is scheduling, not query tuning.

  4. Tighten per-table scale factors on the tables that drift, rather than lowering the global value and flooding every small table with workers.

  5. Add ANALYZE to bulk-load jobs as the final statement, inside the same job. COPY into a new table leaves it with no statistics at all until autovacuum notices.

  6. Increase the statistics target where the distribution is skewed:

    ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE events;

    A larger target keeps more most-common values, which is what protects a skewed column between analyze runs.

  7. Verify vacuum is keeping up, not just running: compare n_dead_tup before and after a cycle, and check pg_stat_progress_vacuum during one.

Common Pitfalls #

Assuming autovacuum handles large tables. The default 20% scale factor means enormous absolute thresholds. Diagnostic signal: last_autoanalyze days old with millions in n_mod_since_analyze. Fix: per-table scale factors in the 1–2% range.

Bulk loading without a following ANALYZE. The first queries after a load are planned against pre-load statistics, or none at all. Diagnostic signal: catastrophic estimates immediately after a load window. Fix: ANALYZE as the load job’s last step.

Blaming the index when Heap Fetches is high. The index is fine; the visibility map is stale. Diagnostic signal: an Index Only Scan whose Heap Fetches approaches its row count. Fix: vacuum that table more often.

Long-running transactions blocking cleanup. Vacuum cannot remove tuples newer than the oldest open snapshot, so one forgotten BEGIN freezes cleanup database-wide. Diagnostic signal: n_dead_tup rising despite recent last_autovacuum. Fix: find it in pg_stat_activity by xact_start and end it; set idle_in_transaction_session_timeout.

Throttled autovacuum workers. Default cost limits make a worker crawl on fast storage, so it never finishes big tables. Diagnostic signal: pg_stat_progress_vacuum showing slow progress over hours. Fix: raise autovacuum_vacuum_cost_limit and the worker count.

Forgetting partitioned parents. Autovacuum does not analyze a partitioned parent from child activity, so parent-level statistics can be absent entirely. Diagnostic signal: bad estimates on cross-partition queries — see partition pruning analysis. Fix: schedule ANALYZE parent_table explicitly.

Treating extended statistics as a substitute for fresh ones. CREATE STATISTICS teaches the planner about correlations between columns, which no amount of vacuuming can discover on its own — but the extended statistics are themselves rebuilt by ANALYZE, so a stale table has stale correlations too. Diagnostic signal: multi-column estimates that were accurate at creation time and drifted since. Fix: treat extended statistics as another consumer of the analyze cadence, not as a way to avoid it.

Assuming default statistics targets suit skewed columns. default_statistics_target of 100 keeps at most 100 most-common values and 100 histogram buckets per column. A tenant identifier with thousands of distinct values, a handful of which dominate, cannot be described well by that sample, so estimates for the mid-frequency values fall back on assumptions. Diagnostic signal: accurate estimates for the largest tenants and wild ones for the rest. Fix: raise the per-column target on the skewed columns and re-analyze, accepting the slightly longer analyze pass.

Letting a replication slot pin the oldest snapshot. An unused or disconnected logical replication slot holds back the transaction horizon exactly as an open transaction does, so vacuum cannot remove any tuple newer than it. The effect is database-wide and invisible in pg_stat_activity. Diagnostic signal: dead tuples rising everywhere with no long-running queries in sight. Fix: check pg_replication_slots for slots with growing lag and drop the ones nothing consumes.

Vacuuming a partitioned parent and expecting the children to benefit. The parent holds no rows of its own; each partition is vacuumed and analyzed independently, and the parent needs its own ANALYZE for cross-partition estimates. Diagnostic signal: healthy per-partition statistics beside poor estimates on queries spanning partitions. Fix: schedule both — the partitions through autovacuum, the parent explicitly.

Confusing a freeze pass with routine cleanup. Anti-wraparound vacuums are not optional and cannot be cancelled the way an ordinary autovacuum can; when one starts on a large neglected table, it runs until it finishes. Diagnostic signal: an autovacuum worker on a table that resists cancellation, with to prevent wraparound in the process title. Fix: keep ordinary vacuum frequent enough that freeze passes are incremental rather than emergencies.

Three symptoms, three maintenance causes Estimates far below actuals point to stale statistics. Rising buffer counts at constant row counts point to bloat and relpages. Climbing heap fetches point to visibility map lag. symptom in the plan maintenance cause estimate 6 rows, actual 3.9M stale column statistics → ANALYZE same rows, twice the buffers bloat inflating relpages → REINDEX / VACUUM Heap Fetches climbing on an index-only scan visibility map lag → vacuum more often

Running maintenance on a schedule instead of on a signal. A nightly VACUUM ANALYZE sounds prudent and is usually both too late for the tables that drift fastest and unnecessary for the ones that barely change. Autovacuum’s threshold model already responds to actual write volume; the work is tuning its per-table parameters, not replacing it with a cron job. Diagnostic signal: a scheduled maintenance window that grows every month while plan instability continues between runs. Fix: tune the scale factors and cost limits so the daemon keeps up, and reserve manual runs for bulk loads and one-off interventions.

Assuming maintenance and performance are separate concerns. Vacuum settings are usually filed under operations and query plans under development, which is why a plan regression caused by a scale factor takes so long to diagnose — the two people who could connect the symptom to the cause are looking at different dashboards. Diagnostic signal: a plan change nobody can attribute to a deployment or a data change. Fix: put last_autoanalyze and dead-tuple ratios next to query latency in the same view, so the correlation is visible without a hypothesis.

Trusting a table’s settings to survive a restore. Per-table storage parameters live in the catalog and travel with a dump, but a table recreated by a migration or a manual rebuild starts from the global defaults again. Diagnostic signal: drift returning on a table you tuned months ago. Fix: keep the ALTER TABLE … SET statements in migrations alongside the schema, so the settings are recreated with the table rather than applied once by hand.

Frequently Asked Questions #

Why does the same query get a different plan hours later? Because the inputs to the cost model changed. Autovacuum’s ANALYZE rewrites the histograms, most-common values, and n_distinct estimates, and both vacuum and analyze refresh relpages and reltuples. When one of those crosses a cost threshold, the planner picks a different shape for identical SQL.

Does a bulk load need a manual ANALYZE? Yes, in practice always. Autovacuum will get there eventually, but the queries running in the meantime are planned against statistics that describe the pre-load table — or, for a newly created table, no statistics at all. Make ANALYZE the last step of the load.

How does vacuum affect index-only scans? An index-only scan skips the heap only for pages the visibility map marks all-visible, and only vacuum sets those bits. Between vacuums the bits decay with update traffic, so Heap Fetches climbs and the scan degrades toward an ordinary index scan without the plan changing at all.

Should I raise autovacuum thresholds on a large table? Lower them. The default scale factors are tuned for small tables; on a large one they allow tens of millions of modified rows before anything runs. Set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor per table, and raise the cost limit so the worker can finish.