The Indeterminate Commit
PostgreSQL is famously a CP system in the CAP sense: a single primary accepts all writes, transactions are ACID, and the server itself is always consistent. But the distributed system formed by the server plus its clients has a subtler failure mode. A client and server can disagree about whether a transaction actually committed — even when the PostgreSQL server never violates its own guarantees.
The problem lives in the commit protocol. PostgreSQL, like most relational databases, uses something akin to two-phase commit. In phase one, the client votes to commit and sends that message to the server. The server checks constraints, and if satisfied, votes to commit, persists the transaction, and acknowledges to the client. Under normal operation, both sides agree on the outcome.
But what if the acknowledgement is dropped in transit? The client has no way to know if the commit succeeded or failed. Two-phase commit is not partition-tolerant; the protocol says you must wait for that acknowledgement to decide. Real systems can't wait forever, so the client eventually times out and reports an error. At that moment, the transaction's fate is indeterminate: it may have committed on the server, or it may never have arrived.
salticid postgres.setup
That installs PostgreSQL from apt, uploads configuration from jepsen/salticid/postgres, and creates a database for testing. The test application writes a single row per transaction.
cd salticid
lein run pg -n 100
With no interference, the result is predictably clean:
...
85 :ok
91 :ok
90 :ok
95 :ok
96 :ok
Hit enter when ready to collect results.
Writes completed in 0.317 seconds
100 total
100 acknowledged
100 survivors
All 100 writes succeeded. :-D
Here all five nodes talk to a single PostgreSQL server on n1. Of 100 writes, the clients reported all 100 as successful — and all 100 numbers appeared in the final result set.
Now introduce a partition. The failure window is narrow: the network must drop the connection after the server decides to acknowledge but before the client receives it. To widen that window, slow the network down:
salticid jepsen.slow
Start the test:
lein run pg
While it runs, sever all PostgreSQL traffic to and from n1:
salticid jepsen.drop_pg
If the timing is right, the test catches an acknowledgement in flight, and the client logs an error:
217 An I/O error occurred while sending to the backend.
Failure to execute query with SQL:
INSERT INTO "set_app" ("element") VALUES (?) :: [219]
PSQLException:
Message: An I/O error occured while sending to the backend.
SQLState: 08006
Error Code: 0
218 An I/O error occured while sending to the backend.
Subsequent transactions fail more predictably — they are attempted after the partition, so they time out cleanly:
220 Connection attempt timed out.
222 Connection attempt timed out.
Heal the partition with salticid jepsen.heal and let the test finish.
1000 total
950 acknowledged
952 survivors
2 unacknowledged writes found! ヽ(´ー｀)ノ
(215 218)
0.95 ack rate
0.0 loss rate
0.002105263 unacknowledged but successful rate
Of 1000 attempted writes, 950 were acknowledged, and all 950 were present in the database. But two writes — 215 and 218 — succeeded even though the client threw an exception claiming failure. Note the asymmetry: write 217 also threw an I/O error during sending, but that connection dropped before the commit message reached the server, so the transaction never happened.
From the client's perspective, there is no way to distinguish these two cases. A network error means the absence of information, not failure. Without a partition-tolerant commit protocol — like extended three-phase commit — the state of those in-flight writes is simply unknowable.
Mitigating False Negatives
Two-phase commit strategies aren't confined to relational databases. They appear wherever a distributed application coordinates writes across an asynchronous medium, and many applications implement similar protocols on top of document stores to fake multi-key transactions.
If you're living with two-phase commit, a few pragmatic options exist. The first is to accept false negatives: in most relational systems, the probability of this failure occurring is small, and it only affects writes in flight when the partition begins. Returning a failure to a client with a small chance that the write committed may be an acceptable trade-off.
Alternatively, design operations to be idempotent. On a network error, retry blindly. A highly available queue with at-least-once delivery is a natural home for repeatable writes that must be retried later without risk of double-applying.
Finally, some databases admit a stronger check: during the transaction, record the current transaction ID somewhere durable — a local disk log or an at-least-once queue — and after the partition resolves, check that log to decide whether to retry or cancel. This works only if you have storage suitable for the timescale of the partition, but it gives the client a definitive answer instead of an indeterminate one.
A very different approach to consistency appears next: Redis Sentinel.
