The Problem
PostgreSQL VACUUM monitoring and bloat prevention in production is the kind of operational discipline that separates a database that degrades silently over months from one that stays predictable under sustained write load. The mechanism behind this degradation is well-understood: PostgreSQL’s MVCC model never overwrites rows in place. Every UPDATE creates a new row version, and every DELETE marks the old one as dead. Those dead tuples stay on disk, occupying pages, until VACUUM reclaims them. When VACUUM falls behind — whether because autovacuum is misconfigured, a long-running transaction is blocking cleanup, or write throughput simply exceeds the default thresholds — tables bloat.
The consequences compound quickly. Queries that previously fit their working set into shared_buffers now scan pages filled mostly with dead rows. Sequential scans grow longer. Index scans hit more heap fetches. Write amplification increases as new rows are forced onto fresh pages rather than reusing reclaimed space. On high-churn tables like order queues, event logs, or session stores, the dead tuple count can reach tens of millions within hours of a vacuum disruption.
What makes this particularly dangerous in production is that the degradation is gradual. There is rarely a single moment of failure — just a slow widening of p95 query latency that gets attributed to application changes, traffic growth, or infrastructure noise before someone finally queries pg_stat_user_tables and finds 40 million dead tuples on a core transactional table.
Approach
The strategy here has three layers that work together rather than independently. First, establish visibility: know the dead tuple counts, bloat ratios, and last vacuum timestamps for every significant table. Second, tune autovacuum at the table level rather than relying on global defaults that were designed for moderate workloads, not high-churn production schemas. Third, wire Prometheus alerts so that when autovacuum falls behind or a blocking transaction stalls cleanup, the team knows within minutes rather than days.
This approach deliberately avoids VACUUM FULL as a routine operation. A full vacuum takes an exclusive lock on the table, which is acceptable in a maintenance window but catastrophic if triggered during business hours on a table receiving thousands of writes per minute. Instead, we tune autovacuum aggressively enough that manual intervention becomes rare, and when it is needed, we use VACUUM VERBOSE — which runs without an exclusive lock and provides detailed per-page statistics.
The postgres_exporter exposes pg_stat_user_tables metrics directly on port 9187, giving Prometheus access to dead tuple counts, live tuple counts, and last autovacuum timestamps per table. Combined with well-calibrated alert thresholds, this creates an early warning system that fires well before bloat becomes a performance incident.
Querying Bloat and Dead Tuple Accumulation
Before tuning anything, you need a clear picture of the current state. The most direct query pulls dead tuple counts, live tuple counts, and the timestamp of the last autovacuum run from pg_stat_user_tables. Run this inside psql connected to the target database:
SELECT
relname,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;The dead_ratio_pct column is the number to watch. A ratio above 10–15% on a large table is a signal that autovacuum is not keeping pace. A ratio above 20–30% on a table receiving active queries will typically show up in query plans as degraded index efficiency.
For a deeper bloat estimate — accounting for free space within pages, not just dead tuples — the pgstattuple extension provides physical-level measurements:
-- Enable once per database
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Inspect a specific table
SELECT
table_len,
tuple_count,
tuple_len,
dead_tuple_count,
dead_tuple_len,
free_space,
ROUND(dead_tuple_len::numeric / NULLIF(table_len, 0) * 100, 2) AS dead_pct
FROM pgstattuple('public.orders');Note that pgstattuple performs a full table scan to produce accurate numbers, so avoid running it repeatedly on very large tables during peak traffic. Once per diagnostic session is appropriate.
If autovacuum appears to be running but dead tuples are not decreasing, check for blocking transactions. Long-running transactions hold an XID horizon that prevents VACUUM from removing dead tuples created before that transaction started:
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;Any transaction running for more than a few minutes on a write-heavy system deserves scrutiny. A stuck or forgotten transaction — an abandoned application connection, a long-running report query, a replication slot with a stale consumer — can silently block autovacuum from cleaning entire tables.
Tuning Autovacuum for High-Churn Tables
PostgreSQL’s global autovacuum defaults are conservative by design. The key parameter, autovacuum_vacuum_scale_factor, defaults to 0.2 — meaning autovacuum triggers only after dead tuples accumulate to 20% of the table’s row count. On a table with 50 million rows, that threshold allows 10 million dead tuples before cleanup begins. That is far too permissive for any table in the critical path of a production workload.
First, locate your configuration file to understand the current global settings:
SHOW config_file;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_threshold;Rather than lowering the global scale factor — which affects every table including small ones where the default is fine — apply per-table overrides to the relations that actually need aggressive vacuuming:
-- High-churn transactional table: trigger vacuum after 1% dead tuples
-- and reduce cost delay to allow faster I/O during vacuum runs
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_cost_delay = 2,
autovacuum_analyze_scale_factor = 0.005
);
-- Event log table with very high insert/delete rate
ALTER TABLE event_log SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 500,
autovacuum_vacuum_cost_delay = 5
);The autovacuum_vacuum_cost_delay parameter controls how much autovacuum throttles itself between I/O bursts. The default of 20ms is intentionally gentle to avoid impacting foreground queries. Dropping it to 2–5ms on tables where bloat is a known problem allows vacuum workers to complete their passes faster at the cost of slightly higher I/O during those passes. On modern SSDs this trade-off is almost always worth making.
A common mistake worth calling out explicitly: setting autovacuum_vacuum_threshold to a very high absolute value on small tables causes vacuum to never trigger during normal operation. The threshold is an absolute row count, not a ratio — it is the floor below which the scale factor calculation is ignored. On a table with 50,000 rows and a threshold of 100,000, vacuum will never fire regardless of the scale factor.
To confirm that your per-table settings are active and that autovacuum is actually running against the target tables, enable logging temporarily in postgresql.conf:
log_autovacuum_min_duration = 0This logs every autovacuum run with timing and tuple statistics. It is noisy in production and should be treated as a diagnostic tool rather than a permanent setting, but during initial tuning it provides direct confirmation that your parameter changes are having the intended effect.
Setting Up Prometheus and Alerting Rules
With postgres_exporter running and scraping your PostgreSQL instance on port 9187, the key metrics for bloat monitoring are available immediately:
– pg_stat_user_tables_n_dead_tup — dead tuple count per table
– pg_stat_user_tables_n_live_tup — live tuple count per table
– pg_stat_user_tables_last_autovacuum — Unix timestamp of last autovacuum
Add the postgres scrape target to your Prometheus configuration if it is not already present:
# prometheus.yml (scrape_configs section)
scrape_configs:
- job_name: "postgres"
static_configs:
- targets: ["localhost:9187"]
scrape_interval: 30s
scrape_timeout: 10sThe alerting rules below cover three distinct failure modes: absolute dead tuple accumulation, dead-to-live ratio bloat, and autovacuum staleness. Place this file at /etc/prometheus/rules/postgres_bloat.yml and reference it in your Prometheus configuration under rule_files:
# /etc/prometheus/rules/postgres_bloat.yml
# Prometheus alerting rules for PostgreSQL VACUUM and bloat monitoring
# Compatible with postgres_exporter >= 0.12 and Prometheus >= 2.40
groups:
- name: postgres_vacuum_bloat
interval: 60s
rules:
# Alert when dead tuples exceed absolute threshold on any table
- alert: PostgresHighDeadTuples
expr: |
pg_stat_user_tables_n_dead_tup{job="postgres"} > 500000
for: 10m
labels:
severity: warning
team: dba
annotations:
summary: "High dead tuple count on {{ $labels.relname }}"
description: >
Table {{ $labels.schemaname }}.{{ $labels.relname }} has
{{ $value | humanize }} dead tuples. Autovacuum may be lagging
or blocked by a long-running transaction.
# Alert when dead-to-live tuple ratio exceeds 20% (bloat indicator)
- alert: PostgresBloatRatioHigh
expr: |
(
pg_stat_user_tables_n_dead_tup{job="postgres"}
/
(pg_stat_user_tables_n_live_tup{job="postgres"} + 1)
) > 0.20
for: 15m
labels:
severity: warning
team: dba
annotations:
summary: "Bloat ratio > 20% on {{ $labels.relname }}"
description: >
Table {{ $labels.schemaname }}.{{ $labels.relname }} dead/live
ratio is {{ $value | humanizePercentage }}. Consider lowering
autovacuum_vacuum_scale_factor for this table.
# Alert when autovacuum has not run on a table for more than 24 hours
- alert: PostgresAutovacuumStale
expr: |
(time() - pg_stat_user_tables_last_autovacuum{job="postgres"}) > 86400
for: 5m
labels:
severity: critical
team: dba
annotations:
summary: "Autovacuum stale on {{ $labels.relname }}"
description: >
No autovacuum recorded on {{ $labels.schemaname }}.{{ $labels.relname }}
in the last 24 hours. Check for blocking transactions via
pg_stat_activity or disabled autovacuum storage parameters.
# Record rule: precompute bloat ratio for dashboards
- record: job:postgres_bloat_ratio:table
expr: |
pg_stat_user_tables_n_dead_tup{job="postgres"}
/
(pg_stat_user_tables_n_live_tup{job="postgres"} + 1)The PostgresAutovacuumStale alert deserves special attention. A table where autovacuum has never fired — or has not fired in over 24 hours on a write-heavy schema — is almost certainly accumulating bloat. The most common causes are: autovacuum explicitly disabled via ALTER TABLE ... SET (autovacuum_enabled = false) left over from a migration, a replication slot preventing XID advancement, or a long-running transaction that has been open since before the last vacuum cycle completed.
Reload Prometheus after placing the rules file:
promtool check rules /etc/prometheus/rules/postgres_bloat.yml
curl -X POST http://localhost:9090/-/reloadFor broader context on how to structure Prometheus alerting rules alongside other infrastructure alerts, the DevOps_DayS blog covers Alertmanager routing and alert fatigue reduction in production environments.
Running Manual VACUUM and VACUUM ANALYZE Safely
Even with well-tuned autovacuum, there are situations where a manual vacuum is the right call: immediately after a large bulk delete, after restoring a table from backup, or when an alert fires and you need to reduce dead tuples before the next autovacuum cycle.
The safe default for production is VACUUM VERBOSE, which runs without acquiring an exclusive lock and prints detailed statistics about pages reclaimed, dead tuples removed, and index vacuuming passes:
-- Safe for production: no exclusive lock
VACUUM VERBOSE orders;The output will include lines like removed 1842304 row versions in 9211 pages and index "orders_pkey" now contains 12500000 row versions in 34247 pages. This is the fastest way to confirm that vacuum is actually reclaiming space rather than being blocked.
If you also need the query planner to have updated statistics — which is almost always the case after significant dead tuple removal — combine the two operations:
-- Vacuum and update planner statistics in one pass
VACUUM ANALYZE orders;What to avoid: VACUUM FULL rewrites the entire table to a new heap file, which does reclaim physical disk space but requires an exclusive lock for the entire duration. On a 50GB table, that can mean 10–20 minutes of complete unavailability for that relation. Reserve VACUUM FULL exclusively for maintenance windows, or replace it entirely with pg_repack which achieves the same physical compaction without the lock.
If you need to observe what autovacuum is currently doing without interrupting it, query the autovacuum worker activity directly:
SELECT
pid,
now() - xact_start AS duration,
query,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE query ILIKE 'autovacuum:%'
ORDER BY duration DESC;This shows active autovacuum workers, which tables they are processing, and whether they are waiting on locks. An autovacuum worker stuck in a lock wait for more than a few minutes indicates a foreground transaction is blocking cleanup — which should trigger investigation of pg_stat_activity for the blocking query.
Verify It Works
After applying per-table autovacuum tuning and running a manual vacuum cycle on the worst offenders, re-run the diagnostic query from the first section and compare the results:
SELECT
relname,
n_dead_tup,
n_live_tup,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'event_log')
ORDER BY n_dead_tup DESC;After a successful vacuum cycle, n_dead_tup should drop to near zero on the target tables and last_autovacuum should reflect a recent timestamp. If dead tuples are not decreasing despite vacuum running, check whether the table has active long-running transactions holding back the XID horizon — that is the most common reason vacuum completes without reclaiming dead rows.
On the Prometheus side, the PostgresHighDeadTuples and PostgresBloatRatioHigh alerts should resolve within one to two scrape intervals after dead tuples drop below threshold. If the PostgresAutovacuumStale alert was firing, confirm it clears after the first successful autovacuum run on the affected table.
For ongoing validation, the precomputed record rule job:postgres_bloat_ratio:table makes it straightforward to build a Grafana dashboard panel showing bloat ratio trends per table over time. A healthy table should show a sawtooth pattern — ratio climbing gradually between vacuum cycles, then dropping sharply after each run. A flat high line or a monotonically increasing trend indicates autovacuum is not triggering at all.
Related Ideas
Several adjacent topics extend what we have covered here and are worth investigating once the baseline monitoring and autovacuum tuning are stable.
pg_repack for zero-lock physical compaction. When a table has accumulated severe bloat — dead ratio above 40–50% on a large relation — autovacuum will reclaim dead tuple space but will not return it to the operating system or compact the heap file. The pg_repack extension rebuilds tables and indexes online, without an exclusive lock, by maintaining a shadow copy and replaying changes via a trigger. It is the production-safe replacement for VACUUM FULL when physical file size reduction is required.
FILLFACTOR tuning for update-heavy tables. The fillfactor storage parameter controls how full PostgreSQL packs heap pages during initial writes. The default is 100% — every page is filled completely. Setting fillfactor = 70 on a table that receives frequent in-place updates leaves 30% of each page as free space, allowing HOT (Heap Only Tuple) updates to place new row versions on the same page as the old ones. HOT updates avoid index entries for the new row version and reduce vacuum workload significantly on tables with high update rates.
ALTER TABLE orders SET (fillfactor = 70);
-- Rebuild the table to apply the new fillfactor to existing pages
VACUUM FULL orders; -- only in a maintenance window, or use pg_repackConnection pooling and its effect on vacuum. PgBouncer in transaction pooling mode is the standard recommendation for high-concurrency PostgreSQL deployments, but it introduces a subtle vacuum interaction: idle transactions that are kept open at the application layer — waiting for the next query in a transaction block — hold XID horizons and can block autovacuum from cleaning dead tuples. Reviewing pg_stat_activity for long-lived idle-in-transaction connections is a necessary part of any bloat investigation on a system using connection pooling.
Replication slot XID horizon bloat. Logical replication slots and physical standby slots both hold back the oldest XID that PostgreSQL can safely vacuum past. A replication consumer that falls behind — or a slot that was created and never consumed — can cause entire tables to accumulate dead tuples indefinitely regardless of autovacuum configuration. Monitor slot lag with SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes FROM pg_replication_slots; and set max_slot_wal_keep_size to prevent unbounded WAL accumulation from abandoned slots.
