PostgreSQL writes succeed but reads lag: locate the reader target

The symptoms

• •An INSERT or UPDATE returns a row count or RETURNING value indicating success, but a follow-up SELECT executed by the same or a different client returns the pre-write value.
• •Read-after-write inconsistencies appear only for traffic routed to specific application instances, regions, or service names, while others see fresh data immediately.
• •pg_stat_replication shows replica replay lag that matches the observed read staleness window, while the primary's commit timestamp is current.
• •Application connection strings, ORM data source names, or proxy routing rules resolve to different hostnames or ports for write and read operations despite using the same database name.
• •Health checks report a green primary while dashboard queries, reporting jobs, or read replicas return data missing the latest committed rows.

Likely causes

• •The application routes writes to the primary hostname or writer endpoint and reads to a read replica hostname, standby, or read-only pool that has not yet replayed the latest WAL.
• •A connection pooler, service mesh, or driver is configured with separate writer and reader pools pointing at different backends, and the read pool silently fell back to a stale node after a failover.
• •ORM or framework read preferences (for example, read-only mode, replica selection, session read-only flag) are steering SELECT statements to a non-primary target by default.
• •A caching layer in front of PostgreSQL returns previously cached query results for the read path, while writes bypass the cache and only invalidate a subset of keys.
• •Logical replication or Change Data Capture consumer is behind, so downstream read models, search indexes, or materialized views reflect older state than the primary.

First ten minutes

1. 01Confirm the failure surface: capture the write SQL, the read SQL, the connection identifiers used for each, and the row identity (primary key) involved in the discrepancy.
2. 02Determine the reader target the application is actually using for the lagging SELECT by inspecting the connection string, driver options, or pool configuration in code or environment.
3. 03Compare the writer target hostname, port, and role to the reader target hostname, port, and role to verify whether they resolve to the same PostgreSQL backend.
4. 04Check pg_stat_replication and pg_stat_wal_receiver for replay lag on any standby that the reader target could resolve to, and record the replay_lsn and replay_lag values.
5. 05Decide whether the lag is bounded by replication, by an in-process or external cache, or by a downstream consumer before proposing any tuning change.

Evidence to collect

• •Application-side connection details for both the failing write and the lagging read, including host, port, database name, user, and any driver- or ORM-level read/write routing flags.
• •PostgreSQL-side identifiers such as backend PID, application_name reported by pg_stat_activity, client_addr, and the query text from the failing SELECT and the succeeding INSERT or UPDATE.
• •Replication state from pg_stat_replication on the primary and pg_stat_wal_receiver on the standby, including state, sync_state, replay_lsn, and replay_lag.
• •Cache or proxy configuration excerpts relevant to the reader path, including TTL, key patterns, invalidation rules, and replica selection policy.
• •Timestamps of the successful commit (xact_start, commit timestamp via pg_xact_commit_timestamp or pg_last_committed_xact) on the writer target and the observation time on the reader target.

Where to look

• •At the application configuration boundary where the writer DSN or URL and the reader DSN or URL are defined, including environment-specific overrides and feature flags that switch read routing.
• •At the driver or ORM boundary where session-level read-only flags, replica selection hints, or lazy connection establishment can steer a SELECT to a non-primary backend.
• •At the cluster boundary between primary and standbys by inspecting pg_stat_replication on the primary and pg_stat_wal_receiver on each standby for replay position and lag.
• •At the caching boundary between the application and PostgreSQL, including in-process caches, sidecar caches, and query result caches that may serve stale rows for the reader path.
• •At the change data capture or logical replication boundary where downstream read models, search indexes, or materialized refresh jobs could lag behind the primary commit.

Diagnostic steps

