PostgreSQL Autovacuum SLO Tuning: How to Configure Vacuum for 200M Rows and 5k UPSERT/s

Autovacuum is the silent janitor until it stops showing up. “Why is the database slow? It worked yesterday.” I look at pg_stat_user_tables and see 50 million dead tuples. Autovacuum is running, but can’t keep up. The table has bloated to 3x its efficient size, index scans are touching 3x the pages they should, and every query is slower than it needs to be.

This is the classic PostgreSQL vacuum failure pattern. Autovacuum is enabled (it almost always is), but the default thresholds were designed for small databases. When you scale to millions of rows and thousands of updates per second, those defaults become actively harmful—they let dead tuples accumulate to tens of millions before triggering cleanup.

The problem is that most teams either ignore autovacuum entirely (“it’s automatic, right?”) or cargo-cult tune it by copying random Stack Overflow settings without understanding why. Neither approach works. What you need is a systematic method to calculate vacuum thresholds based on your actual workload and your SLOs for dead tuple ratio and bloat.

The good news is that this math is straightforward once you understand it. You know your update rate. You know your acceptable dead tuple ratio. From those, you can calculate exactly what vacuum thresholds you need. Then you configure per-table settings for your high-churn tables and let autovacuum do its job.

Tested on: PostgreSQL 16.1, Ubuntu 22.04, 32GB RAM, NVMe SSD, pgbench + custom workload

Why Default Autovacuum Can’t Keep Up

Dead Tuple Math

On UPDATE in PostgreSQL:

1. Old row is marked as “dead” (not physically deleted)
2. New row is written
3. Dead tuples take space and slow down index scans

Dead tuples per hour = UPDATE rate × 3600
5000 UPSERT/s × 3600 = 18 million dead tuples/hour

Default Trigger Thresholds

-- Default settings
autovacuum_vacuum_threshold = 50        -- min dead tuples
autovacuum_vacuum_scale_factor = 0.2    -- 20% of table

-- For a 200M row table:
-- Trigger = 50 + (200M × 0.2) = 40,000,050 dead tuples
-- At 5k UPSERT/s = vacuum every 2.2 hours

Problem: By the time vacuum runs, you have 40M dead tuples = bloat, slow queries, wasted I/O.

SLO-Driven Vacuum Configuration

Step 1: Define SLO

## Vacuum SLO
- Max dead tuples: 5% of table size (10M for 200M table)
- Max bloat ratio: 20%
- Vacuum frequency: every 30 minutes under high load

Step 2: Calculate Vacuum Rate

# vacuum_rate_calculator.py
from dataclasses import dataclass

@dataclass
class VacuumSLO:
    table_rows: int
    update_rate_per_sec: int
    max_dead_tuple_ratio: float  # e.g., 0.05 = 5%
    target_vacuum_interval_min: int

def calculate_vacuum_settings(slo: VacuumSLO) -> dict:
    # Max allowed dead tuples
    max_dead = int(slo.table_rows * slo.max_dead_tuple_ratio)

    # Dead tuples per target interval
    dead_per_interval = slo.update_rate_per_sec * slo.target_vacuum_interval_min * 60

    # Scale factor to trigger vacuum in time
    # trigger = threshold + (scale_factor × table_rows)
    # We want: trigger = dead_per_interval
    # scale_factor = (trigger - threshold) / table_rows

    threshold = 1000  # low fixed threshold
    scale_factor = (dead_per_interval - threshold) / slo.table_rows

    return {
        "autovacuum_vacuum_threshold": threshold,
        "autovacuum_vacuum_scale_factor": round(max(0.001, scale_factor), 4),
        "expected_trigger_dead_tuples": dead_per_interval,
        "max_allowed_dead_tuples": max_dead
    }

# Example for our table
slo = VacuumSLO(
    table_rows=200_000_000,
    update_rate_per_sec=5000,
    max_dead_tuple_ratio=0.05,
    target_vacuum_interval_min=30
)

settings = calculate_vacuum_settings(slo)
print(settings)
# {'autovacuum_vacuum_threshold': 1000,
#  'autovacuum_vacuum_scale_factor': 0.045,
#  'expected_trigger_dead_tuples': 9000000,
#  'max_allowed_dead_tuples': 10000000}

Step 3: Apply Per-Table Settings

-- Settings for specific table
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.045,
    autovacuum_vacuum_cost_limit = 2000,  -- more aggressive
    autovacuum_vacuum_cost_delay = 2      -- less pausing
);

-- Verification
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'high_churn_table';

Monitoring: pg_stat Metrics

Vacuum Health Dashboard Query

-- vacuum_health.sql
WITH table_stats AS (
    SELECT
        schemaname,
        relname,
        n_live_tup,
        n_dead_tup,
        CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / n_live_tup, 2)
            ELSE 0
        END as dead_tuple_ratio,
        last_vacuum,
        last_autovacuum,
        vacuum_count,
        autovacuum_count,
        EXTRACT(EPOCH FROM (now() - last_autovacuum))/60 as minutes_since_vacuum
    FROM pg_stat_user_tables
    WHERE n_live_tup > 100000  -- large tables only
)
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    dead_tuple_ratio || '%' as dead_ratio,
    round(minutes_since_vacuum) || ' min' as since_vacuum,
    CASE
        WHEN dead_tuple_ratio > 20 THEN 'CRITICAL'
        WHEN dead_tuple_ratio > 10 THEN 'WARNING'
        ELSE 'OK'
    END as status
