Database Vacuum and Maintenance

Keep PostgreSQL and other databases healthy with proper maintenance. Covers autovacuum tuning, bloat detection, index maintenance, statistics collection, and the patterns that prevent database performance degradation over time.

Database Vacuum and Maintenance is the ongoing process of keeping a database performant, compact, and resilient by reorganizing data, reclaiming storage, and optimizing query execution. It matters most when a database grows beyond its initial size, especially under frequent insert, update, and delete workloads, where bloat and fragmentation degrade performance silently over time. Without regular vacuum and maintenance, queries slow, indexes grow inefficient, and storage costs rise with unused space.

Core Tasks

Reclaiming Space with Vacuum

Vacuum is the primary operation for reclaiming space from deleted or updated rows. When a row is updated, PostgreSQL creates a new version (tupled) and marks the old version as obsolete. Over time, this leads to *dead tuples*—rows no longer accessible. Vacuum identifies and removes these, reducing storage and improving scan performance.

The basic command is:

VACUUM;

This runs a full vacuum in the current database, scanning all tables and cleaning up dead tuples. For larger databases, use:

VACUUM (VERBOSE, ANALYZE, FREEZE) my_table;

For optimal performance, run VACUUM during off-peak hours:

-- Run vacuum with full parallelism and logging
VACUUM (VERBOSE, ANALYZE, FREEZE, PARALLEL 4, CONCURRENTLY) ALL TABLES;

CONCURRENTLY allows the table to remain available during vacuum, avoiding table locks. However, it incurs a significant cost: each concurrent vacuum creates a new pg_toast segment and requires two passes.

When CONCURRENTLY is used, the table remains accessible, but the following conditions apply:

SELECT 
    pid, 
    phase, 
    relid, 
    heap_blks_scanned, 
    heap_blks_total, 
    index_blks_scanned, 
    index_blks_total
FROM pg_stat_progress_vacuum
WHERE phase = 'Vacuuming';

If the table is heavily updated during a concurrent vacuum, the process may fail to complete due to a *concurrent vacuum conflict*. This occurs when a new transaction attempts to update a row that is currently being vacuumed, leading to a VACUUM CONCURRENTLY conflict. The error appears in the server log as:

ERROR:  concurrent vacuum failed to complete on relation "my_table"
DETAIL:  The concurrent vacuum process could not finalize due to conflicts with concurrent updates.

To mitigate, schedule multiple vacuum jobs with staggered start times:


0 2 * * * /usr/bin/psql -c "VACUUM (VERBOSE, ANALYZE, FREEZE, CONCURRENTLY) my_large_table;"
0 3 * * * /usr/bin/psql -c "VACUUM (VERBOSE, ANALYZE, FREEZE) my_medium_table;"
0 4 * * * /usr/bin/psql -c "VACUUM (VERBOSE, ANALYZE) my_small_table;"

Managing Automatic Vacuum with Configuration

Automatic vacuum is driven by two key configuration parameters in postgresql.conf:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 5000

For example, with a table of 100,000 rows:

Additionally, autovacuum_vacuum_cost_delay controls how long the system waits between I/O operations during vacuum:

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 2000

The cost model uses a budgeting system: each I/O operation costs 10 units. The cost_delay is the time spent sleeping between operations, and cost_limit is the total budget before a new vacuum step is triggered.

If cost_limit is exceeded, the vacuum process pauses, allowing other queries to run. If cost_delay is too high, the vacuum becomes too slow and delays other work.

Index Rebuilding and Maintenance

Indexes grow over time as data is inserted and updated. B-tree indexes become unbalanced, and leaf pages may be sparsely filled. Rebuilding indexes reduces fragmentation and improves seek performance.

Use REINDEX to rebuild an entire index:

REINDEX INDEX my_index;

To rebuild all indexes in a schema:

REINDEX SCHEMA public;

For large databases, rebuild indexes in parallel:

-- Rebuild all indexes with concurrent execution
REINDEX (CONCURRENTLY, PARALLEL 8) INDEX my_index;

CONCURRENTLY allows reads to continue during index rebuild, but it requires two passes:

  1. Create a new index.
  2. Atomically swap the new index into place.

This process can fail if a new transaction modifies the table during the swap. The error:

ERROR:  concurrent index creation failed to complete
DETAIL:  The index could not be swapped due to concurrent modifications during the final step.

To monitor index rebuild progress:

SELECT 
    indexrelid, 
    schemaname, 
    tablename, 
    indexname, 
    status, 
    phase
FROM pg_stat_progress_create_index
WHERE phase = 'Building';