1. 01From a session on the suspected reader target, run SELECT pg_is_in_recovery(); to determine whether the endpoint is a standby receiving WAL rather than the writable primary.
2. 02On the writer target, run SELECT pid, application_name, client_addr, state, query, xact_start FROM pg_stat_activity WHERE query ILIKE '%<table_or_keyword>%'; to identify the backend that handled the successful write.
3. 03On the primary, run SELECT pid, application_name, client_addr, state, write_lsn, replay_lsn, replay_lag, sync_state FROM pg_stat_replication; to measure standby replay progress and lag for each replica.
4. 04On each candidate reader, run SELECT pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn(), pg_last_xact_replay_timestamp(); to determine the high water mark a read on that node would observe.
5. 05Run an explicit SELECT against the writer target using the same primary key to confirm the row is present there; if it is present on the writer and missing or older on the reader, the reader target is the cause.
6. 06Compare the read SQL plan and any session settings such as default_transaction_read_only or role attributes that might cause a redirected or read-only transaction on the reader target.

Common mistakes

• •Tuning synchronous_commit, wal_level, or replication lag thresholds before confirming that the application is actually reading from a replica at all, which wastes time when the read path is hitting the primary.
• •Assuming the read and write targets are identical because both connection strings reference the same logical database name without verifying the host, port, or cluster role behind them.
• •Increasing cache TTLs or disabling cache invalidation in an attempt to make reads appear fresh, which only masks the routing problem and risks serving unrelated stale data.
• •Adding application-level retries on the read path without first identifying the reader target, which can amplify load on a lagging replica and delay recovery from the underlying routing fault.
• •Conflating logical replication lag with physical streaming replication lag; the diagnostic queries and remediation differ, so the boundary must be named before acting.

Safe fixes

• •If the reader target is a streaming replica and lag is bounded, route latency-sensitive read-after-write traffic to the primary hostname until the read replica's replay_lsn has caught up to the write's commit LSN.
• •If the application reads through a cache, invalidate the specific cache key for the affected row or entity after the write commits, and verify that the invalidation covers the same key pattern the read uses.
• •If the ORM or driver has a read-replica preference, disable the preference for the affected code path or set it to prefer the primary, then re-run the read-after-write check from the same client.
• •If a load balancer or pooler is selecting the reader target, pin the affected session or endpoint to the writer pool and confirm subsequent reads observe the committed row.
• •After any change, repeat the diagnostic query that demonstrated the staleness and confirm the row is visible on the reader target within the expected replication or invalidation window.

Prove the fix

1. 01From the previously lagging reader target, run SELECT against the primary key for a freshly inserted or updated row and observe the post-write value within the documented replication or cache invalidation window.
2. 02On the primary, capture pg_current_wal_lsn() and the commit LSN of the write, then verify on the reader that pg_last_wal_replay_lsn() has advanced past that LSN before the read returns the new row.
3. 03Run the original write-and-read sequence end-to-end from the affected application instance and record that both the write success indicator and the follow-up read observe the new state with no manual intervention.
4. 04Monitor pg_stat_replication replay_lag and application-side read-after-write latency for a representative interval and confirm that read-after-write failures stop occurring on the previously affected path.

Prevention and next steps

• •Define a single source of truth for connection endpoints and document the writer and reader hostnames, ports, and roles so routing differences are explicit and reviewable.
• •Add an integration check that performs a write followed by a read from the same logical client path and fails the build if the read returns the pre-write value.
• •Monitor replay_lag and downstream consumer lag with thresholds that reflect the application's read-after-write tolerance, and alert when lag exceeds the tolerated window.
• •Audit driver, ORM, and pooler configuration for read-only or replica-selection flags whenever a dependency is upgraded, and require that any such change be documented in the change log.

Safe commands and checks

SELECT pg_is_in_recovery(); -- identifies whether the current target is a standby receiving WAL rather than a writable primary.
SELECT pid, application_name, client_addr, state, query, xact_start FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start DESC; -- locates the backend handling the suspect write or read and its client address.
SELECT pid, application_name, client_addr, state, write_lsn, replay_lsn, replay_lag, sync_state FROM pg_stat_replication; -- measures per-replica replay position and lag from the primary.
SELECT pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn(), pg_last_xact_replay_timestamp(); -- reports the high water mark a reader on a standby would observe for replicated changes.
SELECT current_setting('default_transaction_read_only'), current_user, session_user, current_database(); -- reveals session or role attributes that could redirect or constrain the read path.