[Premium] Don't Ignore This Warning Before PostgreSQL Shuts Down Your Database
When PostgreSQL warns "must be vacuumed within X transactions," you have exactly one chance to avoid a Sev-1
The Incident: The Warning You Scrolled Past
Most teams don't see this warning until it's too late. They scroll past it in logs, assume autovacuum will handle it, or don't understand what wraparound means.
Then, Sev-1 is opened, the database goes read-only.
The PostgreSQL server log shows:
WARNING:
WARNING: database "test" must be vacuumed within 39985967 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in that
database. This warning appears exactly 40 million transactions before shutdown.
PostgreSQL is giving you a grace period. Many teams waste it.
If you ignore it, this happens:
ERROR: database is not accepting commands to avoid wraparound data loss in
database "test"
HINT: Stop the postmaster and vacuum that database in single-user mode.🚀 Get the PostgreSQL Health Report: The queries in this article are included in the PostgreSQL Health Report a single SQL function that runs 60+ diagnostic checks including TXID wraparound detection, autovacuum health, and ready-to-run fixes..
At this point, all INSERT, UPDATE, DELETE operations fail. All CREATE TABLE, DROP TABLE operations fail. Your application is effectively down. Only superuser VACUUM commands work.
Important: The 40-million-transaction warning is your last chance before catastrophe. This article is about that warning and exactly what to do when you see it.
Table of Contents
The Incident: The Warning You Scrolled Past
What This Warning Actually Means
The Three Phases Before Shutdown
The Complete Diagnostic Toolkit
Query 1: Check Current Wraparound Risk
Query 2: Find Tables Blocking XID Advancement
Query 3: Find Long-Running Transactions Blocking VACUUM
Query 4: Find Prepared Transactions (Often Forgotten)
Query 5: Find Replication Slots Holding Back XIDs
Query 6: Check If Anti-Wraparound Autovacuum Is Running
What to Do When You See the Warning
Why Autovacuum Didn’t Save You
Prevention: Never See This Warning Again
Query 7: Monitoring Alert Query
Configuration: Tune Autovacuum for Aggressive Freezing
Query 9: Find Tables Needing Per-Table Autovacuum Tuning
The 5 Things to Remember
Summary
What This Warning Actually Means
PostgreSQL assigns every transaction a 32-bit ID (XID). The counter goes from 0 to approximately 4 billion, then wraps around.
If old rows aren't frozen before the counter catches up to them, PostgreSQL can't tell if they're from the past or the future.
Instead of risking data corruption, PostgreSQL stops all writes.
This is intentional. It's a safety mechanism, not a bug. The warning at 40 million transactions is PostgreSQL saying:
I'm running out of transaction IDs. VACUUM this database now, or I'll have to shut down writes.
The Three Phases Before Shutdown
Phase 1: Normal Operation
Transactions remaining: More than 40 million
PostgreSQL behavior: No warnings, business as usual
Phase 2: Warning Phase
Transactions remaining: 40 million to 3 million
PostgreSQL behavior: Log warnings every checkpoint
Phase 3: Shutdown
Transactions remaining: Less than 3 million
PostgreSQL behavior: All writes blocked, only superuser VACUUM allowed
The window between warning and shutdown is 37 million transactions. On a busy SaaS database doing 1,000 TPS, that's approximately 10 hours. On a high-traffic system doing 10,000 TPS, that's approximately 1 hour.
The warning is not a fix it someday notification. It's a countdown.
The Complete Diagnostic Toolkit
The following queries are ordered by priority. Run them in sequence when investigating wraparound risk.
Query 1: Check Current Wraparound Risk
Purpose: See how close each database is to the wraparound cliff before you see any warning.
Version: PostgreSQL 9.4+
Scope: All databases on the server
