PostgreSQL Autovacuum Tuning for Busy Tables: Stop Table Bloat

How MVCC creates dead tuples and table bloat

PostgreSQL implements MVCC by keeping multiple physical versions of a row instead of overwriting data in place. An UPDATE does not modify the existing tuple. It writes a new version and marks the old one as expired by recording the updating transaction's ID in its xmax field. A DELETE only sets xmax and leaves the tuple where it is. This lets concurrent readers keep seeing the version that matches their snapshot without taking locks, but every update and delete leaves a dead tuple behind.

Dead tuples still occupy heap pages, and index entries still point to them. Until something removes them, sequential scans read past them and index scans follow pointers to rows that turn out to be invisible. That removal is VACUUM's job. Autovacuum is the background process that runs it automatically, along with ANALYZE, so you do not have to schedule it.

Vacuum does not shrink the table file. It removes dead tuples and their index entries, then records the freed space in the free space map so future inserts and updates can reuse it. The file only gets smaller when empty pages sit at the very end. If dead tuples accumulate faster than vacuum reclaims them, the table and its indexes grow to hold both live and dead data. That excess is bloat, and it stays in the files after the backlog is cleared.

Vacuum also cannot remove a dead tuple that some transaction might still need to see. The xmin horizon marks this limit. It is set by the oldest snapshot still in use across the cluster, and any tuple that expired after that point must be kept, no matter how often vacuum runs.

Copy  UPDATE / DELETE: old version gets xmax, stays on page
       |
       v
+---------------------------------+     +-----------------------+
| Heap page                       |     | xmin horizon          |
|  v1  xmax=200  DEAD   <- index  |     | = oldest snapshot     |
|  v2  live             <- index  |     | in use; tuples that   |
|  r9  xmax=210  DEAD   <- index  |     | expired after it stay |
+---------------------------------+     +-----------+-----------+
       |                                            |
       v                                            |
+---------------------------------+                 |
| Autovacuum runs VACUUM          |<----------------+
| removes dead tuples and their   |
| index entries (up to horizon)   |
+---------------------------------+
       |
       v
+---------------------------------+     +-----------------------+
| Free space map records holes;   |---->| reused by later       |
| space stays inside the file     |     | INSERT / UPDATE       |
+---------------------------------+     +-----------------------+
       |
       | if dead tuples pile up faster than vacuum clears them
       v
+---------------------------------+
| Bloat: table + indexes grow;    |
| file shrinks only if empty      |
| pages sit at the very end       |
+---------------------------------+

Under MVCC, UPDATE and DELETE leave dead tuples that VACUUM can turn into reusable free space only up to the xmin horizon, and any backlog it cannot clear in time becomes bloat that stays in the table and index files.

How autovacuum decides when to run and why defaults fail large tables

Autovacuum's launcher wakes up periodically and checks each table's dead-tuple count, as tracked by the statistics system, against this trigger:

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

The defaults are a base threshold of 50 rows and a scale factor of 0.2. That means a table is vacuumed once roughly 20% of its estimated rows are dead. On a small table this is fine. On a large table the trigger grows with the table, so the amount of garbage tolerated before cleanup grows too.

Illustrative estimate

Assume a 50-million-row table that receives 500 updates per second, with each update leaving one dead tuple.

Under the default, autovacuum lets ten million dead tuples accumulate. Each vacuum pass then has to scan and clean a large backlog.

Throttling limits speed

Starting a vacuum on time is only part of the problem; it also has to run fast enough. Vacuum accrues cost points for page hits, misses and dirtied pages. When the total reaches autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay, then continues.

That cost limit is a single budget divided across all active workers. If you raise autovacuum_max_workers without raising the limit, more workers split the same I/O allowance. Each table then gets cleaned more slowly, and a busy table can fall further behind.

Measuring dead-tuple growth before you tune

Before changing any settings, find out which tables need attention. On most systems, a handful of tables produce nearly all the churn, and global changes tuned for them waste I/O everywhere else.

The starting point is pg_stat_user_tables. For each table, compare n_dead_tup against n_live_tup, and check last_autovacuum and autovacuum_count. A table with millions of dead tuples whose last_autovacuum is hours old is waiting for a threshold it reaches too slowly. A table whose autovacuum_count climbs quickly while n_dead_tup stays high has a different problem: autovacuum runs often but cannot keep up, or something is preventing it from removing tuples (covered in the next section).

A single reading tells you little. These counters are cumulative since the last statistics reset, and n_dead_tup is an estimate. Sample the view on a schedule, for example every few minutes into a metrics table or your monitoring system, and look at rates: dead tuples created per minute versus how often vacuums complete. That rate is what you are tuning against.

Bloat, meaning space already lost, is harder to measure. Common queries estimate it from pg_class page counts and pg_stats column widths. Treat their output as a lead. Wide rows, TOAST, and stale statistics can make these estimates significantly wrong in either direction. Before acting on a number, verify it with the pgstattuple extension. It reads the table and reports actual dead and free space, so run it off-peak on large tables.

