Your PostgreSQL Autovacuum Deserves a Proper Tuning

Good evening. Allow me a word about your vacuum settings.

I realize this is not the most glamorous topic. Nobody writes excited blog posts about autovacuum. It runs in the background, it cleans up dead tuples, and when it works properly, nobody notices it at all. Which is precisely the problem — when it does not work properly, the symptoms appear elsewhere: queries slow down, disk usage climbs, and the database enters a state of gradual decline that resists diagnosis.

I have seen production databases where a single table had accumulated 47 million dead tuples. The table had 12 million live rows. Autovacuum was enabled. It was simply configured to wait until 20% of the table was dead before acting, and on a table that large, 20% represented an extraordinary amount of waste. By the time vacuum triggered, the bloat was so severe that the vacuum itself took over an hour to complete.

The root cause is almost always the same: autovacuum is configured with PostgreSQL's defaults, which were designed for modest workloads and have not changed materially since version 9.x. This guide will walk you through every setting that matters, explain the mechanical reasons behind the recommendations, and provide specific numbers for your workload. If you will permit me, I should like to be thorough.

Why does PostgreSQL need vacuuming at all?

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When you update a row, PostgreSQL does not overwrite it — it creates a new version and marks the old one as dead. When you delete a row, it marks it as dead. The space is not reclaimed automatically. This is not a design flaw; it is the mechanism that allows concurrent transactions to see consistent snapshots of data without blocking each other.

-- PostgreSQL uses MVCC: updates don't overwrite rows.
-- An UPDATE creates a new version and marks the old one as dead.
-- A DELETE marks the row as dead. Neither frees the space.

-- After 10,000 updates to the same 1,000 rows:
-- Live tuples: 1,000
-- Dead tuples: 10,000
-- Table is 10x larger than it needs to be.

-- Autovacuum cleans the dead tuples. If it falls behind,
-- the table bloats, queries slow, disk fills.

Dead tuples occupy space on disk and must be skipped during sequential scans. A table with 80% dead tuples performs sequential scans on five times more data than necessary. Indexes also accumulate references to dead tuples, inflating their size and slowing lookups. The visibility map — which PostgreSQL consults to determine whether a page can be read from the index alone, without touching the heap — becomes inaccurate, disabling index-only scans on affected pages.

Vacuum's job is threefold: reclaim space occupied by dead tuples, update the visibility map so that index-only scans function correctly, and freeze old transaction IDs to prevent wraparound. All three are essential. None are optional.

How do you know if autovacuum is keeping up?

Before tuning anything, measure. The temptation to copy someone else's settings from a blog post is understandable, but your tables are not their tables. Start with the diagnostic query.

-- Which tables have autovacuum fallen behind on?
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- If dead_pct is above 20% and last_autovacuum was hours ago,
-- autovacuum is not keeping up.

The dead_pct column is your key metric. Below 5% is healthy. Between 5% and 10% is acceptable for large tables with heavy write patterns. Above 20% means autovacuum has fallen behind, and the gap between dead tuple creation and dead tuple cleanup is widening.

Also check last_autovacuum. If a write-heavy table was last vacuumed hours ago, the threshold is set too high — autovacuum is waiting for more dead tuples to accumulate before it acts, like a waiter who waits for the entire house to be dirty before picking up a single glass. The autovacuum_count column tells you how many times autovacuum has run on each table since the last statistics reset. A high-churn table with a low vacuum count is a table that is not being attended to frequently enough.

It is also worth checking whether anyone has already applied per-table overrides.

-- Check per-table autovacuum overrides already in place:
SELECT c.relname,
       c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.reloptions IS NOT NULL
  AND c.relkind = 'r'
ORDER BY c.relname;

-- If reloptions is NULL, the table uses global defaults.
-- If it contains autovacuum_vacuum_scale_factor, someone has tuned it.
-- The question is whether they tuned it correctly.

The defaults: what they are and why they are too conservative

-- PostgreSQL defaults (PG 15/16/17 — unchanged since PG 9.x):
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 2ms   -- PG 12+: was 20ms before
autovacuum_vacuum_cost_limit = 200

-- Translation: vacuum triggers when dead tuples > 50 + (0.2 * table rows)
-- On a 10M row table: 50 + 2,000,000 = vacuum only after 2M dead tuples.
-- That is 20% bloat before autovacuum even starts.

