Skip to main content
Postgres Read Replicas: Routing Reads Without Stale-Data Bugs

Postgres Read Replicas: Routing Reads Without Stale-Data Bugs

KU
· Founder & Chief Editor
• 6 min read•Updated •37 views

Adding a read replica cut primary load 60%, then support tickets rolled in about users not seeing their own edits. Replication lag turned into a correctness bug we had to route around.

Key takeaways

  • Route reads to a Postgres replica only when they can tolerate replication lag.
  • Streaming replication is asynchronous, so a user who reads right after writing can see old data.
  • Send each session's reads to the primary for a few seconds after its last write, monitor replay lag, and send everything else to the replica.

Route reads to a Postgres replica only when they can tolerate lag. Streaming replication is asynchronous, so a read right after a write can return stale data. Pin each session's reads to the primary briefly after its last write, monitor replay lag, and send other reads to the replica. You keep the capacity win without the bugs.

Verified against

PostgreSQL 18.6, against the postgresql.org Hot Standby, replication settings and recovery information function docs. Checked October 2026.

Our primary Postgres ran hot during business hours, almost all of it read queries from dashboards and list views. Adding a streaming replica and pointing reads at it cut primary CPU by more than half in an afternoon. Clean win, or so I thought until the next morning, when three users reported saving a profile change and then seeing the old value when the page reloaded.

That's the trap with read replicas. They solve a capacity problem and hand you a consistency problem, and the consistency problem shows up as bug reports, not graphs.

The lag is real and variable

Streaming replication is asynchronous by default. The primary commits, ships WAL to the replica, and the replica applies it, all with a delay. Usually that delay is a few milliseconds. Under a heavy write burst or a long-running query on the replica, it can spike to seconds. You have to watch it:

SQL
-- run on the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

One trap in that query: pg_last_xact_replay_timestamp() is the commit time of the last replayed transaction as generated on the primary. If the primary goes quiet, the number keeps growing even though the replica is fully caught up, so pair it with a check that the primary is actually writing, or read replay_lag from pg_stat_replication on the primary.

We alert when that crosses 500ms and page when it crosses 5 seconds. Most days it sits under 50ms. The problem isn't the average, it's that a user who just wrote data and immediately reads it back can land on a replica that's 800ms behind, and 800ms is plenty of time to reload a page.

Read-your-writes is the pattern that matters

Not all reads are equal. A dashboard showing aggregate numbers from an hour ago does not care about 800ms of lag. A user reloading the page right after saving their own edit cares intensely. So we don't route by "is this a read." We route by "does this read need to see the caller's recent write."

The rule we settled on: for a short window after a user performs a write, their subsequent reads go to the primary. Everything else goes to the replica. We track it with a per-session timestamp:

app/db/routing.py
WRITE_STICKY_WINDOW = timedelta(seconds=3)

def choose_connection(session, is_write):
    if is_write:
        session["last_write_at"] = utcnow()
        return primary_pool

    last_write = session.get("last_write_at")
    if last_write and utcnow() - last_write < WRITE_STICKY_WINDOW:
        return primary_pool   # read-your-writes: stick to primary
    return replica_pool

Three seconds covers our observed lag with a wide margin. After the window, the replica has caught up and the read is safe. This handled the profile-edit bug completely, and it kept 90% of read traffic on the replica because most reads aren't tied to a just-completed write.

Routing at the connection layer

Application-level routing works, but we didn't want every query call site making the decision. We put PgBouncer in front and used SQLAlchemy's binding to keep the choice in one place. The engines are configured with separate DSNs:

app/db/engines.py
engines = {
    "primary": create_engine("postgresql://app@pgbouncer:6432/prod"),
    "replica": create_engine("postgresql://app@pgbouncer:6432/prod_ro"),
}

The prod_ro alias in PgBouncer points at the replica. Pool sizing is its own topic, covered in our PgBouncer connection pooling guide. Keeping both behind the same PgBouncer means connection pooling is consistent and we don't blow out Postgres's max_connections on either box.

One sharp edge: never run writes against the replica by accident. Postgres rejects them, because transaction_read_only is always true during hot standby and cannot be turned off, so a stray write gets cannot execute UPDATE in a read-only transaction. The danger is in your code: if the ORM catches that error and silently retries on the primary, you hide a routing bug. We let that error propagate and alert on it, so a misrouted write fails loud and fast.

When lag itself becomes the alert

The nastier failure is a long report query on the replica holding a snapshot open for minutes. With hot_standby_feedback on, the primary's VACUUM cannot remove rows that query might still see, and the docs warn this "may result in undesirable table bloat" (the cleanup side of that is in our Postgres index bloat post). We kept hot_standby_feedback off (its default) on the analytics replica and left max_standby_streaming_delay at its 30-second default, accepting that a long analytics query gets canceled if it blocks replay for longer than that. That's the right trade for us: we'd rather kill a report than let lag grow unbounded.

Questions people ask

How do I check replication lag on a Postgres read replica?

On the replica, run SELECT now() - pg_last_xact_replay_timestamp(); to see how old the last replayed transaction is. On the primary, query pg_stat_replication and read replay_lag for each standby. The replica-side number overstates lag when the primary is idle, because no new commits arrive to replay. Alert on sustained lag, not single spikes, and page well before it reaches seconds.

Can a Postgres read replica accept writes?

No. A hot standby runs every transaction read-only, and SET transaction_read_only = off or BEGIN READ WRITE are rejected. Inserts, updates, deletes and DDL fail with a read-only transaction error. Writes must go to the primary. If you need writes on more than one node, that is logical replication or a multi-primary system, with very different trade-offs from a streaming read replica.

What is read-your-writes consistency?

It is the guarantee that after you write something, your own next read sees it. Async replicas break it, because the write lands on the primary and the read may hit a replica that has not replayed it yet. The usual fix is session stickiness: route a session's reads to the primary for a few seconds after it writes, longer than your observed lag. For failover behavior under lag, see replication lag and failover practice.

The call we'd make

Read replicas are the correct first move when reads dominate your load, which for most CRUD apps they do. But do not treat "route reads to the replica" as a global switch. The read-your-writes window is the piece that separates a clean scaling win from a slow drip of stale-data tickets. Implement the sticky window before you flip any traffic over, watch pg_last_xact_replay_timestamp from day one, and keep at least one thing on the primary that fails loudly if a write ever lands on the standby. If you skip the consistency work, you'll ship the capacity win and the correctness bug in the same deploy.

Sources

Explore topics:Infrastructure
React
Share this post
Cite this article
Plain text

Kiril Urbonas (2026). Postgres Read Replicas: Routing Reads Without Stale-Data Bugs. DevOpsNess. Updated 2026-10-31. https://www.devopsness.com/blog/postgres-read-replicas-routing-reads

Markdown

[Postgres Read Replicas: Routing Reads Without Stale-Data Bugs](https://www.devopsness.com/blog/postgres-read-replicas-routing-reads) (Kiril Urbonas, DevOpsNess, updated 2026-10-31)

KU

Kiril Urbonas

Founder & Chief Editor

604 articles
View all articles by Kiril Urbonas

You might have missed

Evergreen posts worth revisiting.