9 autovacuum tuning alerts and thresholds that work

Vacuum keeps PostgreSQL healthy, but alerts around it are often noisy or late. The fix is to monitor the few signals that actually predict trouble, wire thresholds to workload, and include enough context in the page to act immediately. The nine checks below fold into four alert groups you can deploy today without spamming on-call.

Core bloat and staleness signals

1) Dead tuple ratio and bytes on hot tables. Alert when dead tuples exceed 20 percent of live rows or 100 MB on a table, whichever is larger. Tie to write rate so OLTP tables trip sooner than archival ones. Compute both signals from pg_stat_user_tables:

-- per-table dead tuple ratio and bytes
SELECT
  relid::regclass AS table,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
  pg_total_relation_size(relid) AS total_bytes,
  (n_dead_tup * avg_width)::bigint AS dead_bytes_est
FROM
  pg_stat_user_tables st
JOIN (
  SELECT relid, avg_width
  FROM pg_stats
  GROUP BY relid, avg_width
) s USING (relid);

Weight by write rate using recent UPDATE+DELETE volume. Track deltas over 5 to 15 minutes and gate the alert if the table’s update+delete rate is in the top quartile of your fleet. That cuts noise from temporarily busy hours:

-- recent mod volume per table
SELECT relid::regclass AS table,
       (tup_upd + tup_del) - LAG(tup_upd + tup_del) OVER w AS mods_5m
FROM pg_stat_user_tables
WINDOW w AS (ORDER BY now() RANGE BETWEEN '5 minutes' PRECEDING AND CURRENT ROW);

2) Last autovacuum staleness vs modification volume. Use pg_stat_all_tables.last_autovacuum. Alert if:

• Time since last_autovacuum > 4x the expected interval for the table’s class, or
• Dead tuples are growing while last_autovacuum is stale.

Expected intervals should differ by schema. For example, customer-facing tables: 15–30 minutes. Reporting tables: 2–4 hours. You can enforce more aggressive cleanup on specific relations with per-table storage parameters:

ALTER TABLE public.orders
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_vacuum_threshold = 5000,
       autovacuum_analyze_scale_factor = 0.05);

3) Freeze age as a percentage of autovacuum_freeze_max_age. Wraparound risk must page before it becomes urgent. Track age(relfrozenxid) by relation and alert at two levels:

• Warning at 70 percent of autovacuum_freeze_max_age.
• Critical at 95 percent with a runbook link.

SELECT
  c.oid::regclass AS table,
  age(c.relfrozenxid) AS freeze_age,
  current_setting('autovacuum_freeze_max_age')::bigint AS freeze_limit,
  round(100.0 * age(c.relfrozenxid) /
        current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Include table size and an ETA to freeze. Large relations need longer maintenance windows. ETA can be a simple size divided by observed scan rate from pg_stat_progress_vacuum.

Blockers and capacity that slow vacuum

4) Long-running transactions that block cleanup. Transactions older than 30 minutes on OLTP or 2 hours on batch systems hold back the visibility horizon. That grows bloat and can starve replicas. Alert with the blocking PID, database, and query to speed triage:

SELECT pid, usename, datname, state, xact_start, now()-xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '30 minutes'
ORDER BY age DESC;

If you already have replication lag alerts, link them to this signal so on-call sees cause and effect on the same page.

5) Autovacuum worker saturation and backlog. Watch active workers versus max_autovacuum_workers. Page if workers are pegged for more than 5 minutes and at least 3x as many tables are waiting as there are workers. Compute backlog from pg_stat_all_tables where n_dead_tup exceeds your autovacuum_vacuum_threshold + scale_factor * n_live_tup, and last_autovacuum is stale. Include a drain-time estimate: backlog_count divided by completions_per_minute from the last hour.

6) Vacuum progress throughput and I/O headroom. Use pg_stat_progress_vacuum to measure scan rate. Alert if heap or index passes fall below a floor for your storage class and host I/O wait is high. On SSD, a sustained rate below 5 MB per second with elevated I/O wait is a good starting tripwire. Add node context so you only page when the host cannot give vacuum the bandwidth it needs.

-- rough heap scan throughput in MB/s
SELECT pid, relid::regclass AS table,
       (heap_blks_scanned * current_setting('block_size')::int)
         / GREATEST(EXTRACT(EPOCH FROM now()-phase_start), 1)
         / 1024 / 1024 AS mb_per_s
