Implementing Two-Phase Commit Across PostgreSQL Shards

Two-phase commit is the only mechanism that makes a multi-shard write genuinely atomic, and the only one that can take a database down if it is operated carelessly. This guide implements it with the safeguards that make it survivable: a durable coordinator log, bounded prepare windows, an orphan recovery process, and alerting that treats a stuck prepare as an emergency. It implements the strictest option from Distributed Transactions & Cross-Shard Consistency under Cross-Partition Querying & Aggregation Strategies.

Prerequisites

Step 1 — Enable prepared transactions with a bounded budget

# postgresql.conf on every shard — requires a restart
max_prepared_transactions = 200      # 0 by default; 2PC is impossible until this is raised
max_connections           = 300
SHOW max_prepared_transactions;
-- confirm the shard will accept a PREPARE at all

Operational note: The default of zero is a safety feature. Raising it enables a failure mode — the leaked prepare — that simply cannot occur otherwise, so raise it only on shards that genuinely participate.

DBA tip: Size it from the maximum number of concurrent distributed transactions, not from max_connections. Each prepared transaction consumes a slot until resolved, and a slot exhausted by leaks fails new prepares with a confusing error about shared memory.

Step 2 — Write the coordinator’s intent before preparing anything

The single most important rule of 2PC: the coordinator must be able to reconstruct its decision after a crash. That means a durable record written before the first PREPARE:

# coordinator.py — the log write happens first, always
import uuid

