Postgres Autovacuum Tuning for Write-Heavy Tables: Thresholds and Workers

What are dead tuples and why does PostgreSQL need VACUUM?

PostgreSQL uses multi-version concurrency control (MVCC), so it never overwrites a row in place. An UPDATE writes a new version of the row (a tuple) and marks the old one as expired. A DELETE only marks the existing tuple as expired. The old versions have to stay on disk because a transaction that started earlier may still need to see them. Once no running transaction can see an expired tuple, it becomes a dead tuple. It still takes up space in the heap page, and index entries still point to it.

VACUUM cleans these up. It scans the table, removes index entries that point to dead tuples, and marks the space they used as free in the free space map. Later inserts and updates can then reuse that space. Vacuum also updates the visibility map, which lets index-only scans skip heap lookups and lets later vacuums skip pages that are already clean. It also freezes old transaction IDs, which a later section covers.

Plain VACUUM usually does not make the file smaller. It can only return pages at the end of the table to the operating system. Free space in the middle of the file stays allocated and is kept for reuse. When dead or free space grows faster than it is reused, the table and its indexes get larger than the live data needs. That excess is table bloat. It costs more I/O on scans and more memory in the buffer cache.

Autovacuum is a background launcher that starts worker processes to run VACUUM and ANALYZE on tables when enough rows have changed. You don't have to schedule these runs yourself.

Copy UPDATE ──► new tuple + old expired      DELETE ──► tuple expired
   │                                       │
   └──────────────────────────┬────────────┘
                              ▼
 ┌────────────────────────────────────────────────────────────┐
 │ Heap page: [live v2][expired v1][dead][live][dead]         │
 │ index entries still point at the dead tuples               │
 │ expired → dead once no running transaction can see it      │
 └────────────────────────────┬───────────────────────────────┘
                              │ enough rows changed
                              ▼
 Autovacuum launcher ──► worker process ──► VACUUM, ANALYZE
                                              │
        ┌──────────────────┬──────────────────┼──────────────────┐
        ▼                  ▼                  ▼                  ▼
 remove index       mark space free    update the         freeze old
 entries for dead   in free space map  visibility map     transaction IDs
 tuples             (FSM)
                           │
                           ▼
              later INSERT/UPDATE reuse that space
                           │
        ┌──────────────────┴───────────────────┐
        ▼                                      ▼
 free pages at END of file:             free space in MIDDLE:
 truncated, returned to OS              stays allocated in the file
                                               │ grows faster than reuse
                                               ▼
                                 TABLE BLOAT: more scan I/O, more cache

MVCC leaves expired tuples that become dead tuples. Autovacuum workers run VACUUM to make that space reusable, but only free pages at the end of the file are returned to the OS, so space that is not reused fast enough builds up as table bloat.

How does autovacuum work?

Autovacuum has two kinds of processes. The launcher runs all the time when autovacuum is on. It tries to start a worker for each database once per autovacuum_naptime interval, and it never runs more than autovacuum_max_workers workers at once. If every worker slot is busy, the remaining databases and tables wait until a slot is free.

A worker connects to one database and builds a list of tables to process. For each table it checks the cumulative statistics that the server tracks per table: dead tuples, rows changed since the last analyze, and rows inserted since the last vacuum (on versions that have the insert-based trigger). It compares each count to a threshold. The threshold comes from the global settings, unless the table has its own storage parameters, in which case those win. The worker also checks how old the table's oldest unfrozen transaction ID is. A table qualifies for:

• Vacuum when dead tuples exceed the vacuum threshold, or when the table is old enough to need an anti-wraparound vacuum.
• Insert-triggered vacuum when inserted rows exceed the insert threshold.
• Analyze when rows changed since the last analyze exceed autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples.

The worker then processes qualifying tables one at a time. While it works, it accumulates I/O cost and sleeps whenever it reaches its share of the cost limit. That throttling keeps autovacuum from saturating storage, but it also limits how fast autovacuum can keep up.

So tuning comes down to three things, and the rest of this article covers each one:

1. When a table qualifies: thresholds and scale factors.
2. How many tables can be processed at once: autovacuum_max_workers and autovacuum_naptime.
3. How fast each run goes: the cost limit and cost delay.

How does the autovacuum trigger formula delay vacuum on large tables?

Autovacuum decides a table needs vacuuming when its count of dead tuples exceeds a threshold calculated per table:

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

reltuples is the planner's row-count estimate stored in pg_class. The defaults are a base threshold of 50 rows and a scale factor of 0.2, so a table qualifies once about 20% of its rows are dead. Because the scale factor is multiplied by table size, the trigger point grows with the table.

Illustrative estimate (assumed inputs: default settings, the row counts shown):

