Why do high-churn tables accumulate dead tuples faster than autovacuum reacts?

PostgreSQL uses multiversion concurrency control (MVCC). An UPDATE does not overwrite a row in place. It writes a new row version and marks the old one as expired. A DELETE only marks the row as expired. These expired versions are dead tuples. They stay on disk until VACUUM confirms that no running transaction can still see them and marks their space as reusable. Until then, sequential scans still read them and indexes still point at them.

Autovacuum is the background process that runs VACUUM for you. It vacuums a table once the dead tuple count exceeds this threshold:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

reltuples is the planner's estimate of live rows. Because the threshold scales with table size, a large table must accumulate a large absolute number of dead tuples before vacuum starts.

Illustrative estimate: assume a 50M-row table with a scale factor of 0.2. Autovacuum waits until about 10M dead tuples have built up. That is 10M row versions of table bloat that queries scan past and indexes keep referencing.

The proportional default works for typical tables, where churn is modest relative to size. It fails in two cases:

  • Large tables. The absolute threshold grows very large before vacuum runs.
  • High-churn tables with few live rows. These tables are rewritten many times over between vacuum runs, so dead tuples can outnumber live rows by a wide margin.

You should tune per-table settings if you run update- or delete-heavy tables such as job queues, session stores or counter tables.

  UPDATE (new version)        DELETE (mark expired)
     │                            │
     ▼                            ▼
┌──────────────────────────────────────────────────────────┐
│ Table heap (MVCC)                                        │
│  [new live] [old dead] [live] [dead] [dead] [live] [dead]│
│ UPDATE: writes new version, old version expires          │
│ DELETE: marks row expired, no space freed                │
└───────────────┬──────────────────────────────────────────┘
                │ dead tuples: scanned past, still indexed
                ▼
┌──────────────────────────────────────────────────────────┐
│ Autovacuum check                                         │
│ dead tuples >                                            │
│   autovacuum_vacuum_threshold                            │
│   + autovacuum_vacuum_scale_factor × reltuples           │
└──────┬───────────────────────────────────────┬───────────┘
    no │                                   yes │
       ▼                                       ▼
┌──────────────────────┐          ┌──────────────────────────┐
│ Wait: dead tuples    │          │ VACUUM marks dead space  │
│ keep accumulating;   │          │ reusable once no running │
│ scans read them and  │          │ transaction can see it   │
│ indexes still point  │          └──────────────────────────┘
│ at them              │
└──────────────────────┘

Illustrative estimate: 50M rows × 0.2 scale factor ≈ 10M dead tuples
Queue/session/counter tables: few live rows, many rewrites → dead >> live

UPDATE and DELETE leave dead tuples in the heap under MVCC, and autovacuum only runs VACUUM once their count exceeds a threshold that grows with reltuples, so large or high-churn tables accumulate bloat first.

What blocks vacuum cleanup no matter how you tune it?

Before you change a single storage parameter, confirm that vacuum is allowed to remove anything at all. Vacuum can only reclaim a dead tuple once no transaction could still need to see it. That cutoff is the xmin horizon, and it is the oldest snapshot held anywhere in the cluster, not just in the database you are tuning. One stale snapshot pins dead tuples in every table, so autovacuum runs, does the I/O, and leaves the dead tuples in place.

There are three common causes:

  • Long-running or idle-in-transaction sessions. Query pg_stat_activity for rows with a non-null backend_xmin, ordered by age(backend_xmin). Also check xact_start and look for state = 'idle in transaction'. An application that opens a transaction and then waits on a network call is a typical offender.
  • Abandoned prepared transactions. Rows in pg_prepared_xacts with an old prepared timestamp hold their snapshot indefinitely, and they survive restarts.
  • Replication slots and standby feedback. In pg_replication_slots, check the xmin and catalog_xmin columns, especially on slots where active is false. With hot_standby_feedback enabled, a long query on a replica holds back the primary's horizon.

The common trap is that bloat grows, so someone lowers the scale factor, then raises the cost limit, then adds workers. Each change makes vacuum run more often but frees nothing. VACUUM VERBOSE reports dead tuples that are "not yet removable", which signals this condition. Rule out a held-back horizon first. It takes a few minutes and avoids days of tuning that cannot work.

-- 1. Sessions holding the oldest snapshots (cluster-wide, all databases).
--    Look for 'idle in transaction' and old xact_start values.
SELECT pid, datname, usename, state,
       now() - xact_start AS xact_duration,
       age(backend_xmin)  AS xmin_age,
       left(query, 60)    AS last_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- 2. Prepared transactions that were never committed or rolled back.
--    These survive restarts and hold the horizon until resolved.
SELECT gid, owner, database, prepared,
       age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

-- 3. Replication slots pinning xmin or catalog_xmin.
--    Inactive slots with an old xmin are the usual suspects.
SELECT slot_name, slot_type, active,
       age(xmin)         AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC;

-- 4. Standbys reporting a snapshot via hot_standby_feedback.
--    A non-null backend_xmin here is a replica query holding back the primary.
SELECT application_name, client_addr, state,
       age(backend_xmin) AS xmin_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Four read-only queries, run on the primary, that find what is holding back the xmin horizon: old session snapshots, abandoned prepared transactions, replication slots, and standby feedback.