The formula that governs when autovacuum triggers is straightforward, and its implications become clear when you apply it to tables of increasing size.

-- The autovacuum trigger formula:
-- dead_tuples > threshold + (scale_factor * n_live_tup)
--
-- Defaults:   50 + (0.2 * n_live_tup)
-- 1K rows:    50 + 200     = 250 dead tuples   → 25% bloat
-- 100K rows:  50 + 20,000  = 20,050 dead tuples → 20% bloat
-- 1M rows:    50 + 200,000 = 200,050 dead tuples → 20% bloat
-- 10M rows:   50 + 2,000,000 = 2,000,050 dead tuples → 20% bloat
-- 100M rows:  50 + 20,000,000 = 20,000,050 dead tuples → 20% bloat
--
-- Notice: on small tables, vacuum triggers quickly.
-- On large tables, 20% bloat accumulates before vacuum stirs.
-- This is backwards. Large tables are precisely where bloat hurts most.

The problem is visible in the numbers. On a 100-row lookup table, vacuum triggers after 70 dead tuples — perfectly reasonable. On a 100-million-row table, vacuum waits for 20 million dead tuples. At 8KB per tuple, that is 160GB of dead data before vacuum acts. This is not a theoretical concern. I have seen it happen.

The autovacuum_vacuum_cost_delay was improved in PostgreSQL 12, reduced from 20ms to 2ms, which increased vacuum throughput by roughly 10x. That was a meaningful improvement. But the scale factor — the 20% threshold — remains unchanged. The defaults assume a modest database running on modest hardware with modest write rates. If your database is none of those things, the defaults are wrong for you.

I should note: the defaults are not bad. For a development database, a staging environment, or a read-heavy production system with infrequent writes, they are perfectly adequate. The defaults become a problem specifically when tables are large and write-heavy — which describes most production systems that people actually worry about.

Per-table tuning: the most effective change you can make

Different tables have different write patterns, and a single global setting cannot serve them all. A 500-million-row event log that receives 10,000 inserts per second has entirely different vacuum needs than a 200-row configuration table that changes once a month. PostgreSQL provides per-table storage parameters specifically for this reason. Use them.

-- Recommended for write-heavy production workloads:

-- Per-table (ALTER TABLE):
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,    -- 2% instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_analyze_threshold = 500
);

-- Now vacuum triggers at 1000 + (0.02 * 10M) = 201,000 dead tuples
-- instead of 2,000,050. Ten times more responsive.

-- For very large tables (100M+ rows), consider even lower:
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.005,   -- 0.5%
    autovacuum_vacuum_threshold = 5000
);
-- Triggers at 5000 + (0.005 * 100M) = 505,000 dead tuples
-- instead of 20,000,050.

The principle is simple: the scale factor should be inversely proportional to the table size. Small tables can tolerate a high scale factor because 20% of 1,000 rows is 200 dead tuples — trivial. Large tables need a low scale factor because 20% of 100 million rows is 20 million dead tuples — a disaster.

For the recommended values by workload:

For OLTP workloads with high write rates, a scale factor of 0.01-0.05 keeps dead tuple ratios low and prevents the accumulation of bloat. For time-series or append-only tables — where rows are inserted but rarely updated or deleted — the defaults are closer to appropriate, because inserts do not create dead tuples. Only updates and deletes do.

A word of caution: setting the scale factor to 0 and the threshold to 1 is technically possible and will trigger vacuum almost continuously. This is too aggressive for most tables. The goal is not to vacuum constantly — it is to vacuum before bloat accumulates meaningfully. A scale factor of 0.02 on a 10-million-row table triggers vacuum after 200,000 dead tuples, which represents a 2% bloat level. That is a sensible balance between vacuum frequency and vacuum overhead.

Global tuning: workers, cost limits, and naptime

Per-table settings control when vacuum triggers. Global settings control how fast vacuum works and how many tables can be vacuumed simultaneously.

-- Global settings (postgresql.conf or ALTER SYSTEM):

-- More workers = more tables vacuumed in parallel
autovacuum_max_workers = 5           -- default: 3

-- Lower cost delay = faster vacuuming (less sleeping between I/O)
autovacuum_vacuum_cost_delay = 2ms   -- default: 2ms (PG 12+)
autovacuum_vacuum_cost_limit = 400   -- default: 200

