The symptoms made no sense for two days. n_dead_tup climbing on every hot table. Autovacuum running almost continuously and accomplishing nothing. Tables growing despite a delete job that had kept them flat for a year. I went hunting for the usual suspect — a long-open transaction — and pg_stat_activity was clean: nothing older than a few seconds, no idle-in-transaction stragglers. Vacuum's verbose log kept repeating that dead row versions could not be removed yet, without saying who was holding them. The who turned out to be one row in pg_prepared_xacts: gid jms-order-svc-88417, prepared eleven days earlier by a Java service that had crashed between prepare and commit and been redeployed without its transaction logs.
One forgotten PREPARE TRANSACTION had been holding the whole database's vacuum horizon hostage for eleven days. That is what two-phase commit costs when the coordinator dies at the wrong moment, and it is why the feature is off by default.
What does a prepared transaction actually pin?
The vacuum horizon, every lock it ever took, and a transaction ID that keeps aging — and it survives restarts, which is the entire point. PREPARE TRANSACTION detaches the transaction from its backend and writes its state durably (WAL records at prepare time, plus a file in pg_twophase once it survives a checkpoint) so that a coordinator can decide commit or abort later, even across a crash of either side. Until that decision arrives, PostgreSQL must treat the transaction as in progress. Concretely, that means the vacuum horizon for its database cannot advance past it, so VACUUM cannot remove any tuple that died after that point — on any table in that database, plus the shared catalogs cluster-wide, not just the ones it touched. It means the row locks and any heavyweight locks it held stay held: if your orphan took an AccessExclusiveLock during a schema migration, that table is locked until you resolve the xact, full stop. And it means its transaction ID remains unresolved while the cluster's XID counter marches on, so an orphan left for months becomes a wraparound-emergency ingredient, not just a bloat problem.
One clarification worth making, because the folklore overstates it: a prepared transaction does not pin pg_wal the way an abandoned replication slot does. The slot disaster — a volume filling segment by segment, covered in the replication slot WAL retention notes — comes from a retention pointer that refuses to move. The prepared transaction's WAL was written once, at prepare time, and the pg_twophase state file carries it across restarts without holding old segments. The pins that actually hurt are the horizon and the locks. Precision about which pin you have matters, because the runbooks are different.
How do you find orphaned prepared transactions before they hurt you?
By querying pg_prepared_xacts on a schedule and treating any row as a finding. If you do not run a coordinator, the correct count is zero, forever, and alerting on nonzero is a one-line rule. If you do run one, alert on age instead: anything older than your coordinator's worst-case recovery window — for us that is fifteen minutes — gets a page.
SELECT gid, database, owner, prepared,
now() - prepared AS age
FROM pg_prepared_xacts
ORDER BY prepared;
-- Locks held by prepared transactions have no backend behind them:
SELECT locktype, relation::regclass AS relation, mode, virtualtransaction
FROM pg_locks
WHERE pid IS NULL;
-- What is the ceiling right now?
SHOW max_prepared_transactions;The middle query is the underused one. Once a transaction prepares, its backend detaches, and its locks remain visible in pg_locks with a NULL pid — a NULL-pid AccessExclusiveLock is how we now spot migration-time orphans in seconds instead of wondering why ALTER TABLE has been waiting since Tuesday. The gid tells you which coordinator family owns it; every framework I have run embeds something recognizable — Atomikos and Narayana put the node name in, Citus uses its own naming for cross-shard commits — so the gid alone usually identifies the guilty service.
What is the safe runbook for resolving an orphan?
Step one: do not touch it until you know what the coordinator decided. The whole point of 2PC is that some other participant may already have committed; if you guess wrong you manufacture distributed inconsistency with your own hands. Pull the coordinator's logs — Atomikos and Narayana both write durable outcome records, Citus tracks it in metadata on the coordinator node — and find whether this gid was commit or abort. Step two: connect to the right database. Prepared transactions are per-database, and ROLLBACK PREPARED issued in the wrong database errors out — the server tells you the transaction belongs to another database and to run the command there instead. Step three: resolve it.
-- In the database shown by pg_prepared_xacts:
ROLLBACK PREPARED 'jms-order-svc-88417';
-- or, only when the coordinator's log says commit:
COMMIT PREPARED 'jms-order-svc-88417';When the coordinator's records are unrecoverable — which was our case, since the service was redeployed without its log volume — the pragmatic default is ROLLBACK PREPARED, and here is the reasoning: every day the orphan sits unresolved it degrades the whole database through the vacuum horizon, while a wrong rollback damages one business transaction that you can find and repair by hand. Choose the bounded, visible injury over the unbounded, silent one. Step four: verify the recovery, not just the resolution — pg_prepared_xacts empty, the NULL-pid locks gone, and within the next vacuum cycle n_dead_tup actually falling. Watching dead tuples finally drain after eleven days was one of the more satisfying graphs of my career. Then write the postmortem and fix the coordinator's log durability, because the orphan was a symptom of that, not of PostgreSQL.
Why is the default max_prepared_transactions zero, and when do you raise it?
Zero is a fail-fast design, and I have come to love it. With the default, PREPARE TRANSACTION errors immediately, which means a prepared transaction on a zero-config cluster can only exist because someone deliberately enabled the feature — there is no accidental 2PC. Raising the limit costs shared memory for the slots and, more importantly, requires a postmaster restart, so it is a planned change rather than a runtime knob. Raise it when, and only when, you operate a real coordinator: XA/JTA stacks like Atomikos or Narayana, Citus for atomic multi-shard commits, MSDTC-style .NET distributed transactions. Size it to the peak of concurrently prepared transactions you expect — the documentation suggests at least max_connections when the feature is in heavy use, and I have never regretted following that.
My position, stated plainly after the eleven-day incident: enabling max_prepared_transactions "just in case something needs it" is trading a loud, immediate failure for a silent, cluster-wide slow injury. Keep it at zero unless a coordinator you can name requires otherwise, and if something in your stack calls PREPARE TRANSACTION anyway, let it error in development where someone will read the message.
How do you watch the vacuum horizon with MonPG?
This incident is a four-signal story, and all four signals are counters MonPG graphs out of the box: dead tuples per table rising while autovacuum runs keep pace (work without progress is the tell), the age of the cluster's vacuum horizon advancing day over day, lock waits accumulating behind NULL-pid holders, and — if you alert on it — any row at all in pg_prepared_xacts. The vacuum side of that dashboard is the same machinery described in the PostgreSQL vacuum guide; the prepared-transaction angle is just the horizon held by an invisible hand. Since the incident we page when the horizon's age crosses a threshold regardless of cause, and the runbook's first query is the pg_prepared_xacts SELECT above. MonPG's PostgreSQL monitoring keeps those horizon and lock series on one screen, which is the difference between a two-day mystery and a two-minute diagnosis the next time a coordinator dies at the wrong moment.