Streaming replication transfers and replays PostgreSQL write-ahead-log changes from a primary to a standby. It provides reliable native replication, but creates a distributed system whose replicas can expose different points in time. section 5.6
The stale-read account mistake
Suppose alpha is primary and beta is a read-only standby configured with a 60-second apply delay. Beta reports an account balance of 100; alpha then updates it to 90. Before beta catches up, another application request reads 100 from beta and writes 150 to alpha, applying a bonus to stale data. The business loses 10 and grants an unintended 50. PostgreSQL preserves ACID safety within its database operations, but the application has split one logical decision across nodes with inconsistent views. 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 executable account-table setup and the sequence of reads and writes that exposes replication lag. section 5.6
