PostgreSQL VACUUM Monitoring: Stop Table Bloat Before It Hurts

Originally published on kuryzhev.cloud

---

Without PostgreSQL VACUUM monitoring, a busy table can degrade for weeks before anyone notices. Consider a typical failure: a high-churn orders table in a mid-sized production database. Queries that took milliseconds in the spring now take seconds. Disk usage on the volume grows steadily even though the row count is flat. Someone checks the dashboards, and CPU, connections, and replication lag all look acceptable.

Then the alerts arrive in a cluster. The disk is at 90%, index scans are slow, and a routine deploy that adds a column times out waiting for a lock. When an engineer finally looks at pg_stat_user_tables, the table holds far more dead tuples than live ones, and last_autovacuum is days old or empty. Autovacuum was enabled the whole time. It simply was not keeping up, or was not allowed to finish.

This scenario is illustrative, not a report of a specific incident. It matches the behavior the PostgreSQL documentation describes for MVCC. It is also a well-known pattern by which a healthy-looking database slowly decays.

Failure scenario: what bloat looks like from the outside

Bloat rarely announces itself. The symptoms are indirect, which is why teams misdiagnose them as "the database needs a bigger instance."

• Disk growth without data growth. Table and index files keep expanding while business metrics stay flat.
• Gradual query slowdown. Sequential scans read pages full of dead rows. Index scans visit heap pages that contain mostly garbage.
• Cache pressure. Bloated pages occupy shared buffers and the OS page cache, so the hit ratio drops and I/O rises.
• Autovacuum running constantly. Workers appear in pg_stat_activity all day on the same few tables, yet the numbers do not improve.
• Transaction ID wraparound warnings. In the worst case, the logs start warning about databases that must be vacuumed within a limited number of transactions.

The dangerous part is the lag between cause and symptom. A blocker that appeared on Monday may not be visible as a performance problem until the following month. That gap is exactly what monitoring has to close.

Why it happens: dead tuples and what stops their removal

PostgreSQL uses MVCC. An UPDATE writes a new row version and leaves the old one in place, and a DELETE only marks the row as removed. VACUUM reclaims those dead versions for reuse, but only when no running transaction could still need to see them. Plain VACUUM makes space reusable inside the table file. It only shrinks the file when empty pages sit at the very end, so in practice it rarely does.

Common causes of persistent bloat include:

1. Defaults that scale poorly. Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the row count. With the documented default factor of 0.2, a table with 500 million rows must accumulate roughly 100 million dead tuples before a run starts. PostgreSQL 18 adds autovacuum_vacuum_max_threshold to cap this trigger, but older versions have no such cap.
2. Old snapshots holding back the cleanup horizon. The following all pin the oldest transaction ID:
   • long-running queries,
   • sessions stuck idle in transaction,
   • abandoned prepared transactions,
   • replication slots that carry an xmin or catalog_xmin.
   VACUUM runs but cannot remove rows newer than that horizon.
3. Throttled workers. Autovacuum is cost-limited by design. On large, write-heavy tables with default cost settings and few workers, it can fall behind permanently. Check your version's documentation for the current defaults, since some changed across releases. For example, the autovacuum cost delay dropped from 20 ms to 2 ms in PostgreSQL 12.
4. Cancelled runs. A regular autovacuum cancels itself if it blocks another session's lock request, so frequent DDL on the same table can cancel it repeatedly. Anti-wraparound autovacuum does not yield this way. That run can then block your DDL instead.

Watch out for hot_standby_feedback. It is useful for avoiding query conflicts on replicas, but a long query on a standby can then hold back vacuum on the primary.

Inactive replication slots are a related risk. An inactive slot retains WAL indefinitely unless max_slot_wal_keep_size (PostgreSQL 13 and later) limits it. Any xmin or catalog_xmin it carries also holds back cleanup until someone drops the slot.

The fix: PostgreSQL VACUUM monitoring and per-table tuning

Start by measuring. This query ranks tables by dead-tuple pressure and shows when autovacuum last touched them. Run it on a schedule or expose it through your metrics exporter.

-- Tables with the most dead tuples and their autovacuum history
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- Who is holding back the cleanup horizon? (oldest snapshots first)
SELECT pid, state, xact_start, backend_xmin, left(query, 80) AS query
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  10;

-- Standbys with hot_standby_feedback report their xmin here
SELECT application_name, state, backend_xmin FROM pg_stat_replication;

-- Replication slots and prepared transactions can pin it too
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;

n_dead_tup is an estimate maintained by the statistics system, so treat it as a trend signal, not an exact count. For a precise measurement on a specific table, use the pgstattuple extension.

