With read/write splitting, a successful write and a subsequent read containing that write are separate conditions. Reading one's own correction requires both replay of its commit on the target replica and a business query obtaining a new snapshot there afterward. This article covers PostgreSQL 18, one primary and its physical streaming read replicas, querying ordinary tables. Logical replication, cross-cluster synchronization, analytical warehouses and caches require their own completeness protocols.
Separate commit, replication and visibility
Write-ahead logging, or WAL, records database changes; physical replicas receive and replay those records. A successful local primary commit does not establish that every asynchronous replica has applied the same position. PostgreSQL's hot-standby documentation distinguishes visibility at the two ends: once a transaction's commit record is replayed, its changes become visible to new snapshots taken afterward.[1] An immediate page refresh returning old data should therefore not initially be diagnosed as a lost write.
One API writing and then reading does not automatically provide read-your-writes consistency. The write may reach primary P while middleware routes the read to S1; another refresh may reach a more delayed S2. To test the application's promise, connect commit acknowledgment, the actual reader node, transaction boundaries and result version. Closely timed requests alone are insufficient evidence.
Managed services also require topology-specific inspection. Amazon RDS documentation for DB instance read replicas states that source updates are replicated asynchronously.[7] That helps identify the boundary of this asynchronous arrangement; it does not establish one protocol for every managed cluster or Multi-AZ product. Procurement and operational handover should record the actual product, engine version and purpose of each endpoint.
The requirement here is to read a state containing a specified write, possibly along with later committed changes. It is not a historical-state query for the exact commit instant, nor a promise that business values remain unchanged. Another legitimate transaction can subsequently modify the order. Exact historical reproduction needs retained input versions or result snapshots, not merely replication progress.
Permissions and metric definitions remain prerequisites. A caught-up replica does not mean the user may see every order, or that orders and an external refund system are jointly complete. Fix the tenant, filters, currency unit and target table before comparing. Differences due to permissions, definitions or upstream completeness will not be repaired by longer waiting.
Three orders and an inspectable commit sequence
Construct an orders table with a non-null unique order_id and integer-unit amounts, all in one authorized scope. Initially O1 is 100 and O2 is 200, totaling 300. One primary transaction changes O2 to 170 and inserts O3 for 80. After successful commit, there should be three orders totaling 350. Allow no other writes during this test so the expected outcome is unambiguous.
Label the write transaction T and the business operation R17, and retain actual commit confirmation. Afterward, obtain a WAL position on the same primary connection as the read gate. A log sequence number, or LSN, identifies a log position, not a timestamp or order version. We use 0/3000200 as an illustrative gate; deployment must use the function's actual return value rather than hard-code it.
Assume the replica has replayed through 0/3000100 and T's commit record remains beyond that point. A direct replica query can return the old two rows totaling 300. It may be correct within that replica's current view yet violate the promise to include R17's correction. A new primary snapshot under the same authorization scope should return three rows totaling 350.
Once that same replica reaches the gate or a later position, such as 0/3000280, a new query snapshot there should see three rows totaling 350. The gate is 256 bytes beyond the earlier position. That difference does not identify a number of missing orders or milliseconds remaining: transactions generate different WAL volumes and replay speeds vary.
Retain a reconciliation record containing R17, confirmed commit, trusted cluster and topology generation, gate LSN, actual replica identity, replay position when the gate passed, business-query start, result count and total. These are proposed application fields, not a complete audit table automatically produced by PostgreSQL. Keep only identities and outcomes needed for verification rather than duplicating all sensitive detail.
Path one: send critical reads to the primary
When post-write read volume is modest and the primary has capacity, the simplest implementation routes the related critical read to the confirmed primary and starts a new query after commit acknowledgment. Mark the operation or session as requiring this guarantee instead of permanently sending every analytical report to the primary. A trusted service should create routing markers; arbitrary client input must not select administrative endpoints.
This reduces replay-check and waiting logic but returns read CPU, I/O and connection load to the primary. Expensive full-table aggregation still needs scope, indexing and execution budgets. Pinning a user to the primary for a few seconds is only a time policy. Once routing returns to a replica, elapsed time does not prove that replica has caught up; fixed delays are not consistency evidence.
The primary path also needs an appropriate new snapshot. A pooled connection still holding an old transaction, or an application returning cached results, defeats the inference that primary routing guarantees fresh values. End old transactions explicitly under the pool's cleanup rules before controlled reads. If failover leaves primary identity uncertain, enter an explicit recovery state instead of continuing to claim the guarantee at an old address.
Path two: obtain a post-commit gate and wait on the actual replica
To preserve read scaling, confirm COMMIT success and then execute SELECT pg_current_wal_insert_lsn() on the original primary connection, retaining the returned position. The documentation distinguishes insertion, write and flush positions; insertion marks the logical WAL end at that instant.[2] Sampling after commit can cover T's commit record and also later WAL from other sessions. It is therefore a conservative gate, not T's exact commit position.
Do not sample inside the write transaction before commit. Row changes may already have generated WAL without the commit record being covered. A replica can pass that prematurely sampled position while T remains invisible. Neither application wall-clock time nor a last_updated field substitutes for this evidence. If commit is confirmed but sampling fails, use the original operation record to select a critical primary read; do not repeat the business write merely to obtain a new position.
Bind the gate to trusted cluster identity, topology generation and operation identity, and transfer it through a controlled server-side path. Select a specific replica, then check pg_is_in_recovery() and pg_last_wal_replay_lsn() on the same physical connection. Compare replay position against the gate using pg_lsn semantics.[2] Do not compare textual values lexicographically or substitute the received position, pg_last_wal_receive_lsn(), for the applied position.
Perform waiting checks in short transactions or autocommit mode. Only after the condition passes should the business-read transaction begin. Where totals and detail need a common view, establish a new read-only Repeatable Read transaction on that connection, read the complete group, and end promptly. Establishing an old stable snapshot and then polling inside it does not refresh that snapshot when replay catches up.
The SQL report snapshot-consistency method explains how multiple queries share a view. The additional ordering here is replay-gate confirmation followed by business-snapshot acquisition. If a proxy changes backends between statements or transactions, retaining a logical connection object is insufficient. Use a path that pins the actual node; if binding cannot be established, prefer an easier-to-test alternative such as the primary path.
Waiting needs an overall deadline, backoff interval and cancellation route, with time left for the subsequent query. For example, a request might have a 1,000-millisecond budget with at most 300 milliseconds for checks and waiting. Those are illustrative application agreements, not PostgreSQL standards. If the gate is not reached, use a primary fallback only when remaining time and primary load permit, or return explicitly not ready. Do not silently return old data as a successful protected read.
The flow applies only within one controlled replication history. A disconnection, promotion or cluster-generation change invalidates the node-check evidence and requires renewed confirmation of the operation and replication relationship. Equal or larger LSN values alone do not establish a common history. An old primary's commit might be absent from the new primary. Check R17's business receipt and new-primary state instead of hiding loss or uncertainty behind a position comparison.
Path three: synchronous apply still requires routing constraints
With appropriate synchronous standbys configured, PostgreSQL's synchronous_commit=remote_apply makes commit wait for the current synchronous standbys to report replay of the transaction.[3] Waiting moves into write acknowledgment. This differs from acknowledgment of remote write or flush, but adds commit latency and makes synchronous-standby availability a dependency of the write path.
Changing this setting alone does not make every replica immediately readable. With empty synchronous_standby_names, it does not wait for a remote replica. Where FIRST or ANY selects a confirmation set among multiple standbys, nodes outside the transaction's confirming set can still lag.[3][4] Explain how reads reach a node covered by the guarantee. Where necessary, retain per-node gates and ensure the business snapshot follows confirmation.
Choose among the three paths by workload. Evaluate primary routing first for a small volume of critical post-write reads. Consider position-gated waiting for many reads that can wait and can bind reliably to nodes. Consider remote_apply when synchronous application is itself required and write latency plus failure blocking are acceptable. No configuration name simultaneously establishes lower latency, greater throughput and zero operating cost.
Changing synchronous settings is a database operations decision. Validate failure behavior, write deadlines, switching and recovery in an isolated environment. No waiting threshold or deployment scale here is a universal recommendation, and the three-row example is not a performance test. Acceptance should traverse the actual pool, proxy and routing policy, not only a successful administrator query using a direct connection.
Distinguish byte gaps, historical lag and the current request
On the primary, pg_stat_replication exposes sent_lsn, write_lsn, flush_lsn and replay_lsn for directly connected standbys, helping locate transmission, write, durability and application progress.[6] Establish node identity and sample timing before comparing. Downstream nodes in cascading replication are not all automatically represented in this primary view; observe the replica actually serving the application.
Time fields such as replay_lag measure recent WAL-processing delay, not a countdown predicting catch-up at current speed.[6] An idle primary may generate no new transactions while the last replayed transaction timestamp grows older. Subtracting it from current time can misdiagnose continuing lag despite no new changes. Combine positions, connection status and evidence of unapplied WAL.
Long replica queries can also conflict with replay. Hot-standby documentation describes conflicts involving cleanup, locks and other operations, with tradeoffs between delaying replay and canceling conflicting queries.[1] Repeatedly extending delays to preserve every long report can worsen other users' post-write reads. Inspect conflict records and query budgets rather than merely adding network retries.
If the gate passes and direct ordinary-table queries return 350 but the page still shows 300, investigate caches, stored aggregates and old result identifiers. Materialized-view refresh and data-boundary acceptance covers that separate lifecycle. Replay does not refresh every derived application result or replace verification that the actual response or page uses the new data.
Six failure tests linking inputs, evidence and repairs
Test one: in an isolated environment, hold S's replay before T's commit, then execute R17 and obtain its gate. An ungated read may expose the old two rows totaling 300. A protected read must wait, fall back to the primary or report not ready; it must not deliver the old result directly. Retain P's commit record, S's position and routing identity. Resume replay and retest the same business scope: three rows totaling 350.
Test two: deliberately sample a position before commit, control the replica to replay only to that point, then commit T. An incorrect implementation may release the read early. Move sampling after commit confirmation and repeat the ordering. Verify values and sampling time, not only LSN comparison. Ensure the early position really excludes the commit record, or the experiment does not form the intended counterexample.
Test three: let S1 pass the gate while S2 remains behind; perform the check on S1 but route the business query to S2. Acceptance should detect the backend change and recheck or reject delivery. Evidence includes actual node identities, connection or transaction association, and amounts. Pin the target and replay the scenario. A logical connection label or common hostname alone does not prove an unchanged backend.
Test four: establish a read-only Repeatable Read snapshot on the lagging replica and read 300. Let replay catch up, then query within that same transaction. Returning the old value remains expected isolation behavior, not replication failure.[5] End the old transaction, confirm progress and establish a new business snapshot. Retest the total and all three detail rows together instead of refreshing only one card.
Test five: exhaust the wait budget or obtain NULL from the replay function. Treat state as unconfirmed, never as zero lag or a successful comparison. Record whether the node remains in recovery, reconnection events and the timeout stage. Resolve identity or connection problems and retest through a new controlled read. Do not reexecute R17 when its write is confirmed; if commit itself is uncertain, inspect its receipt by business-operation identity first.
Test six: trigger failover after the gate check, or cancel a long query through a recovery conflict. Discard the undelivered result group, reconfirm topology and business state, and read again from a new boundary. Do not retain an old total while replacing only the detail. Preserve errors, generation, operation receipt and subsequent-query evidence. Continue claiming inclusion of the correction only after establishing that the new primary contains R17; otherwise report uncertainty and follow recovery handling.
Validate waiting cost and fallback load at operating scale
Position gates consume connections or application tasks. If one hundred requests per second each poll ten times, they add one thousand checks per second and may crowd out business queries. This is load arithmetic, not a benchmark. Limit concurrent waiters, use backoff, or reuse conservative progress observations for a controlled node. No optimization may treat S1's passed check as a guarantee about S2.
Primary fallback needs its own budget. If replicas suddenly fall behind, every request falling back together can overload the primary. Bound fallback concurrency and query cost, reserve capacity for critical reads, and report not ready when no budget remains. Deduct checking and query allowances from the overall request deadline so exhausted waits are not followed by expensive unbounded queries.
Separate observing progress from controlling recovery. Applications generally need appropriate read-only functions and business-table access, not permissions to pause replay, promote nodes or change replication settings. Gates originate in trusted services and operate within tenant and authorization scope. An LSN must not become a credential for reading another tenant's records, and internal node details need not be exposed to external users.
Deliver a minimal acceptance record covering engine version and physical topology; write confirmation and operation receipts; gate-sampling order; cluster generation and node binding; wait deadline and NULL handling; new-snapshot placement; ordinary versus derived results; fallback budget; and failover/conflict retests. Attach an actual input, response and owner to each item. Mark untested proxy behavior as unconfirmed.
Rerun affected tests after changes to drivers, pools, proxies, synchronous sets, failover policy or caches. If the business permits older values, simpler asynchronous reads can be appropriate with a stated data boundary. If it promises inclusion of the just-completed correction, establish commit, replication, node and snapshot conditions together. A usable read replica means an explicit reading guarantee, not merely a passing health check.
References
- [1] PostgreSQL 18: Hot Standby
- [2] PostgreSQL 18: System Administration Functions
- [3] PostgreSQL 18: Write Ahead Log — synchronous_commit
- [4] PostgreSQL 18: Log-Shipping Standby Servers
- [5] PostgreSQL 18: Transaction Isolation
- [6] PostgreSQL 18: The Cumulative Statistics System
- [7] Amazon RDS: Working with DB instance read replicas
