Postgres 膨胀与 autovacuum:2026 调优指南

Postgres tables grow faster than the row count would suggest. Indexes lose half their efficiency every few months. Autovacuum can’t keep up. Most teams find out when latency drift becomes pages, not before. This is a tuning guide, not theory — every parameter listed has a recommended value and the reasoning behind it.

How autovacuum actually works (a working model)

Postgres’s MVCC keeps old row versions. When you UPDATE a row, the old version becomes a dead tuple. Autovacuum scans tables, removes dead tuples, returns space to the free-space map. Two key thresholds trigger it per table:

# A table is autovacuumed when:
n_dead_tup > autovacuum_vacuum_threshold
              + autovacuum_vacuum_scale_factor * reltuples

# Defaults: 50 + 0.2 * reltuples
# A 100M-row table needs 20M dead tuples before autovacuum starts. That's a lot.

The default scale factor of 0.2 is fine for small tables, catastrophic for large ones. Lowering it on big tables is the single most-impactful tuning change most teams can make.

Find your most-bloated tables

SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 25;

Anything with dead_pct > 20% is a problem. Anything with last_autovacuum > 7 days ago and active writes is a bigger problem.

The 7 parameters that matter

Index bloat is its own beast

Vacuum doesn’t fix index bloat. Indexes accumulate empty pages from deleted/updated rows; Postgres marks pages reusable but doesn’t reclaim space.

-- Find biggest indexes (the ones that benefit most from REINDEX)
SELECT schemaname || '.' || indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Online reindex (Pg 12+)
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Already in trouble: pg_repack and VACUUM FULL

• pg_repack is the right tool 95% of the time. Online (no exclusive lock), creates a duplicate, swaps. Plan for 2× the table’s current size in temp disk.
• VACUUM FULL takes an AccessExclusiveLock for the duration. Use only in maintenance windows on tables you don’t need read-available.
• CLUSTER is VACUUM FULL plus reordering by an index. Same lock cost.

What to monitor (alert thresholds)

• Per-table dead_tup_pct trending; alert at > 25% sustained 24h.
• autovacuum_count over rolling window; sudden drop on a busy table = autovacuum stuck.
• maintenance_work_mem utilization (rough proxy: current_setting('maintenance_work_mem') vs vacuum scan time).
• pg_stat_progress_vacuum to see live vacuum progress.

FAQ