A distributed transaction is one atomic unit of work whose operations run on multiple database nodes. PostgreSQL protects transactions within a server, but an application spanning nodes must preserve atomicity itself. section 5.6
The replica-read mistake
Suppose primary alpha changes an account balance from 100 to 90 while standby beta still reports 100. Reading beta and then applying a +50 update on alpha produces 150: the application acted on an inconsistent view and violated the intended business transaction. section 5.6
Two-phase commit can coordinate the operation, but it carries operational costs:
- Use PREPARE TRANSACTION and associated commands to implement the two phases. section 5.6
- Keep prepared transactions short-lived; latency and replication lag make them difficult to manage. section 5.6
- Expect locks and abandoned prepared work after application crashes or restarts. section 5.6
- Running exclusively on the primary avoids read-replica benefits. section 5.6
CREATE TABLE test.accounts (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
balance numeric NOT NULL,
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
INSERT INTO test.accounts (balance) VALUES (100);
TABLE test.accounts;
INSERT 0 1
id | balance | updated_at
----+---------+-----------------------------
1 | 100 | 2024-01-17 19:08:31.0284+00
(1 row)
Inspect the account setup and follow the primary-versus-standby balance changes that expose the cross-node consistency failure. section 5.6