On a small table, 20% churn is a few thousand rows, and the scale factor keeps autovacuum from running constantly. On a 500-million-row table, the same rule allows about 100 million dead tuples to build up first. On a hot table, those dead tuples tend to sit in the pages your queries read most. That table bloat makes index and heap scans slower, and when vacuum finally starts it has far more work to do in a single pass.

You need tuning if a table is large, receives frequent updates or deletes, and serves latency-sensitive reads. For those tables, a percentage-based trigger reacts too late. Small and medium tables usually work fine with the defaults.

Who should tune autovacuum?

Per-table autovacuum tuning is for the engineers who own a PostgreSQL database's schema and its production behavior. That usually means DBAs, SREs, or backend engineers who write the migrations. It pays off when at least one of these describes your workload:

• Large tables with frequent UPDATE or DELETE. Order, session, inventory, and status tables, where the default 20% scale factor lets millions of dead tuples build up between runs.
• Queue-like tables. Rows are inserted, processed, and deleted soon after. The table stays small in live rows, but the churn is constant, and bloat shows up quickly as slower polling queries.
• Large append-only tables. Event, log, and audit tables that rarely produce dead tuples but still need visibility map updates for index-only scans and freezing to avoid a large anti-wraparound vacuum at a bad time.
• Clusters where autovacuum is visibly behind. n_dead_tup stays high, last_autovacuum is old on busy tables, or workers are always busy on the same few large tables.

You can usually leave the defaults alone for small databases, mostly-read workloads, and tables that fit comfortably in memory and see modest churn. If autovacuum is running but cannot remove dead tuples, look for the blockers described in the last sections before you tune anything.

How do you measure dead tuple rates before tuning autovacuum?

Start with pg_stat_user_tables. For each candidate table, read n_live_tup, n_dead_tup, last_autovacuum and autovacuum_count, and sort by n_dead_tup descending. A high dead-to-live ratio combined with an old last_autovacuum timestamp identifies a table where the default trigger is lagging. A high autovacuum_count paired with a persistently high n_dead_tup points to a different problem: vacuum runs often but cannot keep up. That is a cost-limit or worker issue, not a threshold issue.

A single snapshot only shows current state. To get a rate, sample the view twice, for example 15 minutes apart during peak write load. Subtract the readings and divide by the interval. Discard the pair if autovacuum_count changed between samples, because the vacuum reset n_dead_tup partway through. The cumulative counters n_tup_upd and n_tup_del never drop when vacuum runs, so their deltas give a steadier estimate of how many dead tuples the workload produces. Treat all of these numbers as approximations. The statistics system tracks them loosely, and a stats reset clears them.

During a vacuum, query pg_stat_progress_vacuum and join on relid. The phase column, together with heap_blks_scanned against heap_blks_total, shows how far the run has progressed. If the run repeatedly passes through the index-vacuuming phase, the worker's memory for dead tuple identifiers filled before the heap scan finished.

The rate leads to a threshold. Multiply dead tuples per minute by the longest interval you will tolerate between vacuums. The result is the dead tuple count at which vacuum should trigger.

Copy-- 1. Snapshot: tables with the most dead tuples
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 2. Rate: sample during peak load, wait, then diff (run in psql autocommit mode)
CREATE TEMP TABLE vac_sample AS
SELECT relid, n_dead_tup, n_tup_upd, n_tup_del, autovacuum_count,
       clock_timestamp() AS ts
FROM pg_stat_user_tables;

SELECT pg_sleep(900);  -- 15 minutes

SELECT c.relname,
       round(((c.n_dead_tup - s.n_dead_tup) / m.mins)::numeric, 1) AS dead_per_min,
       round(r.upd_del / m.mins, 1) AS upd_del_per_min,
       -- trigger point if you tolerate at most 60 minutes between vacuums
       round(r.upd_del / m.mins * 60) AS threshold_for_60_min
FROM vac_sample s
JOIN pg_stat_user_tables c USING (relid)
CROSS JOIN LATERAL (SELECT extract(epoch FROM clock_timestamp() - s.ts)::numeric / 60 AS mins) m
CROSS JOIN LATERAL (SELECT ((c.n_tup_upd + c.n_tup_del)
                          - (s.n_tup_upd + s.n_tup_del))::numeric AS upd_del) r
-- discard pairs where autovacuum ran and reset n_dead_tup mid-interval
WHERE c.autovacuum_count = s.autovacuum_count
ORDER BY upd_del_per_min DESC;

-- 3. During a run: progress per table; index_vacuum_count > 1 means
--    dead-TID memory filled before the heap scan finished
SELECT p.pid, c.relname, p.phase,
       p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct_scanned,
       p.index_vacuum_count
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid;

Queries that rank tables by dead tuples, turn two pg_stat_user_tables samples taken 15 minutes apart into a per-minute dead-tuple rate and a trigger threshold, and watch pg_stat_progress_vacuum while a vacuum runs.

