诊断膨胀与清理策略

PostgreSQL uses MVCC: updates and deletes leave behind old row versions called dead tuples. Until they are cleaned up, they occupy space and slow scans. This wasted space is bloat.

VACUUM reclaims dead tuples for reuse and updates visibility information. It usually does not return space to the OS; VACUUM FULL does but rewrites the whole table and takes a strong lock.

Inspect dead tuple counts per table from the statistics view.

Autovacuum runs in the background, triggering when dead tuples exceed a threshold based on table size and the scale factor setting.

For large, frequently updated tables, the default 20% scale factor is too lazy. Lower it per table so vacuum runs more often on less garbage.

Autovacuum throttles itself with cost limits to avoid I/O storms. On modern hardware you can raise autovacuum_vacuum_cost_limit so vacuum finishes faster.

VACUUM also prevents transaction ID wraparound, a catastrophic condition. Aggressive anti-wraparound vacuums are non-negotiable and cannot be skipped.

Indexes bloat too. REINDEX CONCURRENTLY rebuilds an index without blocking writes, restoring its compactness.

Heap-Only Tuple updates avoid index churn when no indexed column changes. Leaving some free space via a lower fillfactor helps HOT updates and reduces bloat.

VACUUM reclaims space; ANALYZE refreshes the planner statistics. Autovacuum does both, but after big bulk loads run ANALYZE manually for fresh plans.

Alert on rising n_dead_tup, growing table size with stable row counts, and high age(datfrozenxid). These early signals let you tune before queries slow down.

A large, hot table keeps growing despite stable row counts. What is the likely cause and fix?

You learned to diagnose bloat from MVCC dead tuples, measure it with pg_stat_user_tables, tune autovacuum per table, guard against ID wraparound, and use REINDEX CONCURRENTLY and fillfactor to keep performance high.