Postgres Read Replicas: Routing Reads Without Stale-Data Bugs

Postgres Read Replicas: Routing Reads Without Stale-Data Bugs#

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.

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:

-- 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.

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:

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:

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.

When lag itself becomes the alert#

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.

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.