VACUUM Advisor — Bloat, Autovacuum Lag, Freeze Age

Three views, in order of how often they save you: bloated tables, autovacuum lag, and freeze age.

Bloated tables

Tables where dead tuple percentage is high. Accurate measurement requires pgstattuple. Without it, MonPG estimates from pg_stat_user_tables — reasonable but noisier, especially on tables with bursty deletes.

Each row shows the table name, total size, dead-tuple percentage, last autovacuum timestamp, and a recommended action. The action depends on bloat level: regular VACUUM below 20% dead, VACUUM FULL above 30% bloat (with the warning that it takes an exclusive lock), or pg_repack as the no-lock alternative if you have it installed.

Autovacuum lag

Tables where the dead-tuple threshold is met but autovacuum hasn't actually run. Often means per-table tuning is needed, the autovacuum cost limits are too tight for your workload, or autovacuum is being blocked by another job. The page suggests per-table overrides:

ALTER TABLE big_busy_table SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- trigger at 5% dead (default 20%)
  autovacuum_vacuum_cost_limit = 2000      -- more aggressive cleanup
);

XID wraparound and freeze age

Every table has a "transaction age" — how many transactions it's seen since its oldest row was frozen. PostgreSQL freezes aggressively as age approaches 200M (the default autovacuum_freeze_max_age). Past 2 billion transactions, the cluster shuts down to prevent data loss. This is the worst-case Postgres failure mode.

MonPG flags any table with age over 150M and suggests a proactive VACUUM FREEZE. Cheap insurance — you'd rather discover this on the dashboard at 2pm than from an outage at 3am.

Safety

VACUUM FULL takes an ACCESS EXCLUSIVE lock — no reads, no writes, for the duration. On a 100GB table this can take 30+ minutes. In production, use pg_repack instead. pg_repack needs the extension installed (we don't auto-install it; manual). It works concurrently with reads and writes, which is the whole point of using it.