Replication lag is the delay between a committed change on a primary and its application on a standby. It becomes dangerous when an application reads from a replica but writes related data to the primary: each node can present a different, individually valid view of the database. section 5.6
A stale balance causes a wrong update
With streaming replication from alpha to beta, beta is configured with recovery_min_apply_delay = 60000, creating a one-minute apply delay. An account starts at 100. The application reads 100 from beta, charges the account by updating alpha to 90, then another operation still reads 100 from beta and writes 150 to alpha as though no charge occurred. The customer receives 10 more than intended. 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)
The initial account row establishes the value later observed inconsistently by the delayed standby and updated on the primary. section 5.6
This is not a failure of PostgreSQL ACID guarantees within one server. The application is attempting an atomic action across nodes, which is a distributed transaction. Where that atomicity is required, the relevant mechanism is two-phase commit (2PC), using PREPARE TRANSACTION and related commands; simply mixing replica reads with primary writes does not provide it. section 5.6
Lag also changes failover risk. Promoting a lagging standby after primary failure can discard transactions that other replicas already received through WAL. The promoted node starts a new timeline, while replicas with later WAL cannot follow it until their WAL is rewound before the timeline split. Recovering omitted transactions and restoring the topology may be complicated, and the expected recovery point objective (RPO) may not be met. section 9.6
Treat replication lag as an operational constraint, not merely a delayed read:
- Do not base a primary update on a replica read when the decision requires the latest committed value. section 5.6
- Account for lag when selecting a promotion target; a standby may be behind other replicas. section 9.6
- Prefer established HA solutions over custom promotion scripts that must handle lag and failure edge cases. section 9.6
