PostgreSQL XID Wraparound: What It Is and How to Prevent It

PostgreSQL XID Wraparound: What It Is and How to Prevent It Every PostgreSQL transaction gets a 32-bit transaction ID. That's about 4.2 billion IDs before the counter wraps around. If your database gets close to that limit without properly "freezing" old rows, PostgreSQL will do something drastic: it stops accepting new transactions entirely. Your database goes read-only. Not slow, not degraded -- completely unable to write. I want to walk through what's actually happening under the hood, how to detect it, and how to make sure it never happens to you. What Are XIDs and Why Do They Wrap Around? PostgreSQL uses transaction IDs to determine row visibility. When you run a query, PostgreSQL compares XIDs to figure out whether a particular row was committed before or after your transaction started. It uses modular arithmetic with a window of about 2 billion transactions in each direction. The problem: 32 bits gives you ~4.2 billion IDs total, but the visibility window is only ~2 billion. Whe