PostgreSQL VACUUM and Autovacuum Tuning: Complete DBA Guide

TL;DR — Quick Summary

PostgreSQL VACUUM and autovacuum tuning: configure workers, thresholds, cost delay, monitor bloat, prevent transaction ID wraparound, and use pg_repack safely.

PostgreSQL’s multiversion concurrency control (MVCC) model keeps old row versions alive to serve concurrent readers. Without regular cleanup, those dead tuples accumulate, inflate table size, slow sequential scans, and eventually threaten database availability through transaction ID wraparound. This guide covers every lever a DBA can pull — from global autovacuum parameters to per-table overrides, from bloat monitoring queries to online reorganization with pg_repack.

Prerequisites

Before you begin, make sure you have:

• PostgreSQL 13 or later (most examples work on 12+; some syntax is 14+).
• psql or pgAdmin access with superuser or pg_monitor role.
• pg_repack extension installed if you plan online reorganization (covered below).
• Familiarity with postgresql.conf and the ability to reload the configuration.

MVCC and Dead Tuples

PostgreSQL never modifies a row in place. An UPDATE writes a new tuple version and marks the old one as dead. A DELETE also marks the row dead without removing it. This design allows readers to see a consistent snapshot without taking locks, but the tradeoff is physical dead tuple accumulation.

Dead tuples consume disk space, inflate the table’s physical size, and slow sequential scans because PostgreSQL must read and skip every page even if most of its rows are dead. The pg_stat_user_tables view tracks the count:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

A table with dead_pct above 10–15% is a candidate for immediate attention.

VACUUM, VACUUM FULL, and VACUUM ANALYZE

PostgreSQL ships three VACUUM variants with very different behavior:

Command	Locks table?	Returns space to OS?	Updates statistics?	Safe in production?
VACUUM	No (ShareUpdateExclusiveLock)	No — marks space reusable	No	Yes
VACUUM ANALYZE	No	No	Yes	Yes
VACUUM FULL	Yes (AccessExclusiveLock)	Yes	No	With caution

VACUUM marks dead tuples as available for reuse. The physical file does not shrink — the freed pages are added to the Free Space Map (FSM) for future inserts. This is the workhorse you should rely on.

VACUUM FULL rewrites the entire table to a new heap file, then drops the old one. It returns disk space to the operating system and removes index bloat by rebuilding all indexes, but it acquires an exclusive lock for its entire duration. On a 100 GB table, that could be 30 minutes or more of total unavailability. Never run VACUUM FULL on a busy production table without scheduling a maintenance window — or better, use pg_repack instead.

VACUUM ANALYZE combines dead-tuple cleanup with a statistics update, which the query planner uses to choose optimal execution plans. Running this after a large data load is good practice.

Autovacuum Configuration

Autovacuum runs background workers that call VACUUM and ANALYZE automatically. The key parameters live in postgresql.conf:

# Number of concurrent autovacuum workers (default: 3)
autovacuum_max_workers = 5

# How often the autovacuum launcher wakes up to check tables (default: 1min)
autovacuum_naptime = 30s

# Minimum dead tuples before a VACUUM is triggered (default: 50)
autovacuum_vacuum_threshold = 50

# Fraction of live rows that can be dead before VACUUM triggers (default: 0.2 = 20%)
autovacuum_vacuum_scale_factor = 0.05

# Minimum row changes before ANALYZE triggers (default: 50)
autovacuum_analyze_threshold = 50

# Fraction of rows changed before ANALYZE triggers (default: 0.1 = 10%)
autovacuum_analyze_scale_factor = 0.02

# I/O throttling: delay between vacuum buffer operations in milliseconds (default: 2)
autovacuum_vacuum_cost_delay = 2ms

# Maximum cost units spent before a delay (default: -1 = uses vacuum_cost_limit = 200)
autovacuum_vacuum_cost_limit = 400

Understanding the Trigger Formula

Autovacuum fires on a table when:

n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

For a 10-million-row table with the defaults:

trigger = 50 + 0.20 × 10,000,000 = 2,000,050 dead tuples

That means 20% of the table must be dead before autovacuum kicks in. For a high-write table, that is too late. Lowering autovacuum_vacuum_scale_factor to 0.01 or even 0.005 is appropriate for large, frequently-updated tables.

autovacuum_vacuum_cost_delay

This parameter throttles autovacuum to avoid starving application I/O. Each buffer autovacuum reads or dirtied costs a certain number of “cost units”. When accumulated cost hits autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay milliseconds.

On SSDs and NVMe arrays, you can safely set autovacuum_vacuum_cost_delay = 0 or 2ms and increase autovacuum_vacuum_cost_limit = 800. This makes autovacuum faster without meaningfully impacting IOPS on fast storage.

On spinning disks with competing workloads, leave cost_delay = 20ms to throttle the background work.

After changing postgresql.conf, reload (no restart required):

SELECT pg_reload_conf();

