A distributed system coordinates components running on multiple networked nodes to provide one service. PostgreSQL streaming and logical replication qualify: primary and standby servers form a distributed arrangement even without multi-master writes. CAP and PACELC tradeoffs therefore apply. section 5.6
The stale-read account charge
Suppose alpha is the primary and beta is a read replica with recovery_min_apply_delay set to 60,000 milliseconds. The application reads an account balance of 100 from beta, changes the balance to 90 on alpha, then reads 100 again from beta while applying a +50 bonus on alpha. The resulting balance is 150 rather than the value implied by the latest primary state. PostgreSQL preserves ACID safety on each server, but the application has split one supposedly atomic workflow across nodes with an inconsistent view. section 5.6
Avoid homemade replication mechanisms and external ETL, ELT, or CDC systems when native PostgreSQL replication already supplies the required capability. Treat multi-master replication as a specialized choice: distributed serialization anomalies, prerequisites, and CAP/PACELC tradeoffs make it more complex than it first appears. section 5.6 section 5.7
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)
Use the account table and psql listings to reproduce the primary-versus-standby balance discrepancy before evaluating replication behavior. section 5.6
