PostgreSQL Table Bloat Maintenance: When and How to Vacuum

Quick Answer

• Conclusion: PostgreSQL table bloat from dead tuples is inevitable in write-heavy OLTP systems. Manual vacuum maintenance is required when autovacuum cannot keep up, typically when dead tuple ratio exceeds 20% or index bloat is visible.
• First checks: Query pg_stat_user_tables for n_dead_tup vs n_live_tup ratio, and use pgstattuple extension for precise bloat percentage on suspect tables.
• Minimal fix: Run VACUUM VERBOSE ANALYZE target_table; in autocommit mode as a superuser or user with pg_maintain role. For severe bloat, use pg_repack instead of VACUUM FULL to avoid long exclusive locks.
• Environment boundary: Applies to PostgreSQL 12+ with autovacuum enabled. For 7x24 production systems, prefer pg_repack over VACUUM FULL to minimize downtime.

What Problem It Solves

PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture creates dead tuples when rows are updated or deleted. These dead tuples occupy disk space and degrade query performance because the database must scan through them. Autovacuum runs automatically but may fall behind under high write loads, especially on large tables with frequent updates.

Table bloat manifests as:

• Disk usage growing faster than row count
• Sequential scans becoming slower over time
• Index bloat reducing query performance
• Autovacuum workers constantly running without catching up

Root Cause Analysis

The root cause is a mismatch between dead tuple generation rate and autovacuum's ability to reclaim space. Key factors:

• Autovacuum threshold: Triggered when pg_stat_user_tables.n_dead_tup exceeds autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * reltuples). Default scale factor (0.2) means 20% of rows must be dead before autovacuum acts.
• High write throughput: Tables with thousands of updates per second can generate dead tuples faster than autovacuum can process them.
• Long-running transactions: Hold back vacuum_defer_cleanup_age, preventing dead tuple cleanup.
• Index bloat: Autovacuum does not aggressively reclaim index space; indexes can grow disproportionately.

Minimal Working Configuration

Manual Vacuum Command

SQL
-- Run in autocommit mode (not inside a transaction block)
VACUUM VERBOSE ANALYZE public.your_table;

Parameters explained:

• VERBOSE: Reports progress and dead tuple counts
• ANALYZE: Updates query planner statistics (recommended)
• FULL: Avoid unless necessary—acquires exclusive lock

Check Bloat Before Acting

SQL
-- Requires pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('public.your_table');

-- Or use pg_stat_user_tables for quick estimate
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

Automated Maintenance Script (Conceptual)

PYTHON
import psycopg2
import os

# Never hardcode credentials; use environment variables
conn = psycopg2.connect(
    dbname=os.environ['PGDATABASE'],
    user=os.environ['PGUSER'],
    host=os.environ['PGHOST'],
    password=os.environ['PGPASSWORD']
)
conn.autocommit = True  # Critical: VACUUM cannot run in a transaction

cur = conn.cursor()
cur.execute("SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE n_dead_tup > 10000;")
for row in cur.fetchall():
    dead_pct = 100.0 * row[1] / (row[1] + row[2]) if (row[1] + row[2]) > 0 else 0
    if dead_pct > 20:
        cur.execute(f"VACUUM VERBOSE ANALYZE {row[0]};")
        print(f"Vacuumed {row[0]}: {dead_pct:.1f}% dead tuples")

Common Errors and Fixes

Production Notes and Security Checks

Critical Limitations

1. Concurrency: Multiple VACUUM processes on the same table will block each other. Schedule maintenance jobs to run sequentially.
2. Locking: VACUUM FULL acquires ACCESS EXCLUSIVE lock, blocking all reads and writes. Never run it during business hours. Prefer pg_repack which only locks briefly during table swap.
3. Permissions: VACUUM requires pg_maintain role or superuser. Manage connection credentials via environment variables or a secrets manager—never hardcode database URLs.
4. WAL amplification: Frequent VACUUM increases WAL generation. Monitor disk space on the WAL directory.
5. Autovacuum tuning: Before building custom maintenance, try tuning autovacuum_vacuum_scale_factor to 0.01 (1%) for hot tables, and autovacuum_vacuum_cost_limit higher to allow faster cleanup.

When to Use pg_repack Instead

For production tables that cannot tolerate downtime:

• VACUUM FULL → Long exclusive lock (minutes to hours)
• pg_repack → Brief exclusive lock (milliseconds during table swap)

Install pg_repack extension and run:

BASH
pg_repack --host your_host --dbname your_db --table public.your_table

FAQ

Q: How do I determine if my PostgreSQL table needs VACUUM?

A: Query pg_stat_user_tables for the ratio of n_dead_tup to n_live_tup. When dead tuples exceed 20% of total tuples, or when autovacuum is not keeping up (check last_autovacuum timestamp), manual intervention is warranted. For precise bloat measurement, use the pgstattuple extension.

Q: Which is safer in production: VACUUM FULL or pg_repack?

A: pg_repack is significantly safer. It rebuilds the table in the background using triggers and only acquires an exclusive lock during the final table file swap (typically milliseconds). VACUUM FULL holds an exclusive lock for the entire operation, blocking all reads and writes. For 7x24 production systems, always prefer pg_repack.

Q: Autovacuum is already running—why do I still need manual maintenance?

A: Autovacuum triggers based on thresholds and may fall behind under sustained high write loads. It also does not aggressively reclaim index bloat. Manual maintenance allows you to target specific hot tables with more aggressive settings, and tools like pg_repack can recover index space that autovacuum leaves behind.