Stage 3 · Build

PostgreSQL Operations

VACUUM & Table Bloat

Dead tuple accumulation, autovacuum tuning, and emergency bloat remediation.

7 min readDatabases for SREBuild

MVCC and Dead Tuples

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to allow concurrent reads and writes without locking. When a row is updated, PostgreSQL creates a new version and marks the old version as dead. Dead tuples consume space until they are removed by VACUUM.

See dead tuples in action


-- Check dead tuple count
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Force dead tuples with repeated updates
CREATE TABLE vacuum_demo (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO vacuum_demo (data) SELECT md5(random()::text) FROM generate_series(1, 10000);

-- Update all rows 10 times — creates 10x dead tuples
UPDATE vacuum_demo SET data = md5(data);
UPDATE vacuum_demo SET data = md5(data);
UPDATE vacuum_demo SET data = md5(data);

After multiple updates, the table contains 10x more dead tuples than live tuples. This bloat wastes disk space and slows sequential scans because PostgreSQL must skip dead tuples.

Autovacuum Basics

Autovacuum automatically runs VACUUM and ANALYZE when a table accumulates enough dead tuples. VACUUM reclaims space from dead tuples. ANALYZE updates table statistics for the query planner.

Autovacuum triggers


-- Default trigger: dead tuples > (vacuum_scale_factor * reltuples) + vacuum_threshold
-- For a 1M row table with default scale_factor=0.2:
-- Triggers at 200,000 dead tuples + threshold (50) = ~200,050

-- Check autovacuum status
SELECT
    relname,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE last_autovacuum IS NOT NULL
ORDER BY last_autovacuum DESC
LIMIT 10;

-- Manual vacuum (does not block reads or writes)
VACUUM VERBOSE orders;
VACUUM ANALYZE orders;  -- vacuum + update statistics

VACUUM does not return disk space to the OS immediately. It marks dead tuple space as reusable for future inserts. Only VACUUM FULL returns disk space, but it locks the table exclusively.

Measuring Table Bloat

Bloat measurement queries


-- Table bloat estimate using pg_stat_user_tables
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Index bloat check
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Overall database size
SELECT pg_size_pretty(pg_database_size(current_database()));

Monitor dead_pct to identify tables that need attention. A dead_pct above 10% on large tables usually indicates autovacuum is not keeping up.

Tuning Autovacuum

Per-table autovacuum tuning


-- Aggressive autovacuum for high-churn tables
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 0,
    autovacuum_vacuum_cost_limit = 1000
);

-- Conservative autovacuum for rarely-changed tables
ALTER TABLE config SET (
    autovacuum_vacuum_scale_factor = 0.5,
    autovacuum_analyze_scale_factor = 0.25
);

-- Global settings
ALTER SYSTEM SET autovacuum_max_workers = 4;
ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

autovacuum_vacuum_cost_delay = 0 removes I/O throttling for the table — vacuum runs at full speed. Use this for tables where bloat is actively causing performance issues.

Scale factor tuning prevents bloat

For a 100M row table, the default scale_factor of 0.2 means waiting for 20M dead tuples. Setting it to 0.02 triggers at 2M dead tuples. This keeps the table much healthier at the cost of more frequent vacuum runs.

Emergency Bloat Remediation

pg_repack for online bloat removal


-- Install pg_repack extension
CREATE EXTENSION pg_repack;

-- Check bloat
SELECT * FROM pg_repack.index_stats;

-- Online table bloat removal (no exclusive lock)
pg_repack --no-superuser --no-kill-backend --table orders mydb

-- With index rebuild
pg_repack --no-superuser --no-kill-backend \
  --table orders --only-indexes mydb

pg_repack rebuilds tables without taking an exclusive lock. It creates a new table, copies data, swaps tables, and drops the old one. This is the safe way to remove bloat from production tables.

VACUUM FULL blocks all operations

VACUUM FULL takes an exclusive lock on the table — no reads or writes are possible. Only use it during maintenance windows. For online bloat removal, use pg_repack or the expand/contract pattern.

Index Bloat

Rebuilding bloated indexes


-- Check index bloat (from pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    indexrelid::regclass AS index_name,
    ROUND(avg_leaf_density::numeric, 2) AS leaf_density_pct,
    ROUND(leaf_fragmentation::numeric, 2) AS fragmentation_pct
FROM pgstatindex(indexrelid::regclass);

-- Rebuild bloated index (non-blocking)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Rebuild all indexes on a table concurrently
REINDEX TABLE CONCURRENTLY orders;

REINDEX CONCURRENTLY rebuilds the index without blocking reads or writes. It creates a new index alongside the old one, then swaps them. Available since PostgreSQL 12.

Monitor dead tuples weekly

Set up a weekly query on pg_stat_user_tables to alert when dead_pct exceeds 10% on tables larger than 1GB. This catches bloat before it impacts performance.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.