Stale Reads on Read Replicas: MySQL vs PostgreSQL

The first read-replica incident at most companies follows the same script. Someone offloads reads to a replica, load graphs improve, everyone is pleased. Weeks later a user updates their profile, the next page renders the old value, and a bug report arrives that no one can reproduce, because by the time an engineer looks, the replica has caught up. Nothing was broken. The system did exactly what asynchronous replication does: it served the recent past.

This failure mode is engine-agnostic. MySQL and PostgreSQL replicas are both asynchronous by default, both are usually milliseconds behind, and both are occasionally seconds or minutes behind at exactly the wrong moment. What differs between the engines is the toolbox for managing staleness, and if you are moving from MySQL to PostgreSQL, your read-your-writes strategy needs translating, not just copying. Having built these guarantees on both engines, here is how the pieces map.

Why replicas are stale, and why "usually fast" is a trap

On MySQL, a committed write must be written to the binlog, shipped to the replica's relay log, and applied by the replica's SQL threads before a replica read can see it. On PostgreSQL, WAL must be streamed to the standby and replayed by its recovery process. Under normal load both paths take single-digit milliseconds, which is precisely what makes them dangerous: the application appears to have read-your-writes almost all the time, so nobody designs for the case where it does not. Then a schema migration, a bulk delete, a vacuum burst, or replica CPU saturation stretches lag to thirty seconds, and every code path that silently assumed freshness misbehaves at once. Worse, the misbehavior is scattered: a stale balance here, a missing comment there, nothing that reproduces once lag recovers, so the tickets close as unexplained until someone finally correlates them with the lag graph.

The two engines fall behind for slightly different reasons. MySQL apply lag historically came from single-threaded appliers; MySQL 8.x's parallel appliers with writeset ordering have narrowed that dramatically, though hot-row workloads still serialize. PostgreSQL replay is single-process but block-level and fast; its distinctive stall is the conflict between replay and long-running standby queries, where the standby delays replay (up to max_standby_streaming_delay) to let a report finish, and every reader on that standby silently reads older data meanwhile. Different mechanisms, identical lesson: staleness is a distribution with a long tail, and correctness decisions must be made against the tail.

Read-your-writes on MySQL: waits, pinning, and group replication

The classic MySQL primitive is the GTID wait. After a write, capture the transaction's GTID (or the session's gtid_executed), and before a dependent read on a replica, wait until the replica has applied it.

-- On the replica, before the dependent read:
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-77', 1);
-- returns 0 when the set is applied, 1 on timeout

Wrap that in your data access layer and you get causal reads on demand: pay the wait only on reads that need freshness, let bulk read traffic enjoy the replica cheaply. Proxy layers offer coarser versions: ProxySQL can route based on replication lag ceilings, and MySQL Router steers traffic within InnoDB Cluster. Group replication goes further with group_replication_consistency levels, where BEFORE makes a read wait for the group's preceding transactions and AFTER makes a write wait until it is applied group-wide, effectively letting you buy linearizable-ish behavior per session at a latency cost.

The bluntest tool is session pinning: after a session writes, route its reads to the primary for a window (or until the session ends). It requires no database features, survives any topology, and wastes primary capacity by sending it reads a replica could have served a few hundred milliseconds later. Most mature MySQL shops run a blend: pinning as the default guarantee, GTID waits where the data layer is sophisticated enough to use them.

Read-your-writes on PostgreSQL: LSN tokens and remote_apply

PostgreSQL's equivalent of the GTID wait is LSN arithmetic. After a write, capture the primary's WAL position; before a dependent replica read, check that the standby has replayed past it.

-- On the primary, after the write commits:
SELECT pg_current_wal_lsn();  -- e.g. '7D/A0E42D80'

-- On the standby, before the dependent read:
SELECT pg_last_wal_replay_lsn() >= '7D/A0E42D80'::pg_lsn AS caught_up;

Unlike WAIT_FOR_EXECUTED_GTID_SET, stock PostgreSQL has no built-in blocking wait for an LSN on a standby, so applications poll with a short sleep, fall back to the primary on timeout, or route the read to the primary immediately when the standby is behind the token. The pattern is the same causal-token design; only the ergonomics differ, and a small helper in your data layer hides the difference.

The server-side alternative is synchronous_commit = remote_apply with synchronous_standby_names configured: commits do not return until the synchronous standbys have replayed the transaction, making it visible to their readers. That buys read-your-writes on those standbys for every write, at the price of added commit latency and availability coupling, since commits stall if a synchronous standby stalls. Quorum syntax (ANY 1 of several standbys) softens the availability risk but weakens the read guarantee to whichever standby acknowledged. In practice I reserve remote_apply for narrow, correctness-critical write paths, set synchronous_commit per transaction rather than globally, and use LSN tokens or pinning for the rest. Session pinning itself works identically to MySQL: pgpool-II can do lag-aware routing, though most teams implement pinning in the application or connection layer, where the write-visibility knowledge already lives.

Measure lag where it hurts: at the application level

Whatever strategy you choose, you need to know the actual staleness distribution, and server metrics alone understate it. pg_stat_replication's replay_lag and MySQL's Seconds_Behind_Source both measure the database's view, not the application's, and both have blind spots (idle-period artifacts on one side, applier-position optimism on the other). The robust pattern on either engine is a heartbeat: write a timestamp row on the primary every second, read it through each replica path your application actually uses, and record the difference as a histogram in your metrics system. That number includes connection pools, proxies, and routing, is comparable across both engines during a migration, and gives you honest p99 staleness to design against. On the PostgreSQL side, standby-local measurement is also cheap: now() minus pg_last_xact_replay_timestamp() approximates staleness in one query, with the caveat that it grows meaninglessly on an idle primary, so the heartbeat conveniently fixes that too. How these signals fit into a full replication baseline is covered in PostgreSQL monitoring.

Translating a MySQL playbook to PostgreSQL

The mapping is close to mechanical. GTID waits become LSN token checks. ProxySQL lag-ceiling routing becomes lag-aware routing in your pooler, proxy, or application. Group replication's BEFORE and AFTER consistency levels have their nearest cousin in remote_apply on synchronous standbys. Session pinning is portable as-is. Two genuinely new items on the PostgreSQL side deserve attention: standby query conflicts, where hot_standby_feedback and max_standby_streaming_delay trade primary bloat against standby staleness and cancellations, and the fact that physical standbys are strictly read-only, so any lingering habit of "fix it directly on the replica" ends at migration. If you need selectively replicated, writable targets, that is logical replication territory, with different staleness characteristics again. For a wider view of how PostgreSQL approaches these tradeoffs, see the PostgreSQL overview.

How MonPG helps once you are on PostgreSQL

MonPG watches the PostgreSQL half of this problem, which after your migration is the half that matters. It tracks per-standby write, flush, and replay lag from pg_stat_replication as histories rather than snapshots, so the staleness tail your read-your-writes design depends on is a chart, not folklore. It correlates lag spikes with their usual causes: WAL generation bursts from bulk writes, replay stalls behind long standby queries, vacuum activity, and replication slot growth on the primary. When product asks whether reads can safely move to the standby, you answer with the observed p99 replay lag over the last month instead of a shrug. Start with the replication section of PostgreSQL monitoring to see what that evidence looks like.