FROM pg_stat_progress_vacuum;

If throughput is low and the host is busy, consider temporarily raising autovacuum_vacuum_cost_limit and lowering autovacuum_vacuum_cost_delay on that relation, then revert when pressure clears.

Index and disk safety

7) Index bloat thresholds with safe repair. Set a warning at 30 percent index bloat or 1 GB of waste and a critical at 50 percent or 5 GB. Your alert should paste a safe playbook: prefer REINDEX CONCURRENTLY for production, or VACUUM (FULL) only during a maintenance window because it blocks writes. For extreme cases, plan a rolling reindex or use pg_repack to avoid long locks.

-- example repair
REINDEX INDEX CONCURRENTLY public.orders_status_idx;
-- or during a window
VACUUM (FULL, VERBOSE, ANALYZE) public.orders;

Measure index bloat with your chosen method and trend it, not just snapshots. A single high-water sample without growth is rarely urgent.

8) Bloat growth rate and disk saturation forecast. Static snapshots miss the slope. Alert when table or index bloat grows faster than 10 percent per day or when current growth will fill the data volume within 7 days. Feed the model with deltas of n_dead_tup, relation size, WAL volume, and host free space. Predictive monitoring should emit a lead time and the few relations driving the curve so you can act before the partition is full.

Host-level early warnings with Foreseer

Vacuum quality depends on the host. Foreseer correlates CPU, I/O, memory, and PostgreSQL internals to show when contention will drag vacuum throughput and how wide the impact might be across services on that VM. Pages include the symptom, likely cause, impact, and precise steps to fix. Insights auto-resolve when conditions clear, so noise stays low.

Install the single-line agent on the VM. Foreseer auto-discovers services like Elasticsearch, Redis, Logstash, and disk devices, and detects cloud metadata from AWS, GCP, or Azure during onboarding. From one install you also get Elasticsearch cluster monitoring with a cluster overview and per-node drill-downs plus time-ranged, aggregation-aware charts. For Redis, the same trend models forecast eviction pressure to warn before memory spikes turn into OOM. These host signals often explain why autovacuum lags at the database layer.

Runbooks and implementation details

Good alerts are only half the job. For each threshold, add a short runbook with owner, commands, and success checks. Examples:

• Dead tuples too high: confirm with pg_stat_user_tables, run manual VACUUM on the relation if safe, then lower autovacuum_vacuum_scale_factor for that table.
• Freeze age critical: schedule VACUUM FREEZE on the relation during a window, review long-running transactions, and check replicas for replay lag.
• Worker saturation: raise max_autovacuum_workers by 1–2 on the primary or split hot tables across schemas and storage parameters.
• Throughput low: verify host I/O and CPU, then temporarily raise autovacuum_vacuum_cost_limit on affected tables.
• Index bloat high: queue REINDEX CONCURRENTLY, verify index usage stats, and consider adding a fillfactor for write-heavy indexes.

To speed internal how-tos, your team can record a quick screen capture or generate a narrated clip with a Text to TikTok video app that turns text or a URL into on-brand, faceless short-form videos with scripts, visuals, voiceover, and MP4 export. Pair those short videos with your monitoring pages so the on-call can execute a fix in minutes.

Putting it together

Start with dead tuple ratio, autovacuum staleness, and freeze age to cover safety. Add long-transaction and worker-saturation alerts to catch stalls. Use progress throughput and index bloat to keep runtime and storage in line. Finish with growth forecasts against disk so you can plan work during business hours. Foreseer’s cross-metric correlation helps because host pressure often explains why vacuum lags. It keeps service passwords local on the VM, supports role-based access control with Admin, Project Manager, and Viewer roles, and sends email invites with seat limits so you can share access safely. If you already monitor replicas, keep your PostgreSQL replication lag alerts, but link them to the autovacuum signals above so root cause is obvious.

Key takeaways

• Alert on dead tuple ratio and bytes, staleness vs write rate, and freeze age to avoid wraparound risk.
• Catch real blockers with long-transaction and worker-saturation signals, and include drain-time estimates.
• Watch progress throughput against I/O and CPU headroom so you only page when the node is the bottleneck.
• Set index bloat thresholds and use safe repair methods like REINDEX CONCURRENTLY.
• Forecast bloat growth against disk capacity. Foreseer provides early warnings and clear remediation while monitoring self-hosted services.