-- Naptime = how often autovacuum launcher checks for work
autovacuum_naptime = 15s             -- default: 60s

-- Log vacuum activity for monitoring
log_autovacuum_min_duration = 250ms  -- log any vacuum taking > 250ms

Each of these settings warrants individual attention.

autovacuum_max_workers controls the maximum number of autovacuum processes that can run concurrently. The default of 3 is adequate for databases with fewer than 50 tables. If you have hundreds of tables with active writes, 3 workers may not be enough to keep up — by the time a worker finishes one table and moves to the next, significant bloat has accumulated on the tables it has not yet reached. Five to six workers is typical for busy databases. Going above 8 is rarely beneficial and consumes backend slots that could be used for client connections.

autovacuum_naptime controls how frequently the autovacuum launcher daemon checks for tables that need vacuuming. The default of 60 seconds means that a table that crosses its vacuum threshold must wait up to a minute before a worker is dispatched. Reducing this to 15 seconds is safe and allows vacuum to respond more quickly. On extremely busy systems with many tables, 5 seconds is not unreasonable.

log_autovacuum_min_duration is not a performance setting, but it is essential for understanding what autovacuum is doing. Setting it to 250ms logs every vacuum operation that takes longer than a quarter of a second. This gives you a continuous record of vacuum activity — which tables are being vacuumed, how long each takes, and how many dead tuples are being cleaned. Without this, autovacuum is a black box.

The cost model: why vacuum throttles itself (and when to let it run faster)

Autovacuum is deliberately throttled to avoid disrupting foreground queries. It performs I/O, accumulates a "cost" based on the type of I/O, and when the accumulated cost reaches autovacuum_vacuum_cost_limit, it sleeps for autovacuum_vacuum_cost_delay milliseconds before resuming. The mechanism is simple, but the interaction with multiple workers creates a subtlety that catches many people.

-- How autovacuum throttles itself:
--
-- Every page read costs 1 (vacuum_cost_page_hit if in cache)
-- Every page read from disk costs 10 (vacuum_cost_page_miss)
-- Every dirty page costs 20 (vacuum_cost_page_dirty)
--
-- Vacuum accumulates cost as it works. When cost reaches
-- autovacuum_vacuum_cost_limit (default: 200), vacuum sleeps
-- for autovacuum_vacuum_cost_delay (default: 2ms).
--
-- With defaults: vacuum processes roughly 200 pages, then sleeps 2ms.
-- (Assuming cache hits at cost 1 per page — disk reads at cost 10
-- would reduce throughput proportionally.)
-- That's ~100 iterations per second × 200 pages = 20,000 pages/sec.
-- At 8KB/page = ~156MB/sec throughput. Reasonable for HDD.
--
-- On NVMe (3+ GB/sec capable): this is 5% of disk capacity.
-- Raising cost_limit to 800: ~625MB/sec. Better.
--
-- IMPORTANT: autovacuum_vacuum_cost_limit is shared across all
-- autovacuum workers. 5 workers with cost_limit=200 means each
-- worker gets 200/5 = 40 cost budget. They crawl.
-- With cost_limit=800 and 5 workers: 160 per worker. Adequate.

The critical detail: the cost limit is shared across all autovacuum workers. If autovacuum_vacuum_cost_limit is 200 and you have 5 workers, each worker effectively gets a budget of 40. At that budget, each worker processes approximately 40 pages before sleeping for 2ms. The result is five workers that each crawl along at a fraction of the speed of a single worker with the full budget.

This is why increasing autovacuum_max_workers without increasing autovacuum_vacuum_cost_limit can actually slow down vacuuming. More workers sharing the same budget means each worker does less work per cycle. If you increase workers from 3 to 6, you should at least double the cost limit from 200 to 400 to maintain per-worker throughput.

On modern NVMe storage, the default cost limit of 200 is deeply conservative. NVMe drives can sustain 3+ GB/sec of sequential reads. The default cost model limits vacuum to roughly 156 MB/sec — about 5% of what the hardware can deliver. Raising the cost limit to 400-800 allows vacuum to work faster without meaningfully impacting foreground queries on fast storage. On spinning disks, the defaults are more appropriate, as vacuum I/O competes directly with query I/O for the single disk head.

