VACUUM, ANALYZE and Autovacuum

What we're doing: Show a table where a stale row-count estimate (fixed by ANALYZE) causes a bad query plan, entirely independent of whether the table has any bloat.

-- a table that only ever grows via INSERT -- zero dead tuples, zero bloat
INSERT INTO events SELECT generate_series(1, 1000000);
-- planner still thinks the table has its OLD row count, from creation time
EXPLAIN SELECT * FROM events WHERE id < 500000;
-- may choose a sequential scan, expecting far fewer matching rows than reality

ANALYZE events;  -- refreshes the row count and value distribution
EXPLAIN SELECT * FROM events WHERE id < 500000;
-- now chooses correctly, informed by the accurate statistics

1–2

This table has no dead tuples at all — VACUUM would find nothing to do here.

4

The planner's stale statistics, not any bloat, are the actual problem.

7–8

ANALYZE alone — no VACUUM involved — is what fixes the plan.

-- before ANALYZE: plan based on stale row-count estimate
-- after ANALYZE: plan based on accurate current statistics

Why this works: This table demonstrates the two concerns are genuinely independent — an INSERT-only table accumulates zero dead tuples (nothing for VACUUM to reclaim) but can still have wildly stale planner statistics if ANALYZE has not run recently, since row count and value distribution are ANALYZE's concern, not VACUUM's.

What we're doing: Observe autovacuum actually kick in on a table after enough changes accumulate, via last_autovacuum in pg_stat_user_tables.

SELECT relname, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables WHERE relname = 'accounts';
-- last_autovacuum: (null) -- never run yet

-- generate enough dead tuples to cross the threshold
UPDATE accounts SET balance = balance + 1;  -- touches every row, repeat a few times

-- wait roughly one autovacuum_naptime interval...
SELECT relname, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables WHERE relname = 'accounts';
-- last_autovacuum: a real timestamp -- autovacuum found and processed the table on its own

3

Before enough changes accumulate, autovacuum has never touched this table — there was nothing to justify it yet.

5–6

Enough UPDATEs push n_dead_tup past this table's autovacuum threshold.

9

No human ran VACUUM manually — autovacuum found the table crossed its threshold and handled it automatically.

 relname  | n_dead_tup | last_autovacuum 
----------+------------+-----------------
 accounts |          0 | 

(after updates and a wait)

 relname  | n_dead_tup | last_autovacuum 
----------+------------+-----------------
 accounts |          0 | 2026-08-24 10:15:03

Why this works: n_dead_tup dropping back to a low number alongside a fresh last_autovacuum timestamp is direct, observable proof the background process did its job without any manual intervention — this is exactly the self-regulating behavior that makes "someone remembers to run VACUUM" an unnecessary operational burden for the common case.

What we're doing: Investigate a table with a persistently high dead-tuple ratio, discover the root cause was a long-running transaction blocking autovacuum, fix the root cause, and confirm routine vacuuming resumes keeping pace on its own.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup is very high, last_autovacuum is old -- something is blocking it

SELECT pid, state, now() - xact_start AS duration
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
 ORDER BY duration DESC LIMIT 5;
-- finds a session idle in transaction for 6 hours

SELECT pg_terminate_backend(41213);  -- the offending session

-- routine autovacuum now resumes catching up on its own -- no VACUUM FULL needed,
-- since the bloat had not yet reached the point where a full rewrite is the only option

1–3

This is the diagnostic starting point — a high dead-tuple ratio is the symptom, not yet the diagnosis.

8

The actual root cause: a long-idle transaction has been holding back the vacuum horizon for hours.

12

Fixing the root cause lets routine autovacuum resume doing its job — often avoiding the need for VACUUM FULL entirely.

pg_terminate_backend
-----------------------
 t

(autovacuum resumes progress on its own shortly after)

Why this works: Reaching straight for VACUUM FULL without diagnosing why routine vacuuming fell behind treats the symptom instead of the cause — in this case the actual fix was terminating a stuck session, after which ordinary autovacuum was fully capable of catching up without ever needing the disruptive full rewrite.

What we're doing: Simulate a table accumulating bloat under sustained UPDATE churn without vacuuming keeping pace, and observe the growing dead-tuple ratio directly.

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_live_tup = 10000, n_dead_tup = 0

-- simulate a long-running transaction blocking vacuum, then heavy UPDATE churn
BEGIN;  -- Session A: opens and stays open, holding back the vacuum horizon
SELECT 1;

-- Session B, meanwhile:
UPDATE accounts SET balance = balance + 1;  -- touches all 10000 rows, repeatedly

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup climbing steadily -- autovacuum cannot reclaim any of it
-- while Session A's transaction stays open

4–5

Session A's open, idle transaction is the root cause -- it holds back the point at which any of these dead tuples can be considered reclaimable.

