Postgres vacuum debt compounds quietly

/* PostgreSQL vacuum debt diagnostics. Read-only. */
SELECT
    current_database() AS database_name,
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    last_analyze,
    last_autoanalyze,
    n_mod_since_analyze,
    age(c.relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    CASE
        WHEN age(c.relfrozenxid) > 1000000000 THEN 'CRITICAL: transaction ID age'
        WHEN n_dead_tup > 1000000 AND 100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0) >= 20 THEN 'HIGH: large dead-tuple backlog'
        WHEN n_dead_tup > 100000 AND 100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0) >= 10 THEN 'REVIEW: vacuum may not keep pace'
        ELSE 'Observe in workload context'
    END AS review_status
FROM pg_stat_user_tables AS s
JOIN pg_class AS c ON c.relname = s.relname
JOIN pg_namespace AS n ON n.oid = c.relnamespace AND n.nspname = s.schemaname
ORDER BY age(c.relfrozenxid) DESC, n_dead_tup DESC;

-- Long transactions can hold back vacuum cleanup.
SELECT
    pid, usename, datname, state, xact_start,
    now() - xact_start AS transaction_age,
    wait_event_type, wait_event,
    left(query, 300) AS query_text
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start;

Vacuum debt rarely announces itself with a single alert.

Track dead tuple ratios, freeze age, and wraparound risk. Couple that with index bloat signals so maintenance is scheduled against evidence, not folklore.

Why vacuum health deserves a method

The central principle is that vacuum debt grows when dead tuples accumulate faster than autovacuum can reclaim and freeze them. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

• dead tuple trend and table churn
• autovacuum run frequency and duration
• old transactions and replication slots
• XID age, bloat, and storage growth

A practical workflow

1. Find the relations accumulating debt.
2. Remove blockers such as old transactions.
3. Tune thresholds and cost settings for the table.
4. Verify that vacuum keeps pace with new churn.

Common mistakes

Avoid these shortcuts:

• running VACUUM FULL as routine maintenance
• tuning global settings for one hot table
• ignoring long transactions and retained slots

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Postgres vacuum debt compounds quietly. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Before you run it

• Review every statement and confirm the connection points to the intended PostgreSQL database server.
• Run through a non-production or read-only diagnostic account first when possible.
• Required access: statistics visibility on the target database; pg_monitor may reveal more session details.
• Save the output with the server name, database name, collection time, and workload window so another engineer can reproduce the analysis.

What it does

Returns live and dead tuple estimates, vacuum and analyze timestamps, modification count, XID age, relation size, a review status, and long-running transactions. The script is diagnostic and does not apply an automatic remediation. Result values still need workload context, a second supporting signal, and a before-and-after comparison.

How to read the output values

Prioritize high XID_Age because wraparound risk is time-sensitive. Dead_Tuple_Pct matters more when N_Dead_Tup and Total_Size are also large. An old Xact_Start can hold back cleanup. Statistics are estimates, so confirm with trends and table-specific autovacuum settings.