Why do read replicas return stale data?
Read replica routing is the logic that decides, for each query, whether it runs on the primary or on a replica. Teams usually add it to take read load off the primary, but every routing rule is also a consistency decision. Sending a query to a replica means accepting that it may not see the latest committed data.
PostgreSQL's default streaming replication is asynchronous. The primary commits a transaction, writes it to its write-ahead log (WAL), and acknowledges the client. The WAL is then streamed to each replica, which writes and replays it. Replication lag is the time between the commit on the primary and the moment that change becomes visible on the replica. It always exists, because replay happens after the commit, and its size changes with load.
writes + must-be-fresh reads
┌─────────┐ ─────────────────────────────▶ ┌──────────┐
│ App │ │ Primary │
└─────────┘ └────┬─────┘
│ │ WAL stream (async)
│ lag-tolerant reads ┌────────────┴────────────┐
└───────────────────────────▶ ▼ ▼
┌────────────┐ ┌────────────┐
│ Replica A │ │ Replica B │
└────────────┘ └────────────┘
Lag grows in a few common situations:
- Bulk writes such as backfills, imports, or large
UPDATEs produce WAL faster than replicas can replay it. - Vacuum-heavy periods generate extra WAL, and the cleanup records can conflict with queries still running on the replica.
- Long-running replica queries can block replay. PostgreSQL either pauses replay until the query finishes or cancels the query, depending on configuration.
- Network trouble delays or interrupts the stream.
Lag is often under a second during normal traffic and can reach minutes during these events. Routing logic has to be correct when lag is large, not only when it is typical.
┌───────────────────────────┐
│ Application │
└─────────────┬─────────────┘
│ every query
▼
┌───────────────────────────┐
│ Router: fresh or stale? │
└──────┬─────────────┬──────┘
writes + │ │ lag-tolerant
must-be-fresh │ │ reads
reads ▼ ▼
┌─────────────────┐ ┌──────────────────────────┐
│ Primary │ │ Replica pool │
│ 1. commit │ │ │
│ 2. write WAL │ │ ┌────────┐ ┌────────┐ │
│ 3. ack client │ │ │Replica │ │Replica │ │
└────────┬────────┘ │ │ A │ │ B │ │
│ │ └───▲────┘ └───▲────┘ │
│ └──────┼───────────┼───────┘
│ │ │
└──────────────────┴───────────┘
4. WAL streamed asynchronously after the ack;
replica replay lag is variable, never zero
The router sends writes and must-be-fresh reads to the primary and lag-tolerant reads to replicas, which replay WAL only after the primary has already acknowledged the commit.
How does read replica routing work?
A routing layer sits between the application code and the database connections. It can live in the application's connection layer, in an ORM's multi-database support, or in a proxy or connection pooler between the application and PostgreSQL. In all three cases it does the same four things for each query:
- Classify the query. The router decides whether the query must run on the primary. Writes, anything inside a transaction that has written, and reads marked as must-be-current always go there. The classification can come from parsing the SQL, from the transaction state, or from an explicit annotation in application code. Annotations are the most reliable signal, because only the code knows how fresh a read needs to be.
- Pick a target. For a read that can use a replica, the router chooses one from a pool, typically round-robin or least-connections, and skips any replica currently marked unhealthy.
- Check freshness. A simple router trusts that any healthy replica is fresh enough. A safer one compares the replica's state with what the request needs: a maximum tolerated lag, or a WAL position (LSN) the replica must have replayed. If no replica qualifies, the read waits briefly or goes to the primary.
- Track health. A background check measures each replica's replay lag and connectivity and removes replicas from the pool when they fall too far behind.
Steps 1 and 3 are where stale-read bugs come from. Most of the rest of this article is about getting them right: classifying reads by how much staleness they tolerate, and using session state or LSNs so that a user's reads see their own writes.
What stale-read bugs show up in a monolith?
Most stale-read bugs in a monolith follow one of four patterns. Each starts with a write to the primary, followed by a read that lands on a replica before that write has been replayed.
| Pattern | Request flow | Visible symptom |
|---|---|---|
| Write-then-redirect-then-read | POST creates an order on the primary, responds 303, browser GETs /orders/42 from a replica |
404 or "not found" page for a record the user just created |
| Read-modify-write | Handler reads a row from a replica, changes one field, writes the whole row to the primary | Newer values from another request are silently overwritten |
| Background job | Transaction commits, job is enqueued, worker picks it up within milliseconds and queries a replica | Job fails with "record missing" or processes an old state |
| Cache fill | Cache miss reads a lagging replica and stores the result | Old value keeps being served for the full TTL, long after lag recovers |
The last two outlast the lag that caused them. A failed job may be retried against data that has since changed, or it may land in a dead-letter queue. A poisoned cache entry turns a two-second lag spike into a stale value that lasts as long as the TTL. Read-modify-write is the most dangerous of the four because nothing errors. The data is simply wrong afterward.
These bugs rarely appear before production. Development usually runs a single database, so every read hits the primary. Staging may have a replica, but with low write volume and no long-running queries, lag stays near zero. That window is shorter than the gap between a redirect and the next request. The race exists in staging, but lag is too small for the replica to lose it.
Which queries should go to a replica, and who decides?
The usual routing rule is "send every SELECT to a replica." It fails because a query being read-only says nothing about how fresh its data must be. A more useful classification is how much staleness each read can tolerate.
| Staleness tolerance | Typical queries | Where to route |
|---|---|---|
| Must be current | Reads that feed a write, auth and permission checks, data the user just changed | Primary |
| Bounded stale OK | Dashboards, listings, search results | Replica, with a lag limit |
| Arbitrarily stale OK | Analytics, exports, reports | Replica, or a dedicated reporting replica |
A permission check that reads a revoked role from a lagging replica becomes a security bug. A balance read that feeds an UPDATE becomes a lost write. Neither looks wrong in code review, because both are plain SELECTs.
For this reason, default to the primary and require an explicit opt-in for replica reads. Declare the opt-in at the call site or route level, using a decorator, a route attribute, or a context manager that wraps a block of reads. Inside that scope, the connection layer picks a replica. Everywhere else, queries go to the primary. With this default, the cost of forgetting an annotation is extra load on the primary instead of a correctness bug. Whoever adds the annotation is also stating, in code that reviewers can see, that the read tolerates lag.
This approach fits teams whose primary is the actual bottleneck and whose codebase mixes these three categories. If the primary has CPU and connection headroom, replicas add failure modes without adding capacity you need. If most of your traffic is must-be-current, very little can move to a replica, so you add the complexity and gain almost nothing in exchange.
import contextvars
from contextlib import contextmanager
from functools import wraps
_max_lag = contextvars.ContextVar("max_replica_lag_seconds", default=None)
@contextmanager
def replica_reads(max_lag_seconds):
"""Declare that reads in this block tolerate up to max_lag_seconds of lag."""
token = _max_lag.set(max_lag_seconds)
try:
yield
finally:
_max_lag.reset(token) # restore the outer scope's routing
def replica_ok(max_lag_seconds):
"""Decorator form, for a route handler or a query function."""
def decorate(fn):
@wraps(fn)
def wrapper(*args, **kwargs):
with replica_reads(max_lag_seconds):
return fn(*args, **kwargs)
return wrapper
return decorate
def get_connection(pool):
"""The only place that chooses primary vs replica."""
max_lag = _max_lag.get()
if max_lag is None:
return pool.primary()
# pool is app-defined; replica_within_lag returns None if no replica is fresh enough
return pool.replica_within_lag(max_lag) or pool.primary()
@replica_ok(max_lag_seconds=30) # listing: bounded staleness is acceptable
def list_products(pool): ...
def check_permission(pool, user_id): ... # no annotation: always primary
A framework-neutral routing scope built on contextvars. Reads go to the primary unless a decorator or context manager explicitly opts them into a replica with a maximum tolerated lag.
When should you use read replica routing?
Replica routing is worth its complexity when all of these are true:
- Reads are what's loading the primary. Check where the primary's time actually goes before adding replicas. If
pg_stat_statementsor your query monitoring shows that SELECTs account for most of the CPU, I/O or connection pressure, moving some of them elsewhere relieves the primary. If writes dominate, it won't. - A meaningful share of those reads tolerates lag. Listings, dashboards, search pages, reports and exports are good candidates. If the expensive reads are permission checks and read-modify-write paths, there is little you can safely move.
- Cheaper fixes are already done. Missing indexes, N+1 query patterns, and unbounded result sets are usually faster to fix than a routing layer is to build, and the fixes don't add a consistency model.
- Heavy queries interfere with transactional traffic. Long analytical queries or exports compete with short OLTP queries for CPU, I/O and buffer cache. A dedicated reporting replica isolates them, and those queries usually tolerate arbitrary staleness, which makes them the easiest workload to move.
- You can monitor lag and test for it. Routing is only as safe as the lag health checks and the lag-injection tests behind it, described later in this article.
A practical starting point is to route only the arbitrarily-stale category to a replica first, then audit bounded-stale endpoints one at a time.
When should you avoid read replica routing?
Skip it, or postpone it, in these situations:
- The primary has headroom. Replicas add failure modes such as lag, replay conflicts and routing bugs without adding capacity you need. Scaling the primary vertically is often simpler until it stops being an option.
- The bottleneck is writes. Every replica replays every write, so replicas do nothing for write throughput. A write-bound primary needs batching, schema changes, or partitioning of the write load instead.
- Most reads must be current. If nearly every read feeds a write, checks permissions, or shows the user data they just changed, almost nothing can move, and you pay the complexity cost for little gain.
- Correctness rules forbid any staleness on a path. Financial balances, inventory reservations and authorization data should be read from the primary, or the design should rely on synchronous replication with
remote_applyand accept its write-latency and availability costs. - You can't yet measure lag or test for it. Without replay-lag monitoring and tests that simulate a lagging replica, stale-read bugs show up as intermittent production incidents that are hard to reproduce.
Having standbys for failover does not mean you have to route reads to them. A standby can exist purely for high availability, with all application traffic on the primary.
How do you implement read-your-writes for a user session?
The simplest workable approach is time-window stickiness. When a request performs a write, the application records the time of that write in the user's session or a signed cookie. For every later request, middleware compares the current time with that timestamp. If the write is still inside the configured window, the request's reads go to the primary. Once the window expires, reads return to the replica pool. This targets the stale read users notice most: the one right after their own change. Other users' reads are not affected.
Choose the window from replay lag you have actually observed, not from a round number. Illustrative estimate, assumed inputs: suppose your monitoring shows replay lag with a p99 of 400 ms and occasional peaks near 1.5 s during batch jobs. Covering the peak and adding a 2x margin gives a 3-second window. With different measurements you would get a different window.
A fixed window is a heuristic and does not guarantee fresh reads, for two reasons:
- Lag can exceed the window. If a vacuum, a long transaction or network trouble pushes lag to 10 seconds, reads after second 3 can still return stale data. The middleware has no information that the replica is behind.
- A longer window shrinks the replica's share of reads. An active user who writes every few seconds stays pinned to the primary continuously. Raising the window to absorb rare spikes moves most of your engaged traffic back onto the primary, which reduces the benefit of having the replica.
Both problems come from using elapsed time to estimate replica state, which the database can report directly.
import express from "express";
import cookieParser from "cookie-parser";
import { Pool } from "pg";
const primary = new Pool({ connectionString: process.env.PRIMARY_URL });
const replica = new Pool({ connectionString: process.env.REPLICA_URL });
// Illustrative estimate: 1.5 s observed peak lag x 2 margin = 3 s. Derive yours from metrics.
const STICKY_WINDOW_MS = 3000;
const COOKIE = "last_write_at";
declare global {
namespace Express { interface Request { db: Pool; markWrite: () => void } }
}
const app = express();
app.use(cookieParser(process.env.COOKIE_SECRET)); // signed so clients cannot forge the timestamp
app.use((req, res, next) => {
// Tampered or missing cookie yields false/undefined, which becomes 0 (not pinned).
const lastWrite = Number(req.signedCookies[COOKIE]) || 0;
// Heuristic only: if lag exceeds the window, reads after it can still be stale.
req.db = Date.now() - lastWrite < STICKY_WINDOW_MS ? primary : replica;
req.markWrite = () =>
res.cookie(COOKIE, String(Date.now()), {
signed: true, httpOnly: true, sameSite: "lax", maxAge: STICKY_WINDOW_MS,
});
next();
});
// Writes always go to the primary, then start (or extend) the sticky window.
app.post("/profile", express.json(), async (req, res) => {
const userId = req.get("x-user-id"); // placeholder for real authentication
await primary.query("UPDATE users SET display_name = $1 WHERE id = $2", [req.body.name, userId]);
req.markWrite(); // must run before the response is sent so the cookie header is included
res.sendStatus(204);
});
// Reads use whichever pool the middleware chose for this session.
app.get("/profile", async (req, res) => {
const { rows } = await req.db.query("SELECT display_name FROM users WHERE id = $1", [req.get("x-user-id")]);
res.json(rows[0] ?? null);
});
Express middleware that records each write's time in a signed cookie and sends that user's reads to the primary until a fixed window, taken from observed replay lag, expires.
How does LSN-based consistency work in PostgreSQL?
PostgreSQL tracks replica state precisely, as a position in the write-ahead log. Every committed change has a log sequence number (LSN), and LSNs only move forward. After a write commits, call pg_current_wal_lsn() on the primary and store the result where the next read can find it: a signed cookie, the server-side session, or the payload of a background job. Before routing a read to a replica, query pg_last_wal_replay_lsn() on that replica. If the replica's position is greater than or equal to the stored LSN, the replica has replayed your write and the read is safe. If not, either poll for a short bounded time or send the read to the primary.
This mechanism works only if the unit of work never splits across servers. A transaction that writes must do all of its reads on the primary, because the replica cannot see uncommitted data. ORM lazy-loading can break this: a relation fetched after the transaction closes may go through the replica router. Pooling matters too. With session-mode pooling, "sticky" can mean one backend connection. With transaction-mode pooling, consecutive transactions can land on different backends, so stickiness has to live in your application's routing logic.
The guarantee has costs. Each replica read needs an extra round trip to check the LSN, unless you cache the replica's position for a few milliseconds. You also take on new code paths, such as wait, fallback, and missing-token handling, and each one needs tests.
import time
def write_and_capture_lsn(primary, sql, params):
with primary.cursor() as cur:
cur.execute(sql, params)
primary.commit() # capture after commit so the token covers durable data
cur.execute("SELECT pg_current_wal_lsn()::text")
lsn = cur.fetchone()[0]
primary.commit()
return lsn # store in a signed cookie, the session, or the job payload
def replica_caught_up(replica, lsn):
with replica.cursor() as cur:
# pg_lsn compares by WAL position. NULL (not a standby) counts as not caught up.
cur.execute(
"SELECT COALESCE(pg_last_wal_replay_lsn() >= %s::pg_lsn, false)",
(lsn,),
)
ok = cur.fetchone()[0]
replica.rollback() # end the implicit transaction; don't hold a snapshot
return ok
def choose_read_conn(primary, replica, lsn, max_wait=0.05, interval=0.01):
if lsn is None:
# Missing token: no recent write is known. This branch is a policy choice and needs a test.
return replica
deadline = time.monotonic() + max_wait
while True:
if replica_caught_up(replica, lsn):
return replica # the replica has replayed the write
if time.monotonic() >= deadline:
return primary # bounded wait expired, so fall back to the primary
time.sleep(interval)
A minimal Python router that captures pg_current_wal_lsn() after a commit, checks a replica's pg_last_wal_replay_lsn() against that value, and waits for a bounded time before falling back to the primary.
How should you measure replication lag and handle lagging replicas?
On the primary, pg_stat_replication has one row per standby. Three interval columns show where time goes. write_lag is how long the standby takes to write WAL to the OS. flush_lag is how long until that WAL is durable on disk. replay_lag is how long until the changes are visible to queries. Routing cares about replay_lag, because a replica can flush WAL quickly and still be slow to apply it. For a byte-based view, compare replay_lsn with pg_current_wal_lsn() using pg_wal_lsn_diff().
A common shortcut is to run now() - pg_last_xact_replay_timestamp() on the replica. This is misleading when the primary is idle. If no transactions commit, the timestamp stops advancing, and the lag appears to grow without limit even though the replica is fully caught up. Use the primary's view, or compare LSNs, instead.
Alert on replay lag, and include it in the health check your router or connection pooler uses. Above a threshold, take the replica out of the pool. Add it back only after lag stays below a lower threshold for a while, so a replica hovering near the limit doesn't flap in and out.
Decide ahead of time what happens when every replica lags. Falling back to the primary keeps reads correct, but it can push all replica traffic onto the primary at the moment it is already busy, which can turn a lag problem into an outage. Shedding or degrading reads that tolerate some staleness protects the primary. Only reads that need read-your-writes should go to the primary.
Lag also interacts with replay conflicts. Long replica queries can be cancelled when replay removes rows they still need. Enabling hot_standby_feedback prevents those cancellations, but it makes the primary keep dead rows longer, which causes table bloat.
What are the alternatives, and what do they cost in engineering time?
Each routing strategy trades consistency, write latency and availability differently. The largest cost is usually engineering time rather than hardware.
| Approach | Consistency guarantee | Write latency | Availability impact | Development cost |
|---|---|---|---|---|
| Primary by default, opt-in replicas | Strong, except on audited endpoints | Unchanged | Replica loss only degrades opted-in reads | Gradual endpoint audits |
| Every SELECT to replicas | None; stale reads anywhere | Unchanged | Replica loss breaks most reads | Low up front, high in bug triage |
| Time-window stickiness | Read-your-writes only if lag stays under the window | Unchanged | Primary absorbs post-write traffic | Low |
| LSN checks | Read-your-writes, enforced per request | Unchanged | Falls back to primary when replicas lag | Moderate: session plumbing |
Synchronous, remote_apply |
Commit is visible on the synchronous standby | Adds a round trip plus replay on every commit | Writes stall if the required standby is unavailable | Low code, high operational cost |
| Cache first | Bounded by TTL and invalidation | Unchanged | Cache outage shifts load to the database | Moderate, and staleness persists |
A cache does not fix stale reads. It moves them to a second store with its own invalidation bugs.
With primary by default, the development cost is spread out over time. Each endpoint moves to a replica only after someone confirms it tolerates lag. That work is slow, but it is reversible and each change can be reviewed on its own.
Lag-injection tests keep the policy from eroding over time. In staging, attach a test replica and run SELECT pg_wal_replay_pause(); on it. Confirm the pause with SELECT pg_is_wal_replay_paused();. Then run integration tests that write and immediately read, and finish with SELECT pg_wal_replay_resume();. If an endpoint was wrongly opted in, it returns stale data every time the test runs, so the regression fails in CI instead of surfacing as a production incident.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to read replica routing stale reads, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.