• pgstattuple() scans the entire table.
• pgstattuple_approx() skips all-visible pages using the visibility map.

Both add I/O, so use them deliberately on large tables.

Next, tune the tables that actually need it instead of changing global settings. The statements below make autovacuum fire earlier on a large hot table. They also let it do more work per cost cycle before sleeping. Adjust the numbers to your workload and verify with the official routine vacuuming documentation for your major version.

-- Per-table storage parameters: trigger on ~1% churn instead of 20%
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_vacuum_threshold     = 5000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit    = 2000  -- higher means less throttling
);

-- Guard rails against sessions that pin old snapshots (new sessions only)
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE app_user SET statement_timeout = '60s';
-- PostgreSQL 17+: also cap whole transactions
-- ALTER ROLE app_user SET transaction_timeout = '10min';

-- Log every autovacuum run slower than 1s so you can see what it is doing
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

Role-level settings take effect only for sessions opened after the change, so existing pooled connections keep the old values until they reconnect.

To make the signal visible, alert on it. The rule below assumes postgres_exporter with the per-table collector enabled. Metric names can differ between exporter versions, so confirm them against your own /metrics output. See the Prometheus alerting rules documentation for syntax.

groups:
  - name: postgres-vacuum
    rules:
      - alert: PostgresDeadTuplesHigh
        # dead tuples above 20% of the table, ignoring small tables
        expr: |
          pg_stat_user_tables_n_dead_tup
            / clamp_min(pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup, 1) > 0.2
          and pg_stat_user_tables_n_live_tup > 100000
        for: 30m
        labels: { severity: warning }
        annotations:
          summary: "Dead tuple ratio high on {{ $labels.relname }}"
      - alert: PostgresAutovacuumStale
        # no autovacuum for 3 days (or never) on a table holding many dead tuples
        expr: |
          (time() - pg_stat_user_tables_last_autovacuum > 259200)
          and pg_stat_user_tables_n_dead_tup > 100000
        for: 1h
        labels: { severity: warning }

If a table is already badly bloated, tuning alone will not give the space back. You have three main tools:

• VACUUM FULL rewrites the table but takes an ACCESS EXCLUSIVE lock for the duration, which blocks reads and writes.
• The pg_repack extension rebuilds the table online. It takes only short exclusive locks at the start and end, and it requires a primary key or a unique not-null index.
• REINDEX CONCURRENTLY (PostgreSQL 12 and later) handles index bloat.

Watch out for disk space: both table-rewrite approaches need room for a full copy of the table and its indexes, which is a problem when the volume is already nearly full.

Prevention checklist

Use this as a recurring review, not a one-time cleanup.

[ ] Dashboard: dead tuple ratio and last_autovacuum age per large table
[ ] Alert: oldest transaction age (backend_xmin) beyond an agreed limit
[ ] Alert: inactive replication slots and old prepared transactions
[ ] Alert: age(datfrozenxid) / age(relfrozenxid) well past autovacuum_freeze_max_age
[ ] Per-table autovacuum settings for large, high-churn tables
[ ] idle_in_transaction_session_timeout set for application roles
[ ] log_autovacuum_min_duration enabled and logs actually reviewed
[ ] Autovacuum worker count and cost settings reviewed after growth
[ ] pgstattuple / pg_repack tested on a staging copy before an emergency
[ ] Free disk headroom at least equal to the largest table for rewrites
[ ] Monitoring re-checked after each major version upgrade

Several habits sit behind that list:

• Review the settings whenever a table grows by an order of magnitude. A scale factor that was fine at one million rows is wrong at one billion.
• Keep long analytics queries off the primary. Alternatively, give them their own replica without hot_standby_feedback if that tradeoff suits you.
• Treat any session that stays idle in transaction as a bug in the application. The cause is usually a missing commit or a connection pool misconfiguration.

Also check wraparound separately from bloat. age(relfrozenxid) per table and age(datfrozenxid) per database tell you how close you are to forced anti-wraparound vacuums. These start once the age passes autovacuum_freeze_max_age and run even if autovacuum is disabled. They are still cost-throttled. Only the failsafe in PostgreSQL 14 and later, triggered at vacuum_failsafe_age, drops throttling to finish quickly. Watch out for anti-wraparound runs that appear stuck: they are often blocked by the same old snapshots described above.

Behavior and defaults vary between PostgreSQL major versions. For example, recent releases improved vacuum memory handling and progress reporting in pg_stat_progress_vacuum, so check the release notes for the version you actually run. For more infrastructure and database operations guides, see kuryzhev.cloud.

Related

• Rust game server logs in Loki: avoid label and parsing traps
• An unbounded Loki label turned a logging bill into shock
• SLO burn-rate alerts explained through a traffic spike