def transfer(amount, src_shard, dst_shard, src_acct, dst_acct):
    gid = f"xfer-{uuid.uuid4()}"

    # 1. durable intent, on storage independent of the shards
    coordinator_log.begin(gid, participants=[src_shard, dst_shard],
                          op="transfer", amount=amount)

    prepared = []
    try:
        for shard, sql, params in (
            (src_shard, "UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src_acct)),
            (dst_shard, "UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst_acct)),
        ):
            with shard.connection() as conn:
                conn.execute("BEGIN")
                conn.execute("SET LOCAL statement_timeout = '5s'")
                conn.execute(sql, params)
                conn.execute("PREPARE TRANSACTION %s", (gid,))
            prepared.append(shard)
        coordinator_log.decide(gid, "commit")          # 2. decision is durable before acting
    except Exception:
        coordinator_log.decide(gid, "abort")
        for shard in prepared:
            shard.execute("ROLLBACK PREPARED %s", (gid,))
        raise

    for shard in prepared:                             # 3. apply the decision, retrying forever
        shard.execute("COMMIT PREPARED %s", (gid,))
    coordinator_log.complete(gid)

Operational note: The same gid on every participant is what makes recovery possible. Encode enough in it to identify the operation — a prefix plus a UUID is enough; timestamps in gids are a trap because clocks disagree.

SRE tip: SET LOCAL statement_timeout inside the transaction bounds how long a participant can sit before preparing. It does not bound the prepared state itself — nothing does except the coordinator.

The decision point, and why the log must precede the prepares The coordinator writes intent to a durable log, prepares each shard, records its decision, then commits each prepared transaction. A crash before the decision is recorded is resolved by aborting; a crash after it is resolved by committing. Without the log, a crash between the two prepares leaves no way to know which choice is safe. coordinator shard A shard B log: BEGIN gid PREPARE TRANSACTION 'gid' PREPARE TRANSACTION 'gid' log: DECIDE commit COMMIT PREPARED 'gid' COMMIT PREPARED 'gid' the decision point Crash above the line → recovery rolls back: no decision was recorded, so aborting is always safe. Crash below the line → recovery commits: the decision is durable, and every participant has already prepared. Without the log there is no line, and a human must inspect both shards to guess which half of the transfer was intended.

Step 3 — Recover orphans automatically

A prepared transaction whose coordinator vanished must be resolved by something. Run a recovery worker on a schedule:

What recovery does with an orphan, by log state If the coordinator log records a commit decision, the orphan is committed. If it records an abort, the orphan is rolled back. If the log has a begin entry but no decision, aborting is safe because no decision was ever communicated. If the log has no entry at all, the transaction predates the log or the log is lost, and a human must inspect both shards. Coordinator log state Recovery action Why it is safe DECIDE commit COMMIT PREPARED every participant prepared successfully DECIDE abort ROLLBACK PREPARED the decision was already final BEGIN, no decision ROLLBACK PREPARED no outcome was ever communicated no entry at all escalate to an operator the log is missing; guessing risks data loss Three of the four rows are automatable, which is why the recovery worker can run unattended. The fourth exists only when the log was written after the prepare or stored on a shard that was itself lost — both of which are design errors, not accidents. Note the asymmetry: aborting an undecided transaction is always safe, committing one never is. 
# recovery.py — runs every minute on every shard
def recover(shard):
    rows = shard.query("""
        SELECT gid, prepared, now() - prepared AS age
        FROM   pg_prepared_xacts
        WHERE  now() - prepared > interval '2 minutes'
    """)
    for gid, prepared_at, age in rows:
        decision = coordinator_log.decision_for(gid)     # 'commit', 'abort', or None
        if decision == "commit":
            shard.execute("COMMIT PREPARED %s", (gid,))
        elif decision == "abort":
            shard.execute("ROLLBACK PREPARED %s", (gid,))
        else:
            # no decision recorded: the coordinator crashed before deciding → abort is safe
            if age > timedelta(minutes=15):
                shard.execute("ROLLBACK PREPARED %s", (gid,))
                alert(f"orphan {gid} rolled back after {age}")

Operational note: Aborting an undecided transaction is always safe, because a decision that was never recorded was never communicated to anyone. Committing an undecided one is never safe.

DBA tip: Keep the coordinator log longer than any plausible recovery window — weeks, not hours. Its rows are tiny, and a missing decision turns an automatic recovery into an incident.

Step 4 — Alert on prepared-transaction age, not count

A count of prepared transactions is normal; an old one is not:

# queries.yaml for postgres_exporter
prepared_xacts:
  query: |
    SELECT count(*) AS total,
           coalesce(extract(epoch FROM max(now() - prepared)), 0) AS oldest_seconds
    FROM pg_prepared_xacts
  metrics:
    - total:           {usage: "GAUGE", description: "Prepared transactions currently held"}
    - oldest_seconds:  {usage: "GAUGE", description: "Age of the oldest prepared transaction"}
# page immediately — this blocks vacuum on every table in the database
pg_prepared_xacts_oldest_seconds > 300
What one leaked prepare does to the whole database From the moment a prepared transaction is left unresolved, the oldest transaction id horizon stops advancing. Dead row versions accumulate on every table in the database rather than only the one involved, growing steadily over twelve hours, and autovacuum cannot reclaim any of them until the prepared transaction is committed or rolled back. 40 GB25 GB10 GB0 dead tuples across all tables coordinator crashes; prepare left held autovacuum keeps running and reclaims nothing normal hours 1–12 after the leak The affected tables are not only the ones the transaction touched — the horizon is database-wide, so an unrelated high-churn table bloats fastest and is usually where the symptom is noticed first. This is why the alert threshold is five minutes: by the time bloat is visible on a dashboard, hours of damage are done.

Operational note: Alert on the oldest age, and page rather than warn. There is no volume of prepared transactions that is dangerous — only duration.

SRE tip: Include pg_prepared_xacts in the post-failover checklist. A promoted replica inherits prepared transactions from the WAL stream, and a coordinator that no longer knows about them will not resolve them.

Verification

Exercise the whole path, including recovery, on a staging shard pair:

-- shard A: prepare and deliberately abandon
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 8842;
PREPARE TRANSACTION 'test-orphan-1';

-- confirm it is visible and holding
SELECT gid, prepared, now() - prepared AS age FROM pg_prepared_xacts;
      gid       |           prepared            |      age
----------------+-------------------------------+---------------
 test-orphan-1  | 2026-08-03 09:14:22.118401+00 | 00:02:31.4

The recovery worker should resolve it within its interval, the metric should return to zero, and a SELECT balance should show the pre-transaction value. Then repeat with a decision recorded as commit and confirm the worker commits rather than rolls back.

Failure mode table

Failure mode Root cause SRE mitigation
Database-wide bloat and rising xid age a prepared transaction was left unresolved and pinned the vacuum horizon page on oldest_seconds > 300; run the recovery worker every minute; roll back undecided orphans after fifteen minutes
PREPARE fails with “maximum number of prepared transactions reached” slots exhausted by leaked prepares, or max_prepared_transactions sized for the average rather than the peak resolve orphans first, then raise the setting and restart; alert on slot utilisation above 60%
Recovery cannot decide an orphan’s outcome the coordinator log was lost, or was written after the first PREPARE instead of before write intent before preparing, store the log off the shards, retain it for weeks; escalate undecidable cases to a human with both shards’ state attached

FAQ

What value should max_prepared_transactions have?

Zero unless you genuinely use 2PC, because the default of zero is what prevents an accidental prepared-transaction leak from ever happening. When you do need it, set it to the maximum number of concurrent distributed transactions per shard plus headroom — commonly the same value as max_connections. It requires a restart and allocates shared memory, so size it once rather than tuning it repeatedly.

How does the coordinator recover after a crash?

From its own durable log, which must be written before the first PREPARE. On restart it reads every transaction whose outcome is undecided, queries each shard’s pg_prepared_xacts for the matching gid, and applies the recorded decision: commit if the log says all participants prepared successfully, roll back otherwise. Without that log the coordinator cannot know whether committing is safe, and a human has to decide.

Why does a stuck prepared transaction break vacuum?

A prepared transaction still holds an open transaction id, so the database’s oldest running transaction never advances past it. Vacuum cannot remove any row version newer than that horizon, on any table in the database, so bloat accumulates everywhere and the server edges toward transaction id wraparound. That is why the alert threshold is minutes rather than hours.

Back to Distributed Transactions Cross Shard Consistency