Copy-- 1. Snapshot table for periodic samples of the cumulative counters
CREATE TABLE IF NOT EXISTS vacuum_stats_snapshot (
  captured_at      timestamptz NOT NULL DEFAULT now(),
  relid            oid, relname text,
  n_live_tup bigint, n_dead_tup bigint,
  n_tup_upd bigint, n_tup_del bigint,
  autovacuum_count bigint, last_autovacuum timestamptz
);

-- 2. Run on a schedule (e.g. every 5 minutes from cron or your scheduler)
INSERT INTO vacuum_stats_snapshot
  (relid, relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_del,
   autovacuum_count, last_autovacuum)
SELECT relid, schemaname || '.' || relname, n_live_tup, n_dead_tup,
       n_tup_upd, n_tup_del, autovacuum_count, last_autovacuum
FROM pg_stat_user_tables;

-- 3. Rates between consecutive samples. n_dead_tup falls after each vacuum,
--    so use updates+deletes as the dead-tuple creation rate and compare it
--    with how many autovacuums completed in the same interval.
SELECT relname, captured_at, n_dead_tup,
       round((n_tup_upd + n_tup_del - lag(n_tup_upd + n_tup_del) OVER w)
         / NULLIF(extract(epoch FROM captured_at - lag(captured_at) OVER w) / 60, 0))
         AS dead_tup_per_min,
       autovacuum_count - lag(autovacuum_count) OVER w AS vacuums_in_interval
FROM vacuum_stats_snapshot
WINDOW w AS (PARTITION BY relid ORDER BY captured_at)
ORDER BY dead_tup_per_min DESC NULLS LAST
LIMIT 20;

-- 4. Verify a suspected bloat estimate before acting. pgstattuple scans
--    the whole table, so run it off-peak on large tables.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, dead_tuple_percent, free_percent
FROM pgstattuple('public.orders');

Sampling pg_stat_user_tables into a snapshot table makes it possible to compute per-table dead-tuple creation rates against autovacuum frequency, and pgstattuple then checks actual dead and free space on a candidate table before you tune it.

Implementing per-table autovacuum settings for hot tables

Once the measurements identify the tables that actually accumulate dead tuples, apply overrides to those tables only. A storage parameter set with ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000) makes autovacuum trigger after roughly 1% of rows change, rather than the default 20%. On a 200-million-row table, that is the difference between waiting for about 2 million dead tuples and waiting for about 40 million (illustrative estimate). If the worker then can't keep up, raise autovacuum_vacuum_cost_limit or lower autovacuum_vacuum_cost_delay on the same table. This lets vacuum do more I/O on that table without making every vacuum on the instance more aggressive.

Targeted overrides are safer than lowering the global scale factor. A global change also makes thousands of small or rarely-updated tables vacuum more often. That consumes worker slots and I/O budget the hot tables need, and none of those tables benefit from it.

To see current overrides, query pg_class.reloptions for the table, or use \d+ in psql. Remove an override with ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor), and the table falls back to the instance default.

Overrides are easy to forget, so treat them as schema:

• Track them in migrations. Apply each override through a migration, not an ad-hoc session.
• Record the reasoning. Note the observed write rate in the migration or a COMMENT ON TABLE.
• Watch for drift. Track n_dead_tup, last_autovacuum and autovacuum_count from pg_stat_user_tables per overridden table. If the dead-tuple count climbs, or vacuums run constantly, the workload has changed and the setting should be revisited.

Copy-- Migration: tune autovacuum for the hot orders table only.
-- Vacuum after ~1% of rows change (+1000) instead of the 20% default.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000,
  -- Let vacuum do more work on this table without changing the instance default.
  autovacuum_vacuum_cost_limit   = 2000,
  autovacuum_vacuum_cost_delay   = 1   -- milliseconds
);

-- Record why the override exists so it can be reviewed later.
COMMENT ON TABLE orders IS
  'autovacuum override: scale_factor 0.01, cost_limit 2000. Reason: <observed write rate and date>. Review if workload changes.';

-- Inspect the current overrides on one table.
SELECT option_name, option_value
FROM pg_class c,
     pg_options_to_table(c.reloptions)
WHERE c.oid = 'orders'::regclass;

-- Drift check: dead tuples and vacuum activity for every table with an autovacuum override.
SELECT s.relname,
       s.n_live_tup,
       s.n_dead_tup,
       s.last_autovacuum,
       s.autovacuum_count,
       c.reloptions
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE EXISTS (
  SELECT 1 FROM unnest(c.reloptions) AS opt WHERE opt LIKE 'autovacuum_%'
)
ORDER BY s.n_dead_tup DESC;

-- Rollback: return selected parameters to the instance defaults.
-- ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold,
--                           autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay);

A migration that sets per-table autovacuum thresholds and cost limits on a hot table, records the reasoning, reads back the overrides, monitors every overridden table for drift, and shows how to reset the settings.

When tuning won't help: xmin horizon blockers and wraparound vacuums

Dead tuples vacuum cannot remove

