PostgreSQL Replica Lag: Fix Read-After-Write Surprises

PostgreSQL replica lag can make a successful update appear to disappear. The write commits on the primary, but the next request reads from a replica that has not replayed that change. Refreshing the page may seem to fix the problem, which makes the bug look like unreliable application state rather than a consistency decision.

The solution is not always to eliminate replicas. Decide which operations require read-after-write consistency, observe where requests actually go, and give those operations a suitable routing policy. PostgreSQL's hot standby documentation describes the standby behavior that underlies this distinction.

Confirm that the symptom is a stale read

Create a synthetic record with an unmistakable revision number. Update it through the application's normal write path, wait for the success response, and immediately retrieve it through the normal read path. Record the request identifier, committed revision, returned revision, and database role used by each request.

Do not use a real customer's private record as a debugging fixture. Also avoid logging complete database connection strings. You need to know whether the target was a primary or replica, not expose credentials or internal hostnames in a public issue.

If the primary also returns the old value, investigate transaction boundaries, caching, and application logic. A replica explanation is not proven simply because replicas exist. An ORM identity map, API cache, or background update can produce a similar symptom.

Inspect replay progress carefully

PostgreSQL provides administrative functions for observing WAL and recovery state in its system administration documentation. Choose functions that are appropriate to the server role and version. A function available on the primary may not provide the same meaning on a standby.

WAL positions help compare progress, but do not treat every byte difference as a fixed number of seconds. Write volume varies. A time-based indicator may also be misleading on an idle database: an old last-replay timestamp can simply mean that no new transaction has arrived.

Collect several observations and compare them with known writes. Record whether replication is paused, whether the connection is healthy, and whether replay is progressing. Do not repeatedly run an expensive diagnostic query under heavy incident load without understanding its cost.

Separate routing from replication

Find the code or proxy rule that selects the read target. Some applications use a read pool for every SELECT, including the confirmation query after a write. Others select a target by endpoint, transaction, tenant, or session. Document the actual rule rather than relying on a variable called read_only.

A write transaction's subsequent queries can run on the same primary connection, but that does not automatically control a later HTTP request. Once the transaction ends and the user clicks another page, the application may choose a replica again. Consistency across requests needs an explicit design.

Check caches too. Sending a request to the primary won't help if a stale application cache answers first. Include the returned revision in the test response where appropriate, and inspect the cache's invalidation behavior without exposing internal details to ordinary users.

Choose a bounded read-after-write policy

One simple option is to read from the primary for the confirmation flow and a short, controlled session period after a write. That increases primary load, so measure the affected traffic rather than switching every read indiscriminately.

A more explicit design carries a consistency requirement to the read path. For example, the application can record that a session needs at least a particular committed revision. If the selected replica cannot satisfy it within the allowed time, the application falls back to the primary or returns a clear retryable outcome.

Do not expose raw database positions as an unvalidated client-controlled routing authority. Any consistency token needs an appropriate trust boundary. Also apply an overall deadline: waiting forever for a lagging replica turns a consistency fix into a latency incident.

Test failure and recovery

In a disposable environment, delay or pause replay using your supported operational procedure. Run the synthetic write-and-read sequence. Verify that the user sees the updated revision, a deliberate pending state, or a bounded fallback rather than an apparently successful stale value.

Resume replay and check that the application returns to its normal read policy. Test a replica restart, an unavailable replica, and a primary failover separately. A role change may invalidate assumptions in the connection pool or routing layer.

Make the acceptance test precise: “After a successful profile update, this confirmation endpoint returns that revision or a later one within the request deadline.” That is stronger than “replication lag is usually low.” It describes the user-visible contract the application can actually verify.

Operational pitfalls

Reducing lag by adding resources can help, but it does not remove the need for a consistency policy. Long-running standby queries, network interruptions, replay constraints, and bursts of writes can still change behavior. Investigate the particular bottleneck rather than treating hardware as a universal cure.

Avoid sending all reporting queries to the primary during a lag alarm. That can increase load on the component whose writes must remain reliable. Instead, classify endpoints by consistency need and defer non-essential reporting when appropriate.

Use SQL Formatter to make a redacted diagnostic query easier to review. It does not measure lag or prove a query is safe. Our API timeout guide helps define the request budget around a bounded fallback.

Conclusion

Replica lag is a normal possibility in an asynchronous read architecture, but surprising stale reads need not be normal product behavior. Verify the write, observe the read target, and define a bounded consistency policy for the operations that need it.