How do you find the tables that need per-table autovacuum settings?

Once the horizon is clear, find the tables that churn. pg_stat_user_tables has the counters you need, but it does not keep history. n_tup_upd and n_tup_del accumulate from the last statistics reset, so a single snapshot shows lifetime volume, not current rate. Sample the view twice, an hour or a day apart, and rank tables by the change in n_tup_upd + n_tup_del. A small set of tables usually accounts for most of the write churn.

For each of those tables, compare n_dead_tup with n_live_tup and with the trigger point autovacuum uses. By default, a vacuum starts when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the table's row estimate. With the default values of 50 and 0.2, a 100-million-row table waits for about 20 million dead tuples. The ratio of dead to live tuples can look acceptable while the absolute count is causing serious bloat.

Next, look at last_autovacuum and the change in autovacuum_count between samples. Here is how to read the combinations:

  • High churn, frequent autovacuums, dead tuples staying low: the defaults are working. Leave the table alone.
  • High churn, rare autovacuums, a large dead-tuple count: the scale factor is too coarse for the table's size. This is the case for per-table settings.
  • Frequent autovacuums, dead tuples not falling: vacuum is starting but either can't finish or can't remove tuples. Check cost limits and the horizon before you lower any thresholds.

Small tables that churn heavily rarely need tuning. Their threshold is low enough that the defaults already trigger vacuum often.

-- Step 1: take a baseline snapshot (counters are cumulative since the last stats reset).
CREATE TABLE churn_snapshot AS
SELECT now() AS taken_at, relid, n_tup_upd, n_tup_del, autovacuum_count
FROM pg_stat_user_tables;

-- Step 2: an hour or a day later, rank tables by churn since the snapshot
-- and compare dead tuples with the global autovacuum trigger point.
-- Note: this uses the global settings and ignores any per-table overrides already set.
SELECT s.schemaname || '.' || s.relname                        AS table_name,
       (s.n_tup_upd + s.n_tup_del)
         - (b.n_tup_upd + b.n_tup_del)                          AS churn_since_snapshot,
       s.n_live_tup,
       s.n_dead_tup,
       round(s.n_dead_tup::numeric / nullif(s.n_live_tup, 0), 3) AS dead_ratio,
       -- threshold + scale_factor * reltuples; reltuples is -1 if never analyzed
       (current_setting('autovacuum_vacuum_threshold')::bigint
        + current_setting('autovacuum_vacuum_scale_factor')::float8
          * greatest(c.reltuples, 0))::bigint                   AS vacuum_trigger_at,
       s.autovacuum_count - b.autovacuum_count                  AS autovacuums_since_snapshot,
       s.last_autovacuum
FROM pg_stat_user_tables s
JOIN pg_class c        ON c.oid = s.relid
JOIN churn_snapshot b  ON b.relid = s.relid
ORDER BY churn_since_snapshot DESC
LIMIT 20;

-- Reading the output:
--   high churn, several autovacuums, low n_dead_tup      -> defaults are fine
--   high churn, few/no autovacuums, n_dead_tup large      -> candidate for per-table settings
--   many autovacuums, n_dead_tup not falling              -> check cost limits and xmin horizon first

Snapshot pg_stat_user_tables, then diff it later to rank tables by update/delete churn and compare each table's dead tuples and autovacuum activity against the default vacuum trigger point.

How do you set and manage per-table autovacuum storage parameters?

For large tables that churn heavily, the fix is to stop the trigger point from growing with the table. Autovacuum starts a vacuum when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. With ALTER TABLE ... SET (...), set autovacuum_vacuum_scale_factor near zero (for example 0.0 or 0.01) and autovacuum_vacuum_threshold to a fixed row count. The trigger then becomes a roughly constant number of dead tuples, not a percentage that keeps rising as the table grows.

Tune analysis the same way. autovacuum_analyze_scale_factor and autovacuum_analyze_threshold control when statistics are refreshed. A table that changes quickly can drift far from its last ANALYZE, and the planner then misestimates row counts on recently changed ranges.

To confirm the settings, query reloptions in pg_class for the table. Overrides appear there as key=value strings. Anything not listed falls back to the global configuration. To return to the defaults, run ALTER TABLE ... RESET (...) with the parameter names.

Reloptions are hard to see. They don't appear in postgresql.conf, and engineers reviewing server settings won't find them. A setting applied by hand in production and never applied in staging leaves the two environments behaving differently with no visible reason. Put every SET and RESET in your schema migrations. That way the change is reviewed, versioned, and applied the same way everywhere.

A fixed threshold also goes stale. A value that suited ten million rows may trigger far too often at a billion, or too rarely if the table shrinks. Revisit it when the table's size changes substantially.

-- Migration: tune autovacuum for the high-churn events table.
-- Trigger on a roughly fixed count of dead tuples instead of a % of the table.
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold = 50000,
  -- Keep planner statistics fresh on the same fixed-count basis.
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold = 25000
);

-- Verify: overrides show up as key=value strings in reloptions.
-- A NULL here means the table uses the global settings.
SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.events'::regclass;

