PostgreSQL Vacuum Settings Optimization: Autovacuum Tuning for High-Write Workloads

Introduction

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to handle concurrent transactions. When a row is updated, PostgreSQL does not modify the existing row — it creates a new version and marks the old version as dead. Dead rows accumulate over time and must be cleaned up by the VACUUM process. If vacuuming falls behind, the table grows with dead rows, queries slow down, and in extreme cases, the database can hit transaction ID wraparound and shut down.

I discovered this the hard way when my trading platform's market_data table grew from 12GB to 45GB over the course of two weeks. The table had frequent UPDATE operations (updating prices every second), and the autovacuum process was not keeping up with the dead row accumulation. Here is how I diagnosed the bloat and optimized the vacuum settings.

Environment

• PostgreSQL: 16.1
• Table: market_data (20 million rows, updated frequently)
• Write pattern: ~500 UPDATE/second on peak hours
• OS: Ubuntu 22.04
• Available RAM: 32GB
• PostgreSQL shared_buffers: 8GB

Problem

The market_data table was growing rapidly:

SELECT
    pg_size_pretty(pg_relation_size('market_data')) AS table_size,
    pg_size_pretty(pg_indexes_size('market_data')) AS index_size,
    pg_size_pretty(pg_total_relation_size('market_data')) AS total_size;

 table_size | index_size | total_size
------------+------------+------------
 45 GB      | 12 GB      | 57 GB

This was unexpected for 20 million rows with an average row size of 200 bytes. The expected size was about 4GB for the table data. A 45GB table with 20 million rows meant there were approximately 40GB of dead rows.

I checked the dead row count:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'market_data';

   relname    | n_live_tup | n_dead_tup | dead_pct | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
--------------+------------+------------+----------+-------------+-----------------+--------------+------------------
 market_data  |   20000000 |  800000000 |     97.6 |             | 2024-05-01 03:00|              | 2024-05-01 03:00

800 million dead rows! That is 40 dead rows for every live row. The autovacuum had run only once, and it was not keeping up with the write rate.

I checked the autovacuum configuration:

SHOW autovacuum_max_workers;
-- 3

SHOW autovacuum_naptime;
-- 1min

SHOW autovacuum_vacuum_threshold;
-- 50

SHOW autovacuum_vacuum_scale_factor;
-- 0.2

SHOW autovacuum_analyze_threshold;
-- 50

SHOW autovacuum_analyze_scale_factor;
-- 0.1

The default autovacuum_vacuum_scale_factor of 0.2 means PostgreSQL starts vacuuming a table when 20% of its rows are dead. For a table with 20 million rows, that is 4 million dead rows before vacuuming triggers. With 500 updates per second, 4 million dead rows accumulate in about 2 hours. But autovacuum runs only once per autovacuum_naptime (1 minute) with only 3 workers for the entire database.

Analysis

The fundamental problem was that the default autovacuum settings were designed for typical OLTP workloads, not for a table with 500 updates per second. The scale_factor based threshold was too high — waiting for 20% dead rows meant the table was already significantly bloated before vacuuming started.

Additionally, each autovacuum worker can only vacuum one table at a time. With 3 workers and potentially hundreds of tables, the market_data table might not get vacuumed frequently enough.

I also checked if vacuum was being blocked by long-running transactions:

SELECT
    pid,
    now() - xact_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
AND xact_start < now() - interval '5 minutes';

  pid  |  duration   | state  | query
-------+-------------+--------+------------------------------------
 12345 | 01:23:45.67 | active | SELECT * FROM market_data WHERE ...

A long-running query (1 hour 23 minutes) was preventing vacuum from cleaning up dead rows in the table it was reading. PostgreSQL cannot remove dead rows that are still visible to any active transaction.

Solution

I made changes at the table level and system level:

1. Set aggressive autovacuum settings for the high-write table:

