Route PostgreSQL Reads Without Breaking Read-Your-Writes Consistency

A distributed API sends reads to PostgreSQL hot standbys. Capacity improves, but users update a profile and immediately see the previous value because asynchronous WAL replay has not made the transaction visible on the chosen replica.

The reliable route is not the longest checklist; establish the failing boundary, keep one clean reproduction, and change one layer at a time until the evidence lets you deploy PostgreSQL streaming replicas with explicit lag budgets, primary routing after writes, WAL retention, promotion tests, and measurable RPO/RTO.

Read the failure at the right layer

• The router treats every SELECT as replica-safe even when the request requires read-your-writes or a locking read.
• Replication delay is summarized by one time column without checking byte positions, replay state, or workload idleness.
• A replication slot retains WAL during an unhealthy standby until the primary disk fills.

The branches are ordered to protect the strongest evidence around this possibility: the router treats every SELECT as replica-safe even when the request requires read-your-writes or a locking read. The observed scope and logs—not a familiar-looking error screen—decide which one applies.

Distributed storage trades one machine’s limits for routing, replication, failover, and consistency decisions; a healthy cluster can still return stale data, scatter a query, or lose an acknowledged write within a documented failure window; in this guide, the practical goal is to deploy PostgreSQL streaming replicas with explicit lag budgets, primary routing after writes, WAL retention, promotion tests, and measurable RPO/RTO.

Build a clean diagnostic record

1. Name the consistency requirement for each read path: stale-tolerant, monotonic, read-your-writes, or primary-only.
2. Inspect pg_stat_replication positions and state, plus replay position on each standby; interpret lag columns according to PostgreSQL’s documented idle behavior.
3. Monitor WAL retained by slots, primary disk headroom, replay conflicts, and long standby queries.
4. Exercise promotion and client reconnection in staging with the same DNS, pool, and transaction behavior used in production.

The sequence moves from observation toward intervention. Preserve the result of the final check—exercise promotion and client reconnection in staging with the same DNS, pool, and transaction behavior used in production—because it provides a useful comparison after the repair.

Repair the cause—not the message

1. Pin a session or request to the primary after a write until its commit position is visible on the selected replica, or keep consistency-sensitive reads on primary.
2. Remove lagging replicas from the read pool when their replay position exceeds the path’s staleness budget.
3. Set WAL retention alerts and a documented policy for advancing, recreating, or dropping abandoned slots.

Before applying “Pin a session or request to the primary after a write until its commit position is visible on the selected replica, or keep consistency-sensitive reads on primary,” name its rollback point and the evidence that will count as success. Afterward, repeat the original request and specifically check whether you can run a write-then-read probe through the real router and record which server answered each step; a changed symptom at that point is new evidence, not permission to make several more changes at once.

-- Primary
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_byte_lag,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Standby
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

Verification checklist

• Run a write-then-read probe through the real router and record which server answered each step.
• Pause replay and confirm the replica is drained before stale data violates the defined budget.
• Promote a standby and measure data loss, client recovery time, old-primary fencing, and replica reattachment.

One successful refresh is not closure. Keep the incident open until you can also pause replay and confirm the replica is drained before stale data violates the defined budget, adjacent paths have not regressed, temporary diagnostics are gone, and another operator can explain what changed.

Prepare a useful escalation if the boundary is outside your control

Record topology, versions, consistency settings, request or transaction identity, routing key, replication position, failure timeline, and recovery objective; test promotion and reconfiguration with production-shaped data before relying on them during an outage; include the result of this first observation: name the consistency requirement for each read path: stale-tolerant, monotonic, read-your-writes, or primary-only.

State what was tested, including the result of “Name the consistency requirement for each read path: stale-tolerant, monotonic, read-your-writes, or primary-only,” and what changed between attempts; evidence tied to that observation is safer and more actionable than granting broad access or sending an unnecessary full database export.

Tempting moves to avoid

• Do not call asynchronous replicas strongly consistent because normal lag is usually small.
• Do not fail over by promoting a standby without fencing the former primary.

Primary references

• PostgreSQL high availability and replication — official reference consulted for this guide.
• PostgreSQL replication statistics — official reference consulted for this guide.

Editorial note: The scenario above illustrates how to approach “Pin a session or request to the primary after a write until its commit position is visible on the selected replica, or keep consistency-sensitive reads on primary”; it is a documented example, not a claim about a reader’s server, so verify the cited documentation, take the appropriate backup, and follow the real environment’s access and change-control rules.