Monitoring Bloat

The pg_stat_user_tables and pg_stat_activity views are your primary monitoring tools.

Dead Tuple Dashboard Query

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Finding Running Autovacuum Workers

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

If you never see autovacuum workers here but tables have high dead tuples, autovacuum may be under-resourced (too few workers, or too aggressive cost throttling).

Estimating Table Bloat

The pgstattuple extension gives precise bloat measurements:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  relname,
  (dead_tuple_percent + free_percent) AS bloat_pct,
  pg_size_pretty(relation_size) AS table_size
FROM pgstattuple_approx('orders') AS f
JOIN pg_class c ON c.relname = 'orders';

For a quick heuristic without the extension:

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS table_size,
  pg_size_pretty(
    pg_relation_size(oid)
    - (n_live_tup * 200)  -- approximate avg tuple size
  ) AS estimated_bloat
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_relation_size(oid) DESC
LIMIT 10;

Per-Table Autovacuum Overrides

Global parameters affect every table. Large, frequently-updated tables need tighter thresholds. Apply per-table storage parameters with ALTER TABLE:

-- Trigger vacuum when 1% of rows are dead (instead of default 20%)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0
);

-- Verify the overrides
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

This is the most impactful single change you can make for a hot table. The global scale factor stays conservative for small tables, while the hot table gets aggressive vacuuming.

To reset to global defaults:

ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);

Transaction ID Wraparound Prevention

PostgreSQL uses 32-bit transaction IDs (XIDs). After approximately 2.1 billion transactions, the counter wraps around. Tuples older than the wraparound horizon could become visible again or invisible, causing data corruption. PostgreSQL guards against this by forcing an autovacuum freeze before the horizon is reached.

Checking XID Age

SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

When xid_age exceeds autovacuum_freeze_max_age (default: 200 million), autovacuum will force a freeze vacuum on that table, regardless of dead tuple count. When it reaches 2 billion, PostgreSQL shuts down with:

ERROR: database is not accepting commands to avoid wraparound data loss

Key Wraparound Parameters

# Autovacuum starts aggressive freezing when age exceeds this (default: 200000000)
autovacuum_freeze_max_age = 200000000

# Vacuum freezes tuples older than this many transactions (default: 50000000)
vacuum_freeze_min_age = 50000000

# Postgres will warn and then panic when age approaches this (default: 1000000000)
autovacuum_multixact_freeze_max_age = 400000000

Monitor the oldest unfrozen table system-wide:

SELECT
  datname,
  age(datfrozenxid) AS db_xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Alert when any database XID age exceeds 1.5 billion.

VACUUM VERBOSE Output

Run VACUUM VERBOSE to understand what happened:

VACUUM VERBOSE orders;

Sample output:

INFO:  vacuuming "public.orders"
INFO:  scanned index "orders_pkey" to remove 14523 row versions
INFO:  "orders": removed 14523 row versions in 1203 pages
INFO:  index "orders_created_at_idx" now contains 2847491 row versions in 9801 pages
INFO:  "orders": found 14523 removable, 2847491 nonremovable row versions in 11004 out of 11004 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 9821738
CPU: user: 0.42 s, system: 0.08 s, elapsed: 3.71 s

Key fields: removable is what got cleaned up, nonremovable means tuples still visible to open transactions, and oldest xmin is the oldest transaction holding a snapshot that prevents full cleanup.

If nonremovable is very high despite running VACUUM, look for long-running transactions or idle-in-transaction connections:

SELECT pid, usename, state, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start < now() - interval '5 minutes'
ORDER BY xact_duration DESC;

pg_repack: Online Table Reorganization

When VACUUM cannot reclaim physical space and VACUUM FULL is unacceptable due to locking, pg_repack is the answer. It rebuilds the table using a shadow table and triggers, then swaps them atomically — all while the original table remains fully available.

Installing pg_repack

# Ubuntu/Debian
sudo apt install postgresql-15-repack

# Or via pgxn
pgxn install pg_repack

-- In the target database
CREATE EXTENSION pg_repack;

Reorganizing a Table Online

# Repack a single bloated table
pg_repack -d mydb -t orders

# Repack all tables in a database
pg_repack -d mydb

# Repack and also rebuild all indexes
pg_repack -d mydb -t orders --no-order

# Dry run (show what would happen)
pg_repack -d mydb -t orders --dry-run

pg_repack requires the table to have a primary key or a unique NOT NULL index. It applies no exclusive lock until the final swap, which takes milliseconds.

---

pg_repack vs VACUUM FULL comparison:

Aspect	pg_repack	VACUUM FULL
Table lock during rebuild	None	AccessExclusiveLock (full duration)
Disk space required	2× table size temporarily	2× table size temporarily
Index rebuild	Yes (concurrent)	Yes (blocking)
Requires primary key	Yes	No
Production safe	Yes	Only during maintenance window

Index Bloat and REINDEX CONCURRENTLY