Setting per-table autovacuum thresholds with ALTER TABLE

That number is what you give the table directly. For a hot table, set autovacuum_vacuum_scale_factor to 0 and autovacuum_vacuum_threshold to the count you calculated. Autovacuum then fires after a fixed number of dead tuples, whether the table holds one million rows or one billion. With the default scale factor, the trigger point grows with the table, so the tables with the most churn wait the longest.

Statistics need the same treatment. A fast-growing table can outrun its planner statistics long before it accumulates enough dead tuples to matter. New key ranges then look empty to the planner, and it picks poor plans. Setting autovacuum_analyze_scale_factor low, or to 0 with a fixed autovacuum_analyze_threshold, keeps estimates current as rows arrive.

Append-only tables produce almost no dead tuples, so the vacuum threshold rarely triggers. On versions that support them, autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor start vacuum based on inserted rows. This keeps the visibility map current for index-only scans and freezes tuples in smaller batches.

Per-table overrides are better than global changes because only a few tables usually cause the problem. Lowering the global scale factor makes autovacuum revisit thousands of small, quiet tables, and those visits compete with your hot tables for the same workers.

The overrides are stored as storage parameters in pg_class.reloptions. A manual change made during an incident is easy to lose or forget. Put each override in a schema migration or in config management, next to the table definition. This keeps every environment consistent and makes the reason for each value reviewable.

Copy-- Migration: autovacuum overrides for write-heavy tables.
-- Keep this in version control so every environment gets the same values.

-- Hot update/delete table: vacuum after a fixed dead-tuple count,
-- independent of table size (scale factor 0 disables proportional growth).
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 50000,
  -- Refresh planner statistics on a fixed row-change count as well.
  autovacuum_analyze_scale_factor = 0,
  autovacuum_analyze_threshold = 20000
);

-- Append-only table: few dead tuples, so trigger vacuum on inserts
-- (PostgreSQL 13+) to keep the visibility map current and freeze in
-- smaller batches.
ALTER TABLE events SET (
  autovacuum_vacuum_insert_scale_factor = 0,
  autovacuum_vacuum_insert_threshold = 100000,
  autovacuum_analyze_scale_factor = 0.01
);

-- Verify what is actually stored, to detect drift from the migration.
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('orders', 'events');

-- To remove an override and fall back to the global setting:
-- ALTER TABLE orders RESET (autovacuum_vacuum_threshold);

A migration that sets fixed vacuum and analyze thresholds on a hot table, insert-based vacuum triggers on an append-only table, and checks the stored values in pg_class.reloptions.

How do cost limits and autovacuum_max_workers interact?

Autovacuum throttles itself with a cost budget. Each page it reads or dirties adds cost units. When the running total reaches autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay, then continues. This limit is shared: the launcher divides it among all active workers. The exception is tables that have their own per-table cost settings, which are left out of the balancing. Raising autovacuum_max_workers alone therefore does not add vacuum capacity. It splits the same budget into smaller pieces, so each large-table vacuum runs longer, and dead tuples keep piling up while it does.

Illustrative estimate (assumed numbers, not a benchmark). Assume a cost limit of 200, a delay of 2 ms, an average cost of 10 per page and 8 KB pages. The upper bound, ignoring the time spent doing the work, is:

pages/s = (limit ÷ cost per page) × (1000 ÷ delay_ms) = 20 × 500 = 10,000 pages/s ≈ 80 MB/s in total.

With four busy workers, each gets about 20 MB/s.

Set the worker count to the number of large tables that regularly need vacuuming at the same time. Then raise the cost limit in proportion so each worker keeps its throughput. Shortening naptime helps when many databases or tables are waiting.

On storage that is already saturated, a higher budget turns into I/O bursts that raise query latency. In that case, lower per-table thresholds so each vacuum run is smaller and more frequent, rather than making each run more aggressive.

Copy-- Scale workers and the shared budget together.
-- 4 workers each keeping the old single-worker rate needs 4 x 200.
ALTER SYSTEM SET autovacuum_max_workers = 4;
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 800;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_naptime = '30s';
SELECT pg_reload_conf();

-- context = 'postmaster' means the setting needs a restart.
-- pending_restart shows a changed value that is not yet active.
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('autovacuum_max_workers',
               'autovacuum_vacuum_cost_limit',
               'autovacuum_vacuum_cost_delay',
               'autovacuum_naptime');

-- On saturated storage, trigger vacuum earlier so each run is smaller.
-- Only thresholds are set here. Per-table cost settings would take the
-- table out of budget balancing.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000
);

-- See how many workers are busy and how far each one has scanned.
SELECT p.pid,
       p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total
