How a Nine-Day Idle Transaction Froze Every Write on Our Postgres Primary

03:14 UTC. Every API request that touches the database starts failing with the same error, and it's not one anyone on call has seen before: database is not accepting commands to avoid wraparound data loss. Reads still work. Every write, from every service, fails the same way, at the same instant.

There's no deploy in the timeline. No migration ran that night. The database hadn't been touched in hours before the first alert. Whatever caused this had been building for a lot longer than the page suggested.

the setup

We run a single Postgres 14 primary for the core product, one read replica for reporting. The largest table by a wide margin is events, an append-heavy log of user actions fed by a batch-import daemon that pulls hourly parquet exports from S3 and inserts them in transactional batches. At the time of the incident it held roughly 640 million rows and grew by about 38 million a day.

Postgres transaction IDs are a 32-bit counter. Every write gets one, and the counter is circular, it wraps back to the start once it runs out of numbers. To make that safe, autovacuum periodically "freezes" old rows so their transaction ID no longer needs to be compared against the live counter, which lets the oldest IDs be retired and reused without ambiguity. As long as freezing keeps pace with new transactions, the counter never gets close to actually wrapping.

Freezing can only advance up to the oldest transaction that's still technically in progress, because Postgres can't freeze rows that an open transaction might still need to see under its own snapshot. One long-lived open transaction anywhere in the cluster is enough to pin that horizon in place, no matter how often autovacuum runs.

the scramble

First theory in the incident channel, about two minutes in: PgBouncer had hit max_client_conn and was rejecting connections under load. We checked the pool stats. Plenty of headroom, nowhere near the ceiling, and the errors coming back weren't connection errors anyway, they were coming from Postgres itself, after a connection had already been granted.

Second theory: disk full, since a full WAL volume can also stop writes cold. df -h on the primary showed 61% used. Not that either.

Someone finally read the actual error text instead of pattern-matching it against past incidents, and grepped the Postgres log for anything from before the page fired.

WARNING:  database "prod" must be vacuumed within 903214213 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "prod".
You might also need to commit or roll back old prepared transactions,
or drop stale replication slots.

That warning had been in the logs for days, climbing in severity every few hours, and nothing was alerting on it. Our monitoring covered disk space, replication lag, connection counts, and query latency. Nobody had ever wired an alert to transaction ID age, because nobody had needed one before.

the hunt

With the warning confirmed, the question became which transaction was old enough to be blocking autovacuum from catching up.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

 datname |    age
---------+------------
 prod    | 2146999558

Roughly 2.147 billion, within a few hundred thousand transactions of the hard stop Postgres enforces to keep the counter from wrapping. That's what had flipped the switch: past that line, Postgres refuses new transaction IDs entirely rather than risk two different rows silently claiming the same one after a wrap.

Next question: what was pinning the freeze horizon so far behind. pg_stat_activity answered it in one query.

SELECT pid, state, xact_start, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

  pid  |        state        |     xact_start      |     duration      |                query
-------+----------------------+---------------------+--------------------+--------------------------------------
 48213 | idle in transaction | 2026-08-12 08:02:11 | 9 days 14:12:03    | INSERT INTO events (...) VALUES (...)
                                                                             ON CONFLICT (event_id) DO NOTHING

Nine days, fourteen hours. A single connection, held open in the batch-import daemon's connection pool, sitting inside a transaction that had never been committed or rolled back since the previous Wednesday. Every autovacuum run on events since then had started, scanned, and stopped at exactly the same point, because it could never freeze past a snapshot that connection might still be reading from.

the find

The daemon's batch-insert function wrapped each hourly import in an explicit transaction and caught every exception around it, on the assumption that a bad batch should get logged and skipped rather than kill the process.

def import_batch(conn, rows):
    cur = conn.cursor()
    try:
        cur.execute("BEGIN")
        for row in rows:
            cur.execute(INSERT_SQL, row)
        conn.commit()
    except Exception as e:
        logger.error(f"batch failed: {e}")
        # no rollback here — the exception path never closes the transaction
    finally:
        cur.close()
    # conn goes back into the pool, still holding an open transaction

A batch nine days earlier had thrown a serialization error partway through, on a row that collided with a concurrent update from an unrelated job. The exception was caught, logged, and swallowed, but conn.rollback() was never called. The connection went back into the pool still inside that transaction. Every subsequent hourly run pulled a connection from the pool, most of the time a different one, but that specific connection kept getting reused for other work too, each call issuing its own inserts against the same still-open transaction rather than starting a fresh one. It never crashed. It never timed out. It just never closed.

Root cause: a missing rollback in an exception handler left a transaction open for over nine days, pinning autovacuum's freeze horizon on the busiest table in the database, while the transaction ID counter kept advancing on every other write in the cluster until it hit the wraparound safety limit and Postgres stopped accepting new transactions entirely.

the fix

The immediate fix was one query.

SELECT pg_terminate_backend(48213);
VACUUM FREEZE events;

Killing the backend released the horizon immediately. Autovacuum, which had been running the entire time but was never able to make progress, caught up within a few minutes once nothing was holding it back, and writes resumed.

The code fix replaced the manual BEGIN/commit/swallow pattern with psycopg2's connection context manager, which commits on clean exit and rolls back automatically on any exception, so there's no code path left that can exit without closing the transaction.

def import_batch(conn, rows):
    try:
        with conn:
            with conn.cursor() as cur:
                for row in rows:
                    cur.execute(INSERT_SQL, row)
        # `with conn` commits here on success, rolls back on any exception
    except Exception as e:
        logger.error(f"batch failed, transaction rolled back: {e}")

As a backstop against the next bug we haven't thought of, we set idle_in_transaction_session_timeout to five minutes on the role the daemon connects as. No legitimate batch in this pipeline holds a transaction open anywhere near that long, so anything that does gets killed automatically instead of quietly accumulating for over a week.

We also added two alerts that didn't exist before: one on age(datfrozenxid) crossing 1.5 billion, and one on any session sitting in idle in transaction for more than fifteen minutes. Both are cheap queries against pg_stat_activity and pg_database, run every minute.

the aftermath

Twenty-three minutes of a total write outage, start to resolution, once someone actually read the error text instead of chasing the usual suspects. Reads stayed up the whole time, so the product looked functional and felt broken, which generated more support tickets than a clean outage would have.

age(datfrozenxid) now sits on the same dashboard as replication lag and connection count, and the batch daemon hasn't left a transaction open past its own runtime since the fix shipped. The five-minute timeout hasn't fired once, which is exactly what we want from a backstop we hope never gets used.