Indexes accumulate bloat independently of the table. B-tree indexes have an internal fill factor and do not always reclaim space from deleted entries unless a VACUUM has passed over the corresponding heap pages.

Detecting Index Bloat

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

For precise bloat measurement:

SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  round(100 - avg_leaf_density, 2) AS bloat_pct
FROM pg_stats_ext
-- use pgstattuple for exact measurements
;

Using pgstattuple:

SELECT * FROM pgstatindex('orders_created_at_idx');

The leaf_fragmentation field is the key metric — above 40% is worth addressing.

Rebuilding Indexes Without Downtime

-- Rebuild a single index concurrently
REINDEX INDEX CONCURRENTLY orders_created_at_idx;

-- Rebuild all indexes on a table concurrently (PostgreSQL 14+)
REINDEX TABLE CONCURRENTLY orders;

-- Rebuild all indexes in a schema
REINDEX SCHEMA CONCURRENTLY public;

REINDEX CONCURRENTLY builds a new index alongside the existing one without blocking reads or writes. It requires extra disk space for the duration and takes longer than a blocking reindex, but it is safe for production.

Connection Pooling and Autovacuum Interaction

PgBouncer in transaction-pooling mode creates a subtle problem: idle application connections may hold open transactions that pin the oldest xmin, preventing VACUUM from removing dead tuples even after it runs.

Check for pinned xmin:

SELECT
  client_addr,
  state,
  backend_xmin,
  now() - state_change AS state_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin;

If you see backends with very old backend_xmin values that never change, they may be PgBouncer-pooled connections that started a transaction and went idle. Mitigation options:

• Set idle_in_transaction_session_timeout = '5min' in PostgreSQL to auto-terminate stale transactions.
• Use PgBouncer’s server_idle_timeout to recycle connections.
• In session-mode pooling, this is less of an issue but resource usage is higher.

DBA Monitoring Dashboard Queries

Combine the previous queries into a single monitoring view:

-- Autovacuum health overview
SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  age(relfrozenxid)                                                         AS xid_age,
  last_autovacuum,
  last_autoanalyze,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname))      AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

-- Tables approaching wraparound danger zone
SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  2100000000 - age(relfrozenxid) AS xid_remaining
FROM pg_class
WHERE relkind = 'r'
  AND age(relfrozenxid) > 150000000
ORDER BY age(relfrozenxid) DESC;

-- Currently running autovacuum workers
SELECT
  pid,
  now() - query_start AS runtime,
  substring(query FROM 'autovacuum: .+') AS target
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

Integrate these into Grafana using the PostgreSQL data source, or run them from a monitoring script that alerts when dead_pct > 20 or xid_age > 1500000000.

Common Pitfalls

1. Setting autovacuum = off globally. Never do this on a production database. Without autovacuum, wraparound shutdown is inevitable.

2. Running VACUUM FULL during peak hours. The exclusive lock blocks everything. Schedule it during maintenance windows, or use pg_repack.

3. Ignoring the oldest xmin in VACUUM VERBOSE. If a long-running transaction or replication slot holds an old xmin, VACUUM cannot remove dead tuples regardless of settings. Check replication slots too:

SELECT slot_name, xmin, catalog_xmin, age(xmin) FROM pg_replication_slots;

4. Tuning autovacuum_max_workers too high. Each worker consumes connections and I/O. More than 6–8 workers on most systems causes contention. Tune cost_delay and cost_limit first; add workers only if the queue grows.

5. Forgetting to tune ANALYZE separately from VACUUM. Statistics staleness causes bad query plans independently of dead-tuple bloat. autovacuum_analyze_scale_factor = 0.01 is a good starting value for large tables.

6. Using pg_repack on tables with deferred triggers. pg_repack is incompatible with deferred triggers. Check with \d+ tablename in psql before running.

Summary

Healthy PostgreSQL performance depends on VACUUM keeping pace with your write load:

• Lower autovacuum_vacuum_scale_factor for large, high-write tables — the default 20% is far too loose.
• Monitor n_dead_tup and last_autovacuum continuously via pg_stat_user_tables.
• Check xid_age regularly; alert above 1.5 billion to prevent wraparound emergencies.
• Use per-table overrides (ALTER TABLE SET) instead of loosening global settings for all tables.
• Replace VACUUM FULL with pg_repack for online reorganization without downtime.
• Rebuild indexes with REINDEX CONCURRENTLY when index bloat affects query performance.
• Investigate long transactions and replication slots when VACUUM runs but dead tuples persist.
• Tune autovacuum_vacuum_cost_delay = 0 on NVMe storage to let autovacuum work at full speed.

Related Articles

• PostgreSQL Performance Tuning: Configuration and Query Optimization
• How to Monitor PostgreSQL with pg_stat_statements
• Docker Compose PostgreSQL: Setup and Best Practices
• ZFS Storage Pool Management on Linux: Complete Guide