Some bloat has nothing to do with vacuum settings. Vacuum can only remove a dead tuple once no running snapshot can still see it. The oldest such snapshot sets the xmin horizon, and anything that holds the horizon back stops cleanup in every table at once. Vacuum still runs and scans pages, but it frees nothing. Making autovacuum more aggressive in this state only burns I/O.

The usual causes are:

• Long-running transactions. Check xact_start and backend_xmin in pg_stat_activity.
• Idle-in-transaction sessions. These are rows where state = 'idle in transaction', often left by a connection pool that never committed.
• Replication slots. Look for slots in pg_replication_slots with active = false or an old xmin / catalog_xmin. Standbys using hot_standby_feedback can hold the horizon back the same way.
• Forgotten prepared transactions. These appear in pg_prepared_xacts and survive restarts.

Pass the relevant xid column to age() to rank the culprits. Fix the blocker by ending the session, dropping the slot, or committing or rolling back the prepared transaction. Then let vacuum catch up.

Wraparound vacuums

Transaction IDs are 32-bit, so old rows must be frozen before the counter wraps around. When age(relfrozenxid) for a table exceeds autovacuum_freeze_max_age (200 million by default), PostgreSQL forces an anti-wraparound vacuum. It does this even if autovacuum is disabled for that table. Unlike a normal autovacuum, this one does not cancel itself when it blocks a conflicting lock. It also must visit every page not already marked all-frozen.

On a large, rarely vacuumed table, that can mean hours of heavy I/O, and it can start during peak traffic. The same xmin blockers described above also prevent freezing, which lets XID age keep climbing.

Copy-- 1. What is holding back the xmin horizon? Largest age first.
--    Walsenders for standbys with hot_standby_feedback show up as backends.
SELECT 'backend' AS source, pid::text AS id, state AS detail,
       age(backend_xmin) AS xmin_age, now() - xact_start AS running_for
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name::text,
       CASE WHEN active THEN 'active' ELSE 'inactive' END,
       greatest(age(xmin), age(catalog_xmin)), NULL  -- greatest() skips NULLs
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, owner::text, age(transaction), now() - prepared
FROM pg_prepared_xacts
ORDER BY xmin_age DESC;

-- Clearing a blocker (after confirming it is safe to do so):
--   SELECT pg_terminate_backend(12345);           -- stuck session
--   SELECT pg_drop_replication_slot('old_slot');  -- abandoned slot
--   ROLLBACK PREPARED 'gid_from_query_above';     -- forgotten 2PC txn

-- 2. Which tables are closest to a forced anti-wraparound vacuum?
--    Compares against the server-wide setting; per-table
--    autovacuum_freeze_max_age storage parameters can lower the trigger.
SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       round(100.0 * age(c.relfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::int, 1)
         AS pct_of_trigger,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

Two diagnostic queries: one finds the sessions, replication slots and prepared transactions holding back the xmin horizon, and the other ranks the tables closest to the autovacuum_freeze_max_age threshold that forces an anti-wraparound vacuum.

Recovering existing bloat: VACUUM, VACUUM FULL and the alternatives

Plain VACUUM marks dead tuples as reusable space inside the table. It does not return that space to the operating system. The exception is a run of completely empty pages at the end of the file, which it can truncate after briefly taking a stronger lock. Even so, freed space is still useful. New rows and updates fill it, so a table that has stopped growing is often in a better state than its size suggests. If tuning now keeps dead tuples in check, that steady state may be enough.

Index bloat often outlasts table bloat. B-tree pages that empty out are not always reused well. Rebuilding an index concurrently is therefore usually the lowest-risk win.

A full rewrite is justified in three cases:

• The table holds mostly free space that will never refill, for example after a one-off mass delete.
• Sequential scans read far more pages than the live data needs.
• Disk pressure is real.

On a busy table, VACUUM FULL amounts to a planned outage. Schedule a maintenance window, or use an online rebuild extension that copies the data while triggers capture concurrent changes.

Trade-offs and impact on cost and engineering time

Every autovacuum setting trades cleanup speed against interference with foreground work. A lower scale factor or higher cost limit means vacuum reads and dirties more pages per second. That I/O and CPU competes with your queries for disk bandwidth and buffer cache, and it generates more WAL for replicas to absorb.

The payoff shows up as storage that stays flat, fewer emergency rewrites and less time spent on incidents.

Illustrative estimate (assumed numbers, not a benchmark). Peak dead space before a run ≈ live rows × scale factor × average row size. Assume 1 billion rows at 200 bytes:

• At 0.2, that is 1e9 × 0.2 × 200 B ≈ 40 GB of dead tuples before vacuum triggers.
• At 0.01, it is about 2 GB.

The ongoing engineering effort is mostly measurement, not configuration. Track dead-tuple ratios, vacuum duration and xmin age per table, and alert on trends rather than on absolute values. Review overrides whenever write patterns change, such as after a schema change or a new batch job. That review is cheap compared with a weekend spent on an unplanned rewrite.

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 postgresql 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.