Postgres Read Replicas: Routing Reads Without Stale-Data Bugs
KU
Kiril Urbonas
5 days ago • 4 min read
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.
Our primary Postgres was pinned at 85% CPU during business hours, almost all of it read queries from dashboards and list views. Adding a streaming replica and pointing reads at it dropped the primary to 30% 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.
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:
-- run on the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;
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.
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:
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.
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:
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. 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 will reject them, but the error is cannot execute UPDATE in a read-only transaction, and if your ORM silently retried on the primary you'd hide a routing bug. We set default_transaction_read_only = on on the replica so any stray write fails loud and fast.
The nastier failure is a replica falling permanently behind because a report query on it holds a snapshot open for minutes, and hot_standby_feedback is on, which then bloats the primary. We turned hot_standby_feedback off on the analytics replica and instead set max_standby_streaming_delay = 30s, accepting that a long analytics query might get canceled if it blocks replay. That's the right trade for us: we'd rather kill a report than let lag grow unbounded.
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.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Users kept asking the same questions in slightly different words, and we paid full price every time. Semantic caching cut our LLM bill by a third.
A user got our support bot to recite its system prompt and then draft a refund it wasn't authorized to give. Two layers of guardrails, one on input, one on output, closed both holes.
Explore more articles in this category
The Backstage demo always wows leadership. Then six months later the catalog has 400 stale entries and nobody trusts it. Here's what got ours to actually stick.
We inherited 200-odd AWS resources built by hand over four years, with no state file anywhere. Here's how import blocks and a generation workflow got them under Terraform without a rebuild.
A single ALTER TABLE took a lock and stalled every write for 40 seconds during peak traffic. Expand-contract is how we stopped shipping outages.
Evergreen posts worth revisiting.