FROM table_stats
ORDER BY dead_tuple_ratio DESC;

Bloat Estimation

-- Real bloat (requires pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_name,
    pg_size_pretty(table_len) as table_size,
    round(100 - (tuple_len::float / table_len * 100), 2) as bloat_pct,
    pg_size_pretty(table_len - tuple_len) as wasted_space
FROM (
    SELECT
        'high_churn_table'::text as table_name,
        (pgstattuple('high_churn_table')).*
) t;

Reproducible Benchmark

Test Setup

#!/bin/bash
# vacuum_benchmark.sh

# Create test table
psql -c "
CREATE TABLE vacuum_test (
    id bigserial PRIMARY KEY,
    data jsonb,
    updated_at timestamp DEFAULT now()
);

-- Populate with 10M rows (scaled for test)
INSERT INTO vacuum_test (data)
SELECT jsonb_build_object('value', generate_series(1, 10000000));

-- Create index
CREATE INDEX idx_vacuum_test_updated ON vacuum_test(updated_at);
"

# Simulate UPDATE workload
pgbench -c 10 -j 4 -T 3600 -f - <<EOF
\set id random(1, 10000000)
UPDATE vacuum_test SET data = jsonb_build_object('value', :id), updated_at = now() WHERE id = :id;
EOF

Results: Default vs Tuned

Gotchas

1. Vacuum vs Analyze

-- Vacuum and Analyze have SEPARATE thresholds!
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.045,
    -- Analyze can be less frequent
    autovacuum_analyze_threshold = 10000,
    autovacuum_analyze_scale_factor = 0.1
);

2. Cost Limit Coordination

-- Global limit is SHARED between all vacuum processes
-- If you have 3 autovacuum workers and limit 200:
-- Each worker effectively has ~67

-- For aggressive vacuum on one table:
ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_cost_limit = 1000,  -- dedicated limit
    autovacuum_vacuum_cost_delay = 2
);

3. Long Running Transactions Blocker

-- Vacuum CANNOT clean tuples visible to active transactions
SELECT
    pid,
    now() - xact_start as duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '5 minutes';

Prometheus Metrics

# prometheus_vacuum_rules.yml
groups:
  - name: postgresql_vacuum
    rules:
      - alert: HighDeadTupleRatio
        expr: |
          pg_stat_user_tables_n_dead_tup /
          (pg_stat_user_tables_n_live_tup + 1) > 0.1
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "High dead tuple ratio on {{ $labels.relname }}"

      - alert: VacuumNotRunning
        expr: |
          time() - pg_stat_user_tables_last_autovacuum > 7200
        for: 5m
        labels:
          severity: critical

Checklist

## Autovacuum SLO Checklist

### Analysis
- [ ] Identify high-churn tables (UPDATE/DELETE rate)
- [ ] Measure current dead tuple ratio
- [ ] Calculate required vacuum frequency

### Configuration
- [ ] Set per-table autovacuum thresholds
- [ ] Increase cost_limit for critical tables
- [ ] Reduce cost_delay for faster vacuum

### Monitoring
- [ ] Dashboard for dead tuple ratio
- [ ] Alert on bloat > 20%
- [ ] Track vacuum duration and frequency

### Prevention
- [ ] Kill idle transactions > 5 min
- [ ] Monitor replication slots
- [ ] Regular VACUUM ANALYZE for statistics

Conclusion

Autovacuum is one of those PostgreSQL features that works brilliantly for small databases and fails silently for large ones. The defaults assume you have a few thousand rows and modest update rates. When you scale to millions of rows and thousands of updates per second, those defaults become liabilities.

The key insight is that autovacuum configuration should be SLO-driven, not guesswork. You define what you can tolerate (5% dead tuple ratio, 20% bloat), calculate the vacuum frequency needed to maintain that, and configure thresholds accordingly. This is reproducible engineering, not cargo-cult tuning.

The difference is dramatic. With default settings, a high-churn table accumulates 40+ million dead tuples before vacuum runs. With proper SLO-driven settings, you keep dead tuples under 10 million. That’s the difference between bloated, slow queries and efficient, predictable performance.

Key principles:

1. Calculate vacuum frequency from UPDATE rate - dead tuples = updates × time
2. Set per-table thresholds - one size does not fit all tables
3. Monitor dead tuple ratio continuously - it’s your leading indicator of vacuum health
4. Watch for vacuum blockers - long transactions and replication slots prevent cleanup
5. Alert before problems escalate - 10% dead tuple ratio is a warning, 20% is critical

Default settings are for small databases. With 200M+ rows, you need SLO-driven, per-table vacuum configuration.

Related Articles

• Zero-downtime PostgreSQL Migrations - Safe schema changes
• K8s PostgreSQL Connection Storm - Connection management