All posts

Tuning PostgreSQL Autovacuum for High-Write Workloads

October 26, 2026 · 12 min read · AI‑assisted
postgresqlautovacuumperformancedatabasetuning

Introduction & hook

Imagine this: It’s 3 AM. PagerDuty screams. Your high-traffic API, which just processed a million requests an hour, is dying. Response times are through the roof. Disk space usage is spiking, but you haven’t deployed any new features. You frantically check logs, scale up instances, but nothing helps. The database is the bottleneck. After a harrowing few hours, you discover the culprit: PostgreSQL autovacuum. It failed to keep up.

This isn’t a hypothetical horror story; it’s a common production reality. Your PostgreSQL database, the reliable workhorse of your application, has a hidden cleaning crew: Autovacuum. Its job is to reclaim disk space, prevent data corruption, and ensure query performance. But in high-write environments, this unsung hero can become a silent assassin if not properly configured. Today, we’re going to turn that assassin into a tireless champion.

Autovacuum solves a fundamental problem: PostgreSQL’s Multi-Version Concurrency Control (MVCC). When you UPDATE or DELETE a row, PostgreSQL doesn’t actually delete it immediately. It marks the old version of the row as “dead” and creates a new one (for updates). These dead rows consume disk space and can degrade query performance over time, as the database has to scan more data. Autovacuum is the background process that finds and cleans up these dead rows, making their space available for reuse. It also updates table statistics for the query planner (via ANALYZE), and critically, prevents transaction ID wraparound, a catastrophic event we’ll cover later.

How it works (the visual example)

Think of your database tables like busy restaurants. Every time a customer (transaction) leaves (deletes a row) or orders a new meal (updates a row, creating a new version), they leave behind some crumbs or a used plate. That’s a dead tuple. The restaurant doesn’t stop service just to clean one table. It keeps serving new customers.

Eventually, the restaurant floor gets pretty messy. That’s where autovacuum workers come in. They are like the bussers. Their job is to periodically sweep the floor and clear tables (reclaim space from dead tuples).

Here’s the visual flow:

  1. A busy API endpoint receives POST requests. Each request inserts a new record into user_activity_logs.
  2. A PUT request updates a user’s profile. This marks the old profile row as “dead” and inserts a new one.
  3. A DELETE request removes a temporary session. This marks the session row as “dead.”
  4. Dead tuples accumulate. The table’s physical size grows. Queries become slower because they’re sifting through more data, even if it’s “invisible.”
  5. Autovacuum is triggered. Based on specific thresholds, a dedicated autovacuum worker process wakes up for the user_activity_logs or user_profiles table.
  6. The worker scans the table. It identifies all the dead tuples.
  7. It reclaims space. The dead tuples are marked as reusable space within the table files. It also ANALYZEs the table, updating statistics so the query planner can make smarter decisions.
  8. The cycle repeats. New dead tuples accumulate, and autovacuum workers are triggered again.

The trick in high-write environments is making sure your bussers (autovacuum workers) are numerous enough, fast enough, and smart enough to keep pace with the customers. If they fall behind, your restaurant becomes a chaotic mess.

Real-world use cases

Autovacuum tuning is a lifesaver in scenarios dominated by high-churn tables. These are tables where rows are frequently inserted, updated, or deleted, leading to a rapid accumulation of dead tuples.

Top lifesaver scenarios:

  • Log tables: audit_events, api_requests, server_logs. These tables see continuous INSERTs, often without corresponding DELETEs or UPDATEs, but they still need vacuuming for Transaction ID maintenance.
  • Session stores/Caches: user_sessions, temporary_data. High INSERT/DELETE rates as sessions start and expire.
  • Real-time counters/Aggregates: Tables tracking likes, views, or rapidly changing metrics. Frequent UPDATEs on specific rows.
  • Queues: job_queue_items. High INSERT and DELETE volume as jobs are added and processed.

When it becomes an anti-pattern (or less critical):

  • Static lookup tables: country_codes, product_categories (that rarely change). These tables have very few writes; aggressive autovacuuming is overkill and can waste resources.
  • Read-heavy archives: historical_orders, archived_reports. Once written, these rows are almost never updated or deleted. Default autovacuum settings are usually sufficient, or you might even disable it for specific partitions if you manage manual vacuuming during maintenance windows.
  • When poorly tuned: If autovacuum runs too frequently, or too aggressively without proper resource limits, it can consume excessive CPU/IO, becoming a direct cause of performance degradation rather than a solution.

Implementation & code

PostgreSQL’s autovacuum has global defaults, but for high-write tables, you must use per-table settings. This is where the real magic happens. Trying to tune for high-write tables globally usually means over-tuning for low-write tables, which is inefficient.

Let’s assume we have a user_events table that sees hundreds of INSERTs per second and DELETEs for old events.

First, your global postgresql.conf provides the baseline:

# postgresql.conf snippet

