Originally published on kuryzhev.cloud
The scenario
PostgreSQL vacuum monitoring became a priority for us after a very specific scare: a 400GB OLTP database on managed Postgres started throwing slow queries and eating disk space, even though the actual dataset hadn't grown in weeks. No new customers, no big import job, nothing in the changelog that explained it. Classic symptoms, in hindsight — dead tuples piling up faster than autovacuum could clean them.
We didn't catch it early. We caught it when someone ran a diagnostic query out of curiosity and found dead-tuple counts in the millions on a handful of hot tables. Worse, when we checked age(datfrozenxid) against autovacuum_freeze_max_age, we were closer to transaction ID wraparound than anyone was comfortable admitting out loud in a Slack thread titled "quick question."
Wraparound is the nightmare scenario nobody explains until it's almost too late: if a database's transaction ID counter wraps before old rows get frozen, Postgres refuses new writes entirely. Disk usage climbing is annoying. Wraparound is an outage. That gap between "annoying" and "outage" is exactly what proactive vacuum monitoring is supposed to close — catching bloat and freeze risk weeks before they show up in a slow-query postmortem, not during one.
Prerequisites
Before touching any settings, you need a few things in place:
-
Access: superuser or the
pg_monitorrole, plus the ability to install extensions likepgstattuple. On managed Postgres — RDS, Cloud SQL — extension installs and some parameter changes go through a parameter group, and some require a reboot or "apply immediately" flag. Plan for that lead time. -
Metrics pipeline:
postgres_exporter(or an equivalent) feeding Prometheus and Grafana, with support for custom metric queries. The default exporter metrics don't cover dead-tuple ratio or freeze age — you'll need to add those yourself, covered below. - A maintenance window: some remediation, like repacking a badly bloated table, needs a low-traffic period. Identify one before you start, because you will find at least one table that needs it.
One security note: don't hand out blanket superuser access for monitoring. Grant pgstattuple execution and read access to a dedicated read-only monitoring role. It's a smaller attack surface and it's easier to justify in an access review.
Step 1: Baseline the current bloat and autovacuum health
Before changing anything, know where you actually stand. This baseline query set is the first thing we run on any database we're asked to look at:
-- baseline_vacuum_health.sql
-- Run this first: ranks tables by bloat risk and shows last autovacuum activity
-- 1. Dead tuple ratio per table (top offenders)
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC
LIMIT 20;
-- 2. Transaction ID wraparound risk per database
SELECT
datname,
age(datfrozenxid) AS xid_age,
round(age(datfrozenxid)::numeric /
(SELECT setting::numeric FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') * 100, 1
) AS pct_of_freeze_max_age
FROM pg_database
WHERE datallowconn
ORDER BY xid_age DESC;
-- 3. Any vacuum currently in progress and its phase
SELECT
p.pid,
s.relname,
p.phase,
p.heap_blks_total,
p.heap_blks_scanned,
p.heap_blks_vacuumed
FROM pg_stat_progress_vacuum p
JOIN pg_stat_user_tables s ON s.relid = p.relid;
-- 4. Sessions that could be blocking vacuum's cleanup horizon
SELECT pid, state, xact_start, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start ASC;
Query #2 is the one that matters most and gets ignored most. Disk usage is a lagging, noisy signal — age(datfrozenxid) is the real wraparound-risk metric. We've seen teams stare at disk graphs for weeks while freeze age quietly crept past 70% of the default 200-million threshold.
Watch out: if you have tables with large text or jsonb columns, check their TOAST tables separately. TOAST bloats independently of the parent table and won't show up cleanly in the same dashboard view — we lost an afternoon once assuming a table was healthy because its main relation looked fine, while its TOAST relation was the actual problem.
Step 2: Tune autovacuum per table, not globally
The default autovacuum_vacuum_scale_factor of 0.2 means a table gets vacuumed once 20% of its rows are dead. That's fine for a 50-row config table. It's disastrous for a 50-million-row hot table, because 20% is 10 million dead tuples before autovacuum even triggers — and by then you're fighting a much bigger job with the same default cost limits.
Instead of touching global config, identify your top 5-10 tables by write churn and dead-tuple ratio (Step 1 gives you this list) and tune them individually:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_limit = 2000
);
-- Lower fillfactor on update-heavy tables to enable HOT updates
ALTER TABLE orders SET (fillfactor = 90);
Lowering fillfactor leaves free space in each page for updated rows to stay put instead of migrating and touching every index. That's a Heap-Only Tuple (HOT) update — it generates less bloat at the source and reduces index churn, which is cheaper than cleaning bloat up after the fact.
Gotcha: don't crank autovacuum_vacuum_cost_limit too aggressively across many tables at once. More aggressive vacuuming means more I/O, and on a busy primary that can worsen replication lag. Tune incrementally and watch replica lag metrics as you go — we've caused exactly this problem by being too enthusiastic with a "fix it once and for all" config push.
Also worth knowing: autovacuum_max_workers defaults to 3. On a cluster with hundreds of actively written tables, those workers become a bottleneck — tables can sit well past their scale_factor threshold simply waiting for a free worker. If Step 1's baseline keeps showing stale last_autovacuum timestamps despite reasonable per-table settings, check worker saturation before blaming your scale_factor math.
Step 3: Wire up continuous monitoring and alerts
One-time diagnostics are useful for a fire drill. They don't prevent the next one. We added custom queries to postgres_exporter for dead-tuple ratio, time since last autovacuum, and freeze age percentage — none of which are exposed by the exporter's default metric set.
With those metrics flowing into Prometheus, the alert rules are straightforward:
# prometheus_alerts_vacuum.yaml
# Custom alert rules built on postgres_exporter custom-query metrics
groups:
- name: postgres_vacuum
rules:
- alert: HighDeadTupleRatio
expr: pg_stat_user_tables_dead_pct > 15
for: 30m
labels:
severity: warning
annotations:
summary: "Table {{ $labels.relname }} dead tuple ratio > 15%"
- alert: FreezeAgeCritical
expr: pg_database_xid_age_pct_of_max > 75
for: 10m
labels:
severity: page
annotations:
summary: "{{ $labels.datname }} nearing transaction ID wraparound"
- alert: AutovacuumStalled
expr: time() - pg_stat_user_tables_last_autovacuum_timestamp > 86400
for: 1h
labels:
severity: warning
annotations:
summary: "{{ $labels.relname }} not autovacuumed in 24h"
We page on freeze age crossing 75% of autovacuum_freeze_max_age and only warn on dead-tuple ratio, because dead tuples are a performance problem while wraparound is an availability problem — the severity should reflect that gap.
Also set log_autovacuum_min_duration = 0 (or a low threshold in ms) in your Postgres config. It's the cheapest observability win in this whole setup — every autovacuum run, its duration, and how many tuples it removed shows up directly in the Postgres logs, which you can correlate against your alerts without extra tooling. Check the official PostgreSQL vacuuming documentation for the full parameter list before changing defaults on a production cluster.
Step 4: Handle the cases autovacuum can't fix alone
Some bloat problems aren't config problems. The most common one we run into is an idle-in-transaction session — an app connection that opened a transaction and never committed or rolled back. That session holds back the xmin horizon, which silently prevents dead-tuple removal no matter how well you've tuned scale factors. Query #4 from Step 1 catches these; alert on any session idle-in-transaction for more than a few minutes and kill it.
For tables that are already badly bloated, resist the urge to run VACUUM FULL on a live production table. It rewrites the entire table and takes an ACCESS EXCLUSIVE lock for the whole operation — nothing reads or writes until it finishes. We've seen this used as an "emergency fix" that turned into a longer outage than the bloat itself. Use pg_repack instead — it rebuilds the table with much lighter locking.
A manual VACUUM (VERBOSE, ANALYZE) off-hours is still sometimes the pragmatic call, especially right after a bulk delete or migration, rather than waiting for tuned autovacuum settings to catch up on their own schedule.
Common mistake we've seen more than once: disabling autovacuum entirely on a hot table after it caused a lock contention scare during business hours. It feels like a fix in the moment. It guarantees catastrophic bloat and an eventual emergency VACUUM FULL a few months later — usually at a worse time than the original scare.
Verify and test
Don't call this done until you've confirmed it actually works. Re-run the Step 1 baseline queries a few days after applying per-table tuning and check that dead-tuple ratios on your tuned tables are trending down, not just stable.
Trigger a synthetic alert by temporarily lowering a threshold — drop HighDeadTupleRatio to 1% for a few minutes — and confirm it actually fires in Grafana and pages whoever it's supposed to page. An alert rule that's never fired in staging is an alert rule you can't trust in production.
Finally, confirm last_autovacuum timestamps on your key tables are recent, and that pg_total_relation_size on any repacked table has actually shrunk. Disk usage stabilizing — or dropping — after remediation is the clearest sign the whole loop is working, not just the dashboards.
PostgreSQL vacuum monitoring isn't a setting you configure once and forget — it's an ongoing feedback loop between your workload and autovacuum's settings, and workloads change constantly as tables grow, write patterns shift, and new features add hot paths you didn't tune for. Skipping that feedback loop doesn't save time; it just moves the cost from a boring dashboard check to an actual outage, measured in downtime rather than gigabytes. We treat these alerts the same way we treat disk-space or replication-lag alerts: routine, boring, and exactly the reason nothing dramatic happens at 3am. If you're building out a broader observability stack, our DevOps_DayS archive has more on wiring Prometheus and Grafana into production databases the same way.
Top comments (0)