-- Down migration: drop the overrides and fall back to global defaults.
ALTER TABLE events RESET (
  autovacuum_vacuum_scale_factor,
  autovacuum_vacuum_threshold,
  autovacuum_analyze_scale_factor,
  autovacuum_analyze_threshold
);

A migration that pins vacuum and analyze triggers for a high-churn table to fixed row counts, checks the overrides in pg_class.reloptions, and includes a RESET down step to restore the global defaults.

How do cost limits and shared worker budgets affect whether vacuum finishes?

Autovacuum doesn't run flat out. Each worker accumulates "cost" points for every page it reads or dirties. When it reaches autovacuum_vacuum_cost_limit, it sleeps for autovacuum_vacuum_cost_delay. If the limit is unset (-1), it falls back to vacuum_cost_limit. You can set both parameters globally or per table. The throttle protects foreground queries from I/O and CPU spikes. It can also mean a vacuum that triggers every few minutes never finishes before the next batch of dead tuples arrives.

Throughput is roughly: pages/s ≈ (cost_limit ÷ cost_delay_seconds) ÷ avg_cost_per_page ÷ active_workers.

Illustrative estimate (assumed numbers, not a benchmark): Assume a limit of 200, a delay of 2 ms, an average cost of 10 per page and 3 active workers. That gives (200 ÷ 0.002) ÷ 10 ÷ 3 ≈ 3,333 pages/s per worker, or about 26 MB/s at 8 KB pages. A 400 GB heap is about 52 million pages, so one pass takes about 4.4 hours.

The global limit is a shared budget, divided among active workers. Raising autovacuum_max_workers alone gives each worker a smaller share. Workers on tables with their own cost settings are excluded from that balancing and run at their own rate. That worker can hold a slot for hours and saturate shared disks, so neighboring tables still compete with it for I/O.

Change Effect on hot table Effect on others / foreground
Raise global limit Faster, shared More total I/O
More workers, same limit Unchanged or slower Slower per worker
Per-table higher limit Faster Extra I/O outside the budget

On a busy primary, raise limits step by step and watch read latency and checkpoint pressure after each change.

When should you avoid aggressive autovacuum tuning, and what are the alternatives?

Tuning changes how often vacuum runs. It cannot fix problems vacuum is unable to touch, and in three situations it is the wrong lever:

  • Bloat already exists. Plain vacuum makes dead space reusable but usually does not shrink the file. Running it more often keeps a bloated table at its current size.
  • The xmin horizon is held back. Long transactions, idle-in-transaction sessions, stale replication slots or forgotten prepared transactions stop dead tuples from being removed. More aggressive settings then spend I/O on scans that remove nothing. Fix the holder first.
  • The primary has no I/O headroom. Lower thresholds and higher cost limits show up as read latency for the application.
Option What it does Cost
Lower fillfactor + HOT updates Leaves free space on each page so updates stay on the same page and skip index writes Works only when the updated columns are not indexed. The table gets larger and sequential scans slow down
Plain VACUUM Marks dead space as reusable Rarely returns space to the OS
VACUUM FULL Rewrites the table and its indexes compactly Holds an exclusive lock for the whole run
Online rebuild Copies data to a new table and then swaps it in Needs extra disk and adds operational complexity

Preventing bloat costs less than removing it. Vacuuming a few fresh dead tuples is a small incremental pass. Reclaiming accumulated bloat means rewriting the entire table and its indexes.

Do not set autovacuum_enabled = false on a high-churn table. Doing so also stops autoanalyze, and dead tuples then grow without bound. Anti-wraparound vacuum still runs regardless of the setting, so you will eventually get a forced, long-running vacuum on a table that is now much larger.

How do you verify that autovacuum tuning worked?

Tuning is only finished when the numbers show it. Three signals tell you whether autovacuum now keeps pace with a table's churn.

Dead tuple trend. Sample n_dead_tup and n_live_tup from pg_stat_user_tables on a schedule and store the results. A single reading means little, because the count rises between runs and drops after each one. What matters is the shape over time: a sawtooth that peaks near your configured threshold and returns to near zero is healthy. A baseline that keeps rising means vacuum is falling behind or something is blocking cleanup.

Frequency. autovacuum_count and last_autovacuum show how often autovacuum actually runs. Compare the change in autovacuum_count between snapshots with what your threshold and the table's write rate predict. These counters reset when statistics are reset, so diff your snapshots instead of reading absolute totals.

Duration. The statistics views do not record how long a run took, so use the logs for this. Set log_autovacuum_min_duration to a value in milliseconds, either server-wide or as a per-table storage parameter. Any run that takes longer than that value is logged with its elapsed time, the tuples removed, and the dead tuples that could not yet be removed. Setting it to 0 logs every run. Runs that get longer over time, or a high count of dead tuples that could not be removed, point back to the blockers covered earlier.

Review routine. Recheck your tuned tables on a fixed schedule, such as monthly, and after any schema change or traffic shift. Drop overrides that are no longer needed, and add overrides for tables that have become high-churn. This catches drift before it turns into an incident.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres autovacuum tuning, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.