FROM pg_stat_progress_vacuum AS p;

Raises autovacuum_max_workers and the shared cost limit together, uses pg_settings to see which changes still need a restart, makes per-table vacuums smaller with lower thresholds, and monitors the workers that are running.

Why do append-only tables still hit anti-wraparound vacuums?

An append-only table generates almost no dead tuples, so the dead-tuple trigger from earlier sections rarely fires. The table still needs vacuum for two reasons.

Visibility map maintenance. Vacuum sets the all-visible bit for pages whose rows every transaction can see. Index-only scans rely on that bit. Without it, they fall back to fetching heap pages, so a freshly loaded table that has never been vacuumed loses much of the benefit of covering indexes.

Transaction ID freezing. Transaction IDs are 32-bit and compared modulo about two billion. Once a row's inserting XID is old enough, it must be marked frozen, or it would eventually appear to be in the future and become invisible. When a table's relfrozenxid age passes autovacuum_freeze_max_age, PostgreSQL forces an anti-wraparound vacuum.

Why these vacuums hurt

An anti-wraparound vacuum starts even if autovacuum is disabled for the table. It also behaves differently from a regular autovacuum:

• It is not cancelled automatically. A regular autovacuum yields when another session requests a conflicting lock. An anti-wraparound vacuum holds its lock, so a routine ALTER TABLE or TRUNCATE queues behind it, and every query queues behind that.
• It is aggressive. It must visit every page not already marked all-frozen. On a large, never-frozen append-only table, that means reading most of the table.

Keeping them off-peak

Track age(relfrozenxid) from pg_class for your largest tables and compare it to autovacuum_freeze_max_age. When a table approaches the limit, run VACUUM (FREEZE) during a quiet window. The forced vacuum then finds little left to do.

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

No threshold setting helps if vacuum is not allowed to remove dead tuples. Vacuum can only remove row versions older than the oldest snapshot still in use, so a single old snapshot pins the xmin horizon for the entire cluster. Check for these before you tune:

• Long or idle-in-transaction sessions: look at backend_xmin, xact_start and state = 'idle in transaction' in pg_stat_activity. Setting idle_in_transaction_session_timeout caps the damage.
• Stale replication slots: inactive rows in pg_replication_slots with a non-null xmin or catalog_xmin.
• Forgotten prepared transactions: old prepared timestamps in pg_prepared_xacts.

If autovacuum runs often but n_dead_tup does not fall, one of these is usually the cause.

A very low vacuum scale factor can also waste I/O. If a small set of hot rows is updated constantly, vacuuming the whole table more often does little for them. Compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables. If HOT updates are rare, the likely causes are updates that touch indexed columns or pages with no free space. Removing unnecessary indexes or lowering fillfactor addresses the cause.

For bloat that already exists:

How can autovacuum tuning save engineering time?

Most of the engineering time spent on vacuum problems is reactive. Someone investigates a slow query, finds a bloated table or index, schedules a maintenance window for a rewrite, and later deals with an anti-wraparound vacuum blocking a migration. Tuning moves that work earlier, into one-time configuration that runs unattended:

• Fewer bloat investigations. A fixed per-table threshold keeps dead tuples on hot tables bounded, so query plans and scan costs stay predictable. Engineers spend less time explaining latency regressions that turn out to be bloat.
• Fewer emergency rewrites. Bloat that never builds up does not need VACUUM FULL or an online rebuild, along with the downtime planning, disk-space checks, and testing those require.
• No surprise anti-wraparound stalls. Insert-based vacuum on append-only tables and freeze-age monitoring on large tables mean freezing happens in small batches. Schema migrations are less likely to queue behind an uncancellable vacuum during business hours.
• Less custom tooling. Once autovacuum keeps up, cron-driven manual VACUUM jobs, and the monitoring needed to make sure those jobs ran, can usually be retired.
• Reviewable, repeatable settings. Overrides kept in migrations are applied the same way in every environment, and the reasons are recorded next to the table definition. New team members do not have to rediscover why a table behaves differently.

Illustrative estimate (assumed inputs, not a measured result). Assume a team has one bloat-related incident per month, and each one takes 6 engineer-hours to diagnose and fix, plus 4 more hours to plan and run a maintenance-window rewrite. That is about 120 engineer-hours a year. Now assume the measurement queries and per-table migrations in this article take 16 hours once, plus 1 hour a month to review dead-tuple and freeze-age trends. That is about 28 hours in the first year. With these assumed inputs, the tuning work costs less than a quarter of the reactive work it replaces. Your own numbers depend on how often bloat actually causes incidents in your system.

The savings depend on doing the measurement first. Thresholds based on sampled dead-tuple rates fix the tables that need it. Guessed global changes tend to create new work, such as extra I/O on quiet tables or worker contention on busy ones.

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.