You can also set per-table cost limits to override the global setting for specific tables. This is useful when one table needs aggressive vacuuming (high cost limit) while the rest of the database should be vacuumed gently.

HOT updates and fillfactor: reducing vacuum's workload

The best vacuum tuning is the vacuum work you avoid entirely. PostgreSQL has a mechanism called Heap-Only Tuple (HOT) updates that can dramatically reduce the dead tuple overhead of updates — if the conditions are right.

-- HOT (Heap-Only Tuple) updates bypass index maintenance entirely.
-- They occur when:
-- 1. The updated columns are NOT in any index
-- 2. There is free space on the same page for the new tuple
--
-- Check HOT update ratio:
SELECT relname,
       n_tup_upd AS updates,
       n_tup_hot_upd AS hot_updates,
       round(100.0 * n_tup_hot_upd / GREATEST(n_tup_upd, 1), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC
LIMIT 10;

-- hot_pct above 90% = excellent. Most updates avoid index churn.
-- hot_pct below 50% = indexes on frequently-updated columns
--                      are generating unnecessary dead index entries.
-- Consider: do you need that index on updated_at?

When a HOT update occurs, the new tuple is placed on the same heap page as the old one, and no index entries need to be created or cleaned up. The dead tuple is still created, but the index maintenance cost is eliminated. On tables with heavy update patterns, this can reduce vacuum's workload by an order of magnitude.

The conditions for HOT updates are straightforward: the updated columns must not appear in any index, and there must be free space on the same page. The first condition is a schema design choice — every index on a frequently-updated column prevents HOT updates for changes to that column. The second condition is controlled by fillfactor.

-- fillfactor reserves space on each page for HOT updates.
-- Default: 100 (pack pages full). No room for HOT updates.
-- For tables with frequent updates:
ALTER TABLE orders SET (fillfactor = 80);

-- Each page now reserves 20% free space.
-- Updated rows can be placed on the same page (HOT eligible).
-- Trade-off: table is ~20% larger on disk, but vacuum has less
-- dead index tuple cleanup to do, and updates are faster.

-- After changing fillfactor, the existing pages are unaffected.
-- Run VACUUM FULL or pg_repack to rewrite with new fillfactor.
-- Or let it take effect gradually as pages are modified.

The trade-off is honest: a lower fillfactor makes the table larger on disk (an 80% fillfactor means the table is roughly 25% larger), but updates are faster and vacuum has less work to do. For tables where the dominant operation is UPDATE rather than SELECT, this is typically a good trade. For read-heavy tables that are rarely updated, keep the fillfactor at 100.

I should note that PostgreSQL 17 and the proposed PostgreSQL 18 changes are making HOT updates possible even when indexed columns are updated, by decoupling the heap and index update paths. This is an area of active improvement, and the situation will get better with each major release.

Transaction ID wraparound: the emergency vacuum you want to prevent

PostgreSQL transaction IDs are 32-bit integers that wrap around after approximately 4 billion transactions. To prevent old data from becoming invisible — or future data from appearing prematurely — PostgreSQL must periodically "freeze" old transaction IDs in every table. This freezing is one of vacuum's responsibilities.

-- Autovacuum also handles transaction ID wraparound prevention.
-- When a table approaches the wraparound threshold, PostgreSQL
-- runs an aggressive "anti-wraparound" vacuum that CANNOT be canceled.

-- Check tables approaching the threshold:
SELECT relname,
       age(relfrozenxid) AS xid_age,
       setting::int AS wraparound_limit,
       round(100.0 * age(relfrozenxid) / setting::int, 1) AS pct_to_wraparound
FROM pg_class c
CROSS JOIN pg_settings s
WHERE s.name = 'autovacuum_freeze_max_age'
  AND c.relkind = 'r'
  AND age(relfrozenxid) > setting::int * 0.5
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

-- If pct_to_wraparound approaches 100%, a forced vacuum will run.
-- On a 500GB table, this can take hours and saturate I/O.
-- Regular vacuuming prevents this emergency entirely.

When a table's relfrozenxid age approaches autovacuum_freeze_max_age (default: 200 million), PostgreSQL launches an aggressive anti-wraparound vacuum. This vacuum cannot be canceled — not by the user, not by autovacuum configuration, not by shutting down and restarting (it will resume where it left off). It runs until it finishes, scanning the entire table to freeze old transaction IDs, consuming I/O the entire time.

On a 500GB table, an anti-wraparound vacuum can run for hours. I have seen it take 14 hours on a particularly large table with slow storage. During that time, the vacuum consumes one autovacuum worker slot, generates sustained sequential I/O, and competes with foreground queries for buffer cache and disk bandwidth.

The way to prevent this is straightforward: ensure regular vacuuming keeps relfrozenxid current. If autovacuum is tuned to run frequently on your write-heavy tables, the freeze work is spread across many small incremental passes rather than one large emergency operation. Each regular vacuum freezes tuples older than vacuum_freeze_min_age, gradually advancing the table's frozen transaction ID without requiring a full scan.

-- Freeze-related settings worth adjusting:

-- vacuum_freeze_min_age: minimum XID age before a tuple is frozen
-- Default: 50,000,000 (50M transactions)
-- Lower = more aggressive freezing during regular vacuum
-- Reduces anti-wraparound emergency risk
vacuum_freeze_min_age = 10000000     -- 10M (more aggressive)

-- vacuum_freeze_table_age: when to scan entire table for freezing
-- Default: 150,000,000 (150M)
vacuum_freeze_table_age = 100000000  -- 100M

-- autovacuum_freeze_max_age: when forced vacuum triggers
-- Default: 200,000,000 (200M)
-- DO NOT lower this below 100M. It forces full table scans.
-- Instead, ensure regular vacuum freezes tuples incrementally.

There is a counterpoint here that deserves mention: some teams aggressively lower vacuum_freeze_min_age and vacuum_freeze_table_age to freeze tuples sooner. This reduces wraparound risk but increases the write amplification of every vacuum pass (frozen tuples must be written back to disk). On write-heavy systems with large tables, this trade-off can generate substantial WAL volume. The right balance depends on your transaction throughput and your tolerance for wraparound risk. For most production systems, a vacuum_freeze_min_age of 10-50 million is reasonable.

Monitoring autovacuum: what to watch and what to alert on

Autovacuum tuning without monitoring is guesswork. You need to know whether your changes are working, and you need to know when they stop working — because workload patterns change, tables grow, and the settings that were adequate last quarter may not be adequate now.

Real-time vacuum progress

-- Monitor autovacuum progress in real time:
SELECT p.pid, a.relname, p.phase,
       p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed,
       round(100.0 * p.heap_blks_vacuumed / GREATEST(p.heap_blks_total, 1), 1) AS pct_done
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON a.pid = p.pid;

The pg_stat_progress_vacuum view (PostgreSQL 9.6+) shows active vacuum operations and their progress. This is invaluable when vacuum is running on a large table and you want to know how long it will take. The phase column shows where vacuum is in its lifecycle: scanning heap, vacuuming indexes, vacuuming heap, cleaning up.

Worker saturation

-- Are autovacuum workers busy or idle?
SELECT count(*) AS active_workers,
       current_setting('autovacuum_max_workers')::int AS max_workers
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

-- If active_workers = max_workers consistently, you need more workers
-- or more aggressive per-table settings so each vacuum finishes faster.

If active_workers consistently equals max_workers, your autovacuum workers are saturated. Every table needing vacuum must wait for a worker to become available. The solution is either more workers, more aggressive per-table settings (so each vacuum finishes faster), or both.

Dead tuple trends

-- Track dead tuple ratios over time.
-- Run this from your monitoring system every 60 seconds:
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Alert thresholds:
-- Warning: dead_pct > 10%
-- Critical: dead_pct > 25%
-- Emergency: dead_pct > 50% (vacuum is severely behind)

This is the single most important autovacuum metric for ongoing monitoring. Track dead_pct as a time series for your largest and most active tables. A healthy database shows a sawtooth pattern: dead tuples accumulate, vacuum runs, dead tuples drop to near zero, and the cycle repeats. An unhealthy database shows a monotonic increase — dead tuples accumulate faster than vacuum can clean them.

Long-running transactions

-- Long-running transactions prevent vacuum from cleaning dead tuples.
-- Any tuple visible to an open transaction's snapshot cannot be vacuumed.
SELECT pid, now() - xact_start AS duration,
       state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- A single forgotten BEGIN in a psql session can prevent vacuum
-- from reclaiming hours of dead tuples across EVERY table.
-- This is the most common reason autovacuum "stops working."

This is the most common reason autovacuum appears to "stop working" even when it is properly configured. Long-running transactions hold back the xmin horizon — the oldest transaction ID that any active transaction needs. Vacuum cannot remove any tuple that might be visible to that transaction. A single idle-in-transaction session that has been open for 6 hours prevents vacuum from cleaning up 6 hours of dead tuples across every table in the database.

If you take one thing from this section: monitor for idle-in-transaction connections and set idle_in_transaction_session_timeout (PostgreSQL 9.6+) to a reasonable value. Five minutes is generous. Thirty seconds is defensible. The right answer depends on your application, but "unlimited" is never correct.

PostgreSQL 17: what changed for autovacuum

PostgreSQL 17 brought several improvements to the vacuum subsystem that are worth understanding, particularly if you are deciding whether to upgrade.

-- PostgreSQL 17 improvements to autovacuum:
--
-- 1. Eager page freezing: vacuum freezes pages more aggressively
--    during normal operation, reducing anti-wraparound risk.
--
-- 2. Improved dead tuple storage: TidStore replaces the old array,
--    using ~20x less memory for large vacuum operations.
--    A vacuum that previously needed 1GB of maintenance_work_mem
--    now needs ~50MB.
--
-- 3. Stream I/O for vacuum: vacuum can issue sequential reads
--    more efficiently, improving throughput on large tables.
--
-- If you are on PG 17, vacuum is meaningfully better out of the box.
-- But the scale_factor defaults are still 0.2. Still too relaxed.

The TidStore improvement is particularly significant. In previous versions, vacuum stored the list of dead tuple IDs in a flat array, which consumed substantial memory for large tables. A vacuum on a table with 50 million dead tuples could require 1-2GB of maintenance_work_mem. In PostgreSQL 17, the new TidStore data structure uses approximately 20x less memory for the same workload. This means vacuum can process larger batches of dead tuples in a single pass without hitting memory limits.

However — and this is the honest counterpoint — the fundamental trigger formula has not changed. The scale factor defaults are still 0.2. The cost limit defaults are still 200. PostgreSQL 17 makes vacuum faster and more efficient when it runs, but it does not change when vacuum decides to run. You still need to tune the trigger thresholds for your workload.

Common autovacuum mistakes

In my experience attending to production databases, these are the errors I encounter most frequently. Each one is understandable. Each one is avoidable.

• Disabling autovacuum on "hot" tables. Some teams disable autovacuum on high-write tables to reduce I/O contention. This guarantees bloat, guarantees eventual wraparound vacuums, and guarantees a worse outcome than the contention they avoided. If vacuum's I/O impact is a concern, reduce the cost limit for that specific table — do not disable vacuum entirely. There is no scenario where disabling autovacuum is the correct choice.
• Using the same settings for every table. A 100-row lookup table and a 500-million-row event log have entirely different vacuum needs. Per-table settings are the mechanism PostgreSQL provides. Using only global settings is like dressing every room in the house identically regardless of its function.
• Increasing workers without increasing the cost limit. As discussed above, the cost budget is shared. Five workers with a cost limit of 200 gives each worker a budget of 40. They accomplish less individually than three workers with a budget of 67 each. Always adjust the cost limit proportionally when adding workers.
• Ignoring autovacuum_vacuum_cost_limit on fast storage. The default of 200 was set when rotational disks were standard. On NVMe storage, 200 throttles vacuum to a fraction of what the hardware can deliver. On fast SSDs, 400-800 is appropriate. On io2 or local NVMe, 1000 is defensible.
• Not monitoring n_dead_tup. If you don't watch it, you won't know it is growing until queries slow down or disk fills up. Add it to your monitoring dashboard. This takes 10 minutes and prevents hours of emergency maintenance.
• Forgetting about long-running transactions. A single idle-in-transaction session with a stale snapshot holds back vacuum for the entire database. Set idle_in_transaction_session_timeout. Monitor for it. This is the silent killer of autovacuum performance.
• Never checking autovacuum_count. Tables with high write churn and a vacuum count of zero are tables that have never been vacuumed — either because autovacuum is disabled, the threshold is unreachable, or vacuum is perpetually preempted by other tables. This column is the simplest way to identify neglected tables.

The full audit: a query to assess your entire database

If you have read this far and want a single query that tells you where your database stands, this is the one. Run it, read the output, and focus on the tables marked CRITICAL and WARNING first.

-- Full autovacuum health audit. Run this on any PostgreSQL database.
-- It tells you exactly where to focus.

WITH table_stats AS (
    SELECT relname, relid,
           n_live_tup, n_dead_tup,
           round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
           last_autovacuum, autovacuum_count,
           pg_total_relation_size(relid) AS total_bytes,
           n_tup_upd + n_tup_del AS churn
    FROM pg_stat_user_tables
)
SELECT relname,
       pg_size_pretty(total_bytes) AS size,
       n_live_tup, n_dead_tup, dead_pct,
       autovacuum_count AS vacuums,
       churn AS updates_and_deletes,
       last_autovacuum,
       CASE
           WHEN dead_pct > 25 THEN 'CRITICAL'
           WHEN dead_pct > 10 THEN 'WARNING'
           WHEN churn > 100000 AND autovacuum_count = 0 THEN 'NEVER VACUUMED'
           ELSE 'OK'
       END AS status
FROM table_stats
WHERE total_bytes > 10 * 1024 * 1024  -- tables > 10MB
ORDER BY
    CASE WHEN dead_pct > 25 THEN 0
         WHEN dead_pct > 10 THEN 1
         ELSE 2 END,
    dead_pct DESC
LIMIT 30;

Three columns to focus on: dead_pct (the percentage of the table that is dead), vacuums (how many times autovacuum has run on this table), and status (the assessment). Any table marked CRITICAL deserves immediate per-table tuning. Any table marked NEVER VACUUMED with significant churn is a table that autovacuum has not reached — either because the threshold is too high, the workers are saturated, or autovacuum is disabled on that table.

The honest limitations of autovacuum tuning

I have spent considerable effort in this guide advocating for aggressive autovacuum tuning, and I stand by every recommendation. But I should be honest about the boundaries of what tuning alone can achieve.

Autovacuum tuning cannot solve a fundamentally mismatched workload. If your table receives 100,000 updates per second and vacuum can only process 50,000 dead tuples per second, no amount of threshold adjustment will close that gap — you need partitioning, schema changes, or a different approach to the workload. Tuning determines when vacuum starts and how fast it works, but vacuum has a ceiling imposed by I/O throughput and CPU availability.

Autovacuum tuning also cannot compensate for long-running transactions. If a reporting query runs for 2 hours, no dead tuples visible to that transaction's snapshot can be vacuumed. The solution is not to tune autovacuum more aggressively — it is to move the reporting query to a read replica or to reduce its runtime.

Finally, per-table tuning requires ongoing maintenance. When tables grow, the settings that were appropriate at 10 million rows may not be appropriate at 100 million rows. When write patterns change — a seasonal spike, a new feature that increases update frequency — the settings need adjustment. Autovacuum tuning is not a one-time exercise; it is a part of operating a database well.

Gold Lapel monitors the query patterns that contribute to vacuum pressure — write-heavy tables, long-running transactions that hold back the xmin horizon, and index-heavy update patterns that prevent HOT updates. The proxy surfaces these patterns before they manifest as bloat, providing the information to act proactively rather than reactively. But the fundamentals described in this guide apply whether you use Gold Lapel or not. A well-tuned autovacuum is a prerequisite for any healthy PostgreSQL deployment.

A final observation

Autovacuum is perhaps the least glamorous subsystem in PostgreSQL, and that is precisely why it is so often neglected. It runs quietly, it asks nothing of you, and when it falls behind, the consequences arrive gradually — a query that used to take 12ms now takes 45ms, a table that used to occupy 4GB now occupies 16GB — until one day the symptoms are severe enough that someone investigates.

The difference between a PostgreSQL deployment that degrades over months and one that hums along for years is often nothing more than 20 minutes spent on autovacuum settings and a monitoring query added to your dashboard. The settings in this guide are not exotic. They are the standard adjustments that every production database deserves. In infrastructure, as in household management, the maintenance that prevents emergencies is always less disruptive than the emergency itself.