# Autovacuum settings
autovacuum = on                   # Make sure it's on!
autovacuum_max_workers = 5        # Number of autovacuum launcher processes (default 3)
autovacuum_vacuum_cost_delay = 10ms # Delay between vacuum cost charges (default 10ms)
autovacuum_vacuum_cost_limit = 500  # Amount of vacuum work before sleeping (default 200)

# Critical for anti-wraparound (covered later)
old_snapshot_threshold = -1       # Default -1, use cautiously if changing
vacuum_freeze_min_age = 50000000  # Default 50M
vacuum_freeze_table_age = 150000000 # Default 150M

Here’s a breakdown of why these matter:

  • autovacuum_max_workers: This dictates how many tables can be vacuumed concurrently. For high-write systems, increasing this to 5 or even 10 can help autovacuum keep up.
  • autovacuum_vacuum_cost_delay: This is a throttle. A vacuum worker pauses for this many milliseconds once its cost limit is reached. A lower value means less delay, making autovacuum more aggressive, but also potentially more impactful on foreground operations. For high-write tables, you might need to drop this.
  • autovacuum_vacuum_cost_limit: The amount of “work” (disk blocks read/written) a vacuum worker can do before potentially sleeping (controlled by cost_delay). A higher limit means it does more work per burst. Increasing this, coupled with a lower cost_delay, makes autovacuum very eager.

Now, for our user_events table, which is a high-churn scenario:

-- Naive approach: Relying on global defaults (BAD for high-churn)
CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- This table will quickly accumulate dead tuples, grow unbounded, and degrade queries.
-- Autovacuum will trigger based on global scale_factor (default 20% dead tuples)
-- and vacuum with global cost_delay/limit, which might be too slow.


-- Robust, production-ready implementation for high-write tables
ALTER TABLE user_events SET (
    autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.05,  -- Trigger vacuum when 5% of table is dead tuples
    autovacuum_vacuum_threshold = 5000,     -- OR when 5000 dead tuples accumulate (absolute threshold)
    autovacuum_analyze_scale_factor = 0.05, -- Trigger analyze when 5% of table is new/changed
    autovacuum_analyze_threshold = 5000,    -- OR when 5000 new/changed tuples accumulate
    autovacuum_vacuum_cost_delay = 5ms,     -- More aggressive delay for THIS table (default 10ms)
    autovacuum_vacuum_cost_limit = 1000     -- More work per burst for THIS table (default 200)
);

-- WHY this is structured this way:
-- 1. autovacuum_vacuum_scale_factor = 0.05: For very active tables, waiting for 20% dead tuples (the default) is often too long.
--    Reducing this to 5% or even 1% makes autovacuum kick in much earlier, preventing large buildups.
-- 2. autovacuum_vacuum_threshold = 5000: This is crucial for *very large* tables. If a table has 1 billion rows, 1% dead tuples is 10 million dead tuples.
--    This might still be too many to clean efficiently in one go. An absolute threshold of, say, 5000 or 10000 dead tuples ensures autovacuum triggers frequently
--    on a fixed amount of work, keeping the cleaning bursts smaller and more manageable. Autovacuum triggers when EITHER scale_factor OR threshold is met.
-- 3. autovacuum_analyze_scale_factor/threshold: Similar logic for updating table statistics. If stats are stale, the query planner makes bad choices, leading to slow queries.
-- 4. autovacuum_vacuum_cost_delay = 5ms and autovacuum_vacuum_cost_limit = 1000: These overrides make autovacuum for `user_events` run twice as aggressively (half the delay, double the work)
--    as the global default, allowing it to keep up with the high write throughput of this specific table, without impacting other, less busy tables.

Senior-level insights & gotchas

Tuning autovacuum is a dance between reclaiming space/performance and minimizing impact on your live application. It’s easy to get wrong.

The cost-based vacuuming throttle

autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit are your throttle controls.

  • Misconception: Many engineers think lowering cost_delay makes autovacuum “faster.” It does, but it also makes it more intrusive. A lower cost_delay means autovacuum will sleep less, consuming more CPU and IO bandwidth from your system during live traffic.
  • Insight: For high-write tables, you often need to decrease autovacuum_vacuum_cost_delay (e.g., from 10ms to 2ms or 0ms) and increase autovacuum_vacuum_cost_limit (e.g., from 200 to 1000 or 2000) on a per-table basis. This tells autovacuum: “For this table, work harder and longer in each burst.” But you must monitor for foreground impact. The goal is to let autovacuum keep up without making your application queries consistently slower.

Worker scheduling and contention

autovacuum_max_workers defines the global pool of workers.

  • Gotcha: If you have many high-churn tables and autovacuum_max_workers is too low, some tables will always be waiting for a worker, falling behind.
  • Insight: Monitor pg_stat_activity for autovacuum processes. If you frequently see workers queued or tables consistently lagging, you might need to increase autovacuum_max_workers. However, each worker consumes resources. Too many workers can lead to IO contention if your disk subsystem can’t handle multiple concurrent vacuuming operations alongside application queries. Your storage IOPS is the ultimate bottleneck here.

