The write succeeded, then the old value came back
A user saves a setting, receives a success response and reloads the page. The previous value appears. Before adding another write retry, identify which database served the read. A successful commit on the primary does not by itself establish that an asynchronous replica has applied it.
In the constructed incident here, the primary commits revision 42 while a replica still exposes revision 41. Replication catches up after an assigned delay of 3 seconds. Four reads arrive at 0.1, 1.0, 2.1 and 3.1 seconds after the write. The executed routing model compares those same reads under four policies.
Reading only from the replica returns the old revision three times. A 2-second sticky-primary window reduces that to one stale read but does not eliminate it. Primary routing and a trusted revision gate both return the written revision for every read in this fixture. The 16-row trace exposes the routing decision rather than hiding it behind an average latency number.
Reconstruct the read path before blaming the commit
The first useful comparison is the primary's committed value against the value returned by the next read. If the primary also holds the old value, replica lag is not yet an explanation. Check whether the write targeted the intended tenant and record, whether its transaction committed and whether a concurrent write changed the value again.
If the primary has revision 42 but the application returns 41, trace the request through any cache, read router and replica. The response can be stale before it reaches a database. The separate cache-aside race example covers a delayed cache fill; this investigation assumes the response comes directly from the selected database state.
Record the request identifier, selected backend and relevant data revision without logging the record payload. Compare observations from the same request sequence. A dashboard showing a healthy replica several seconds later cannot explain which version it exposed when the failing read occurred.
PostgreSQL 18 documents asynchronous streaming replication, including the delay between a primary commit and visibility on the standby. It distinguishes receiving WAL from replaying it. For read visibility, inspect replay progress. Receiving bytes is an earlier stage and does not establish that a query can see their effects.
The timer fixes the example until the delay outlasts it
The sticky policy in the fixture sends reads to the primary while elapsed time is below 2 seconds. The reads at 0.1 and 1.0 seconds succeed. At 2.1 seconds, the timer expires, routing returns to the replica and revision 41 appears again. The next read at 3.1 seconds sees 42 because replay has caught up.
| Routing policy | Reads | Stale reads | Reason for the result |
|---|---|---|---|
| Replica only | 4 | 3 | Reads arrive before the assigned replay time |
| Primary only | 4 | 0 | Every read uses the primary state |
| Primary for 2 seconds | 4 | 1 | The stickiness window ends before replay finishes |
| Trusted revision gate | 4 | 0 | A replica behind revision 42 falls back to primary |
A longer timer would pass this particular sequence. It would establish a bound only if the system had a justified upper bound on the relevant replication delay and the timer covered it. Increasing the timer until a demonstration turns green does not supply that evidence.
The fixture uses in-memory revision numbers and an assigned replay time. It does not start PostgreSQL, measure WAL throughput or implement a routing proxy. The results file labels those limits. Its value is a counterexample to a timer-based guarantee, not a recommended production timeout.
A revision gate needs evidence from the same history
A consistency token can express the minimum state a later read must observe. In this model, the write yields a trusted requirement of revision 42. The selected replica can serve the read only when its applied revision is at least 42; otherwise the router uses the primary.
A real implementation needs a token with meaning in the actual replication history, a trustworthy way to bind it to the write and a visibility check on the backend that will serve the query. A client-supplied large integer cannot simply force arbitrary waiting. Validate token scope, limit its resource impact and keep tenant authorization independent of consistency routing.
PostgreSQL's recovery information functions expose replay information for an implementation-specific check. Capturing a position, checking it and starting the read transaction must be designed together. Failover, multiple replicas and an already-open transaction snapshot can invalidate a casual translation of this model into a few SQL calls.
The model chooses immediate primary fallback. Waiting for a replica is another policy, with a request deadline and a defined outcome when the deadline expires. The request-deadline article shows why every wait consumes the same end-to-end budget. Do not silently serve stale state after promising the caller read-your-writes consistency.
Choose the guarantee for the user action
A confirmation page after saving a setting usually needs evidence of that save. A broad analytics view may tolerate documented staleness. Those two reads can use different policies even when they query the same underlying data. State the contract at the application operation, then choose routing and fallback behavior that can support it.
PostgreSQL's remote_apply synchronous-commit mode waits for the relevant synchronous standbys to replay a transaction. That is stronger than confirmation that WAL was written, but it does not turn every asynchronous or downstream replica into a suitable read destination. Check the configured acknowledgement set and the actual router together.
For the fictional setting change, the acceptance test should write a distinctive value, force the next request onto each eligible read path and inspect the returned revision while replay is delayed. Include a failover or unavailable-primary outcome if the product promises to handle it. The diagnostic worksheet records the missing observations without marking an untested deployment as consistent.
Keep the stale-read case in the test even after primary routing makes it disappear. The next routing optimization needs to demonstrate why the same save-and-reload sequence still sees revision 42.
Sources
Documentation checked .
Dreamtsoft Editorial
The read just after the sticky-primary window expires is the revealing case. Extending the timer to fit one trace would not establish an upper bound on replication delay.
Dreamtsoft Editorial
Record the backend that served the failing request. A later healthy replica observation cannot reconstruct which revision that earlier read actually returned.
Dreamtsoft Editorial
The consistency token needs a trusted scope as well as a minimum revision. The fixture's integer comparison should not become permission for a caller to demand arbitrary waiting.
Dreamtsoft Editorial
A save-confirmation page and a broad analytics view can have different freshness requirements. Define the promise for each user action before sharing a routing policy.