In a read/write-split architecture where writes go to a primary database and reads are load-balanced across asynchronous read replicas, what concrete strategies can a team use to avoid serving a user stale data right after their own write, and what does each strategy cost?

## Why lag exists at all **Asynchronous replication** - the default mode for read replicas in essentially every mainstream relational database (MySQL, PostgreSQL streaming replication, etc.) - means the primary commits a write and returns success to the client without waiting for any replica to apply it. The replica receives the change afterward, over the network, via a replication stream (binlog events in MySQL, WAL segments in PostgreSQL), and applies it on its own schedule. - Under light load this lag is typically single-digit milliseconds and invisible in practice. - Under load spikes, long-running queries competing for the replica's resources, network partitions, or a replica falling behind because it simply can't apply changes as fast as they arrive, lag can grow to seconds or, in bad cases, minutes. A read/write-split architecture that blindly load-balances all `SELECT`s across replicas will, during any nonzero lag window, sometimes return data that doesn't yet reflect a very recent write - the canonical symptom being a user who just saved a change and, on the very next page load, sees the old value, which reads to them as data loss even though nothing was actually lost. ## Primary pinning after a write The most direct mitigation is **read-after-write consistency via primary pinning**: after a client performs a write, route that same client's subsequent reads to the primary (or to the connection that performed the write) for some bounded window - a few seconds, or for the remainder of the request/session - before letting them fall back to replica-routed reads. This is simple to reason about and guarantees the specific user who made the change sees it immediately, but it doesn't help a different user who reads the same data through a replica moments later, and it adds load back onto the primary exactly for the traffic that read/write splitting was meant to offload, so it has to be scoped narrowly (only the affected rows/user, only a short window) rather than applied broadly. ## Lag-aware routing A more general approach is **lag-aware routing**: continuously measure how far behind each replica is - commonly - via a small **heartbeat table** that the primary updates with a timestamp every second or so, which each replica then reflects with its own delay, letting the router compute 'this replica is currently 340ms behind' by diffing timestamps; - or via native replication position markers (PostgreSQL LSNs, MySQL GTIDs) compared between primary and replica. The router then excludes replicas whose lag exceeds a threshold from receiving read traffic, falling back to the primary (or to a less-lagged replica) if all replicas are over budget. This generalizes read-after-write consistency to any client, not just the one who wrote, but it costs real engineering: a monitoring/heartbeat mechanism, a routing layer that consults it on every read (or on a cached recent measurement), and a failure mode of its own - if lag monitoring itself lags or breaks, the system can either wrongly route to a stale replica or wrongly overload the primary by excluding replicas that are actually fine. ## Session or sticky consistency **Session or sticky consistency** takes a middle path: rather than tracking lag globally, pin a given user's session to a single replica (or the primary) for its duration, so that user always sees a monotonically advancing view of the data - they'll never see something 'go backward' between two of their own reads, even if their view is a few hundred milliseconds behind absolute real time. This is cheaper to implement (a hash of session/user ID to a replica, no lag telemetry needed) but only solves consistency within one user's session; two different users hitting two different replicas can still momentarily disagree about the current state, which is fine for most consumer-facing reads but not for anything requiring a single global source of truth at read time. ## Paying only where freshness matters The strategy that costs the least is simply not paying for strong consistency where it isn't needed: classify reads by how much staleness they can tolerate, and only route the freshness-sensitive minority (e.g., 'show me the order I just placed,' 'show my current account balance before I authorize a debit') through primary-pinning or lag-aware routing, while the freshness-tolerant majority (product listings, an activity feed, aggregate counts, search results) flow through plain replica load balancing with no special handling at all. A concrete real-world shape: a social app pins reads to the primary for the 2 seconds immediately following a user's own post (so they see their post appear instantly in their own feed), routes everyone else's view of that same feed through ordinary lag-tolerant replica reads (a few hundred milliseconds of staleness in someone else's feed is imperceptible), and reserves lag-aware routing specifically for a payments subsystem where even other users' reads of balance data must not be stale.