scale_factor vs. threshold

  • autovacuum_vacuum_scale_factor: A percentage of the table size. The default is 20%.
  • autovacuum_vacuum_threshold: An absolute number of dead tuples. The default is 50.
  • Insight: For small tables, scale_factor works well. For very large tables with high write volume (e.g., a billion-row log table), 20% dead tuples could mean hundreds of millions of dead tuples, an unmanageably huge vacuum. Here, threshold becomes king. Setting autovacuum_vacuum_threshold = 50000 (e.g.) ensures autovacuum triggers after a fixed, manageable amount of dead tuples accumulate, regardless of table size. It keeps the cleaning jobs small and frequent, rather than massive and disruptive. You usually combine both, letting autovacuum trigger on whichever condition is met first.

Anti-wraparound emergencies (the big one)

This is perhaps the most critical autovacuum function. PostgreSQL uses Transaction IDs (XIDs), which are 32-bit integers. If the database exhausts its supply of XIDs, it can no longer assign unique IDs to new transactions. This is called XID wraparound, and it means your database will shut down to prevent data corruption. Autovacuum prevents this by “freezing” old transactions, essentially marking them as permanently completed so their XIDs can be recycled.

  • Gotcha: If autovacuum falls behind, especially on tables with very old, un-vacuumed tuples, the database can approach wraparound.
  • Monitoring: Use age(datfrozenxid) for databases and age(relfrozenxid) for tables. This tells you how many transactions have passed since the oldest unfrozen transaction. The limit is around 2 billion. When age(relfrozenxid) approaches vacuum_freeze_table_age (default 150 million), an emergency autovacuum (more aggressive, higher priority) triggers. If it reaches vacuum_freeze_min_age (default 50 million) for a database’s datfrozenxid and that database is not active, it will cause autovacuum to ignore autovacuum_cost_delay entirely, running full-speed.
  • Insight: Regularly monitor these ages! If you see age(relfrozenxid) consistently growing for critical tables, you need to be more aggressive with per-table autovacuum tuning, or manually VACUUM FREEZE that table during a maintenance window.

Monitoring your autovacuum

You can’t tune what you don’t measure.

  • pg_stat_activity: Shows currently running autovacuum workers.
  • pg_stat_all_tables: Critically important view. Look at n_live_tuples, n_dead_tuples, last_autovacuum, last_autoanalyze, and autovacuum_count to understand table activity and if autovacuum is keeping up.
  • pg_settings combined with pg_relation_filepath: Get per-table settings and table sizes.
-- Check autovacuum stats for a specific table
SELECT
    relname,
    n_live_tuples,
    n_dead_tuples,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    pg_size_pretty(pg_relation_size(relid)) AS current_size,
    age(relfrozenxid) AS xid_age
FROM pg_stat_all_tables
WHERE relname = 'user_events';

Summary & production checklist

Mastering autovacuum is non-negotiable for high-performance PostgreSQL. It’s not a fire-and-forget setting; it demands continuous monitoring and adaptive tuning. You want autovacuum to run frequently, but in small, unobtrusive bursts, keeping dead tuples at bay without hogging resources.

Production checklist

  • Enable Autovacuum: Ensure autovacuum = on in postgresql.conf.
  • Increase Workers: If you have many busy tables, consider increasing autovacuum_max_workers (e.g., to 5-10). Monitor IOPS impact.
  • Identify High-Churn Tables: Query pg_stat_all_tables to find tables with high n_dead_tuples or rapid n_dead_tuples growth.
  • Per-Table Tuning: Apply ALTER TABLE overrides for high-churn tables:
    • Lower autovacuum_vacuum_scale_factor: (e.g., 0.01 to 0.05) to trigger vacuums more frequently.
    • Set autovacuum_vacuum_threshold: (e.g., 5000 to 50000) for large tables to keep vacuum bursts manageable.
    • Adjust autovacuum_vacuum_cost_delay: (e.g., 5ms or 2ms) for critical tables to make vacuuming more aggressive.
    • Adjust autovacuum_vacuum_cost_limit: (e.g., 500 to 2000) for critical tables to allow more work per vacuum burst.
  • Analyze Aggressively: Ensure autovacuum_analyze_scale_factor and autovacuum_analyze_threshold are also tuned for high-churn tables to keep query planner statistics fresh.
  • Monitor XID Age: Regularly check age(relfrozenxid) on your critical tables and age(datfrozenxid) for databases. Set alerts if values climb towards vacuum_freeze_table_age (150M).
  • Regularly Review pg_stat_all_tables: Use this view as your dashboard to ensure autovacuum is keeping pace across all your tables.
  • Beware of Long-Running Transactions: Long transactions can hold back vacuuming, preventing dead tuples from being cleaned and delaying XID freezing. Identify and fix them.

Go forth and tame your PostgreSQL autovacuum. Your future self (and your pager) will thank you.