8–10

Every UPDATE creates more dead tuples, and none of them can be reclaimed until Session A finally commits or rolls back.

 n_live_tup | n_dead_tup 
------------+------------
      10000 |          0

(after updates, Session A still open)

 n_live_tup | n_dead_tup 
------------+------------
      10000 |      30000

Why this works: This makes bloat's dependence on both write activity AND successful vacuuming directly visible — the dead tuples are being created by ordinary UPDATE traffic exactly as expected, but Session A's open transaction is specifically what prevents VACUUM from doing anything about them, which is exactly the compounding of costs the earlier Transaction Duration concept described.

What we're doing: Directly demonstrate one idle-in-transaction session, which never touches "orders", still preventing VACUUM from reclaiming dead tuples on "orders".

-- Session A: opens a transaction, queries an UNRELATED table, then goes idle
BEGIN;
SELECT 1 FROM some_other_table LIMIT 1;
-- (application bug: never commits or rolls back)

-- Session B, meanwhile, generates dead tuples on "orders":
DELETE FROM orders WHERE status = 'cancelled';  -- 5000 rows

VACUUM orders;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';
-- n_dead_tup is still high -- VACUUM could not reclaim them

-- diagnosis:
SELECT pid, age(backend_xmin), now() - xact_start FROM pg_stat_activity
 WHERE state = 'idle in transaction';
-- finds Session A, open for a long time, holding back the horizon

2–4

Session A never touches 'orders' at all -- its own query was against a completely different table.

8–10

VACUUM on 'orders' still cannot reclaim the dead tuples, because it has no way to know Session A won't query 'orders' next.

13–15

The diagnostic query is what actually finds the real cause — nothing about the "orders" table itself would reveal it.

DELETE 5000
VACUUM
 n_dead_tup 
------------
       5000

  pid  |    age    |    now - xact_start 
-------+-----------+-------------------------
 41200 |      1203 | 00:47:12

Why this works: This is precisely why long-running transactions are a database-wide operational risk rather than a concern local to whatever tables they happen to use — the fix for 'orders' specifically was never anything to do with the orders table itself, only with finding and resolving Session A's unrelated, forgotten transaction.

What we're doing: Show a query plan degrading after a large bulk load leaves statistics stale, then recovering once ANALYZE catches up.

-- table starts with 1,000 rows, evenly distributed
EXPLAIN SELECT * FROM orders WHERE status = 'pending';  -- reasonable plan, based on accurate stats

-- bulk load adds 990,000 more rows, ALL with status = 'shipped'
INSERT INTO orders (status) SELECT 'shipped' FROM generate_series(1, 990000);

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- planner still assumes the OLD, now-wildly-wrong distribution --
-- may pick a poor plan, unaware 'pending' is now a tiny fraction of a huge table

ANALYZE orders;
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- planner now has fresh statistics reflecting the true, current distribution

2

The original plan was reasonable — statistics matched reality at that point.

5–6

A massive, distribution-skewing bulk load, but statistics are not automatically refreshed by the INSERT itself.

10–11

Only after ANALYZE explicitly runs does the planner's picture of the data catch up to reality.

-- before ANALYZE: plan based on the pre-bulk-load distribution
-- after ANALYZE: plan reflects the true post-bulk-load distribution

Why this works: The bulk INSERT itself does nothing to update pg_statistic — statistics only change when ANALYZE (explicit or autovacuum-triggered) actually runs, so there is a real window, potentially a long one on a quiet table, where the planner is working from data that no longer describes the table at all.

What we're doing: Compute the actual vacuum trigger point for a 50-million-row table under the default settings, and show why that number is often too permissive for a genuinely hot table.

SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE relname = 'orders';
-- estimated_rows = 50,000,000

-- default formula: 50 + 0.1 * 50,000,000 = 5,000,050 dead tuples needed
-- for a table receiving heavy UPDATE traffic, that is a LOT of accumulated
-- bloat before autovacuum even considers touching it

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
-- new threshold: 50 + 0.01 * 50,000,000 = 500,050 -- triggers 10x sooner

2

A genuinely large table — this is where the default scale factor's trade-off becomes visible.

4–5

Five million dead tuples is a substantial amount of bloat to tolerate before autovacuum acts, for a table under heavy write load.

7–8

Lowering the scale factor for this specific table trades more frequent (but individually cheaper) vacuum runs for less accumulated bloat at any given time.

 estimated_rows 
----------------
       50000000

(default threshold: 5,000,050 dead tuples)
(tuned threshold: 500,050 dead tuples)

Why this works: The default scale factor is a reasonable, safe default across a huge variety of table sizes and workloads, but it is not automatically correct for every table — a large, heavily-updated table specifically is a common case where the default's proportional threshold is too permissive, and per-table tuning (not changing the global default) is the standard, targeted fix.