ALTER TABLE market_data SET (
    autovacuum_vacuum_scale_factor = 0.01,      -- Vacuum after 1% dead rows
    autovacuum_analyze_scale_factor = 0.005,     -- Analyze after 0.5% change
    autovacuum_vacuum_cost_delay = 2,            -- Reduce delay between vacuum cycles
    autovacuum_vacuum_cost_limit = 1000,         -- Increase work per cycle
    autovacuum_vacuum_threshold = 1000           -- Minimum dead rows before vacuum
);

With these settings, vacuum triggers when either 1000 dead rows OR 1% of rows are dead (whichever comes first). For a 20 million row table, this means vacuuming starts at 200,000 dead rows instead of 4 million.

2. Increase system-wide autovacuum settings:

-- Increase max workers for databases with many tables
ALTER SYSTEM SET autovacuum_max_workers = 6;

-- Reduce naptime for more frequent checks
ALTER SYSTEM SET autovacuum_naptime = '30s';

-- Increase cost limit for faster vacuuming
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;

-- Reduce cost delay for faster vacuuming
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 2;

-- Reload configuration
SELECT pg_reload_conf();

3. Run a manual VACUUM to recover space:

-- Full vacuum to reclaim space (requires exclusive lock)
VACUUM FULL market_data;

-- Or use pg_repack for online vacuum without locking
-- pg_repack --table=market_data --no-superuser-check mydb

4. Prevent long-running transactions from blocking vacuum:

-- Set a statement timeout to kill long queries
ALTER ROLE analytics_user SET statement_timeout = '5min';

-- Set idle-in-transaction timeout
ALTER ROLE analytics_user SET idle_in_transaction_session_timeout = '60s';

-- Monitor long transactions
CREATE OR REPLACE FUNCTION check_long_transactions()
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'Long transactions:';
    PERFORM dblink_exec(
        'host=localhost dbname=mydb',
        format(
            'SELECT pg_terminate_backend(pid)
             FROM pg_stat_activity
             WHERE state != ''idle''
             AND xact_start < now() - interval ''5 minutes''
             AND pid != pg_backend_pid()'
        )
    );
END;
$$ LANGUAGE plpgsql;

5. Monitor bloat and vacuum health:

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
        THEN round(100.0 * n_dead_tup / n_live_tup, 1)
        ELSE 0
    END AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

After implementing all changes and running a manual vacuum:

SELECT
    pg_size_pretty(pg_relation_size('market_data')) AS table_size,
    pg_size_pretty(pg_indexes_size('market_data')) AS index_size,
    pg_size_pretty(pg_total_relation_size('market_data')) AS total_size;

 table_size | index_size | total_size
------------+------------+------------
 4 GB       | 3 GB       | 7 GB

The table shrank from 45GB to 4GB. Over the following week, the table stayed at approximately 5GB with the new autovacuum settings keeping dead rows under control.

I also set up monitoring for vacuum health:

#!/bin/bash
# monitor-vacuum.sh — Check for tables with high dead row ratios

psql -d mydb -t -c "
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
    now() - last_autovacuum AS since_last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
AND now() - last_autovacuum > interval '1 hour'
ORDER BY n_dead_tup DESC;
" | while read line; do
    if [ -n "$line" ]; then
        echo "$(date): WARNING: Table with high dead rows: $line"
    fi
done

Lessons Learned

1. Tune autovacuum per table — High-write tables need more aggressive vacuum settings. Use ALTER TABLE ... SET to override defaults for specific tables.
2. Reduce scale_factor for large tables — The default 20% threshold is too high for large, frequently updated tables. Set it to 1-5% for high-write tables.
3. Watch for long transactions — A single long-running transaction can prevent vacuum from cleaning up dead rows across the entire table. Set timeouts for all roles.
4. Monitor dead row ratios — Check pg_stat_user_tables regularly for tables with high dead row counts. This is an early warning sign of vacuum issues.
5. Increase autovacuum workers and cost_limit — For databases with many tables or high write rates, the default settings are insufficient. Increase autovacuum_max_workers and autovacuum_vacuum_cost_limit.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.