For performance-critical systems, schedule index rebuilds during low-traffic windows:

# Rebuild all indexes in public schema every Sunday
0 3 * * 0 /usr/bin/psql -c "REINDEX SCHEMA public;"

Analyze for Query Plan Optimization

ANALYZE collects statistics about the distribution of data in tables and indexes. These statistics guide the query planner to choose optimal execution plans.

Run ANALYZE after major data loads:

ANALYZE;

Or target specific tables:

ANALYZE my_table;

To update statistics across all tables in a database:

ANALYZE (VERBOSE, ANALYZE) ALL TABLES;

ANALYZE performs a full table scan and samples data to compute:

The statistics are stored in pg_statistic and used by the planner to estimate row counts, cost, and selectivity.

If ANALYZE is not run regularly, query plans degrade over time. A query might use a hash join when a nested loop would be faster, or a sequential scan instead of an index scan.

To ensure timely analysis, combine it with VACUUM:

VACUUM (ANALYZE, VERBOSE) my_table;

This is more efficient than running VACUUM and ANALYZE separately.

Managing Transaction ID Wraparound

PostgreSQL uses 32-bit transaction IDs (XIDs), which wrap around every 2^32 ≈ 4 billion transactions. If XIDs are not managed, the system can experience *wraparound*, where older transactions become invisible.

To prevent wraparound, use VACUUM FREEZE:

VACUUM FREEZE;

This freezes all transaction IDs in the table, marking them as "frozen" and ensuring they remain visible indefinitely.

The process is triggered automatically by the autovacuum daemon when the vacuum_freeze_min_age and vacuum_freeze_table_age thresholds are met.

Set in postgresql.conf:

vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000

When a table’s age reaches freeze_table_age, a FREEZE phase is added to the next VACUUM. The system logs wraparound warnings:

WARNING:  transaction ID wraparound detected in database "mydb"
DETAIL:  Transaction ID 1234567890 is near wraparound (1234567890 > 1234567890).
HINT:  Run VACUUM FREEZE on frequently updated tables.

Failure to run FREEZE leads to a full table scan during VACUUM, as each row must be scanned and updated to mark its XID as frozen.

Monitoring and Diagnostics

Use pg_stat_user_tables to monitor table health:

SELECT 
    schemaname, 
    tablename, 
    n_dead_tup, 
    n_live_tup, 
    n_mod_since_analyze, 
    last_vacuum, 
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Tables with high n_dead_tup are candidates for immediate vacuum.

To track vacuum performance over time, query pg_stat_progress_vacuum:

-- Show all active vacuums and their progress
SELECT 
    pid,
    relid::regclass AS table_name,
    heap_blks_scanned,
    heap_blks_total,
    index_blks_scanned,
    index_blks_total,
    phase
FROM pg_stat_progress_vacuum
ORDER BY phase, heap_blks_scanned;

Also monitor pg_stat_progress_create_index for index rebuilds.

Failure Modes and Common Pitfalls

Automation and Scheduling

Use systemd to schedule maintenance tasks:

# /etc/systemd/system/db-maintenance.service
[Unit]
Description=Database Maintenance Tasks
After=network.target

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/psql -c "VACUUM (VERBOSE, ANALYZE, FREEZE, CONCURRENTLY) ALL TABLES;"
ExecStart=/usr/bin/psql -c "REINDEX (CONCURRENTLY, PARALLEL 8) SCHEMA public;"
ExecStart=/usr/bin/psql -c "ANALYZE (VERBOSE, ANALYZE) ALL TABLES;"
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target

Enable and start:

sudo systemctl enable db-maintenance.service
sudo systemctl start db-maintenance.service

Schedule the service weekly:

# Run maintenance every Sunday at 2 AM
0 2 * * 0 systemctl start db-maintenance.service

Summary of Key Commands

TaskCommand
Basic vacuumVACUUM;
Vacuum with analysis and freezeVACUUM (ANALYZE, FREEZE) my_table;
Concurrent vacuumVACUUM (CONCURRENTLY) my_table;
ReindexREINDEX INDEX my_index;
Reindex with concurrencyREINDEX (CONCURRENTLY) INDEX my_index;
Analyze all tablesANALYZE ALL TABLES;
Analyze with statisticsANALYZE (VERBOSE, ANALYZE) my_table;
Freeze all XIDsVACUUM FREEZE;
Monitor progressSELECT * FROM pg_stat_progress_vacuum;

These commands form the foundation of database maintenance. Together, they ensure that the database remains compact, responsive, and resilient under evolving workloads.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.