ScalingDATABASE-SPECIFICCLOUD-SPECIFICSCALE-SPECIFIC

Read Replicas From the Application

Routing reads to a replica multiplies read capacity and introduces a window where the application can read data that is older than what it just wrote.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Which reads can safely go to a replica, and what breaks when they go to the wrong one?

The requirement

The primary database is saturated by read traffic — dashboards, list pages, exports — while writes are a small fraction of the load.

The obvious build

Point the application at a replica for all SELECT statements and at the primary for everything else. It is a connection-string change and read capacity multiplies.

Why it breaks

A user updates their profile and is redirected to a page that reads it back from a replica that has not received the change yet. The UI shows the old value and the user tries again (Eventual Consistency in Practice).

How it breaks in production

A user updates their profile and is redirected to a page that reads it back from a replica that has not received the change yet. The UI shows the old value and the user tries again (Eventual Consistency in Practice).
A read-modify-write sequence reads from a replica and writes to the primary, so the write is computed from stale data and silently overwrites a concurrent change (Optimistic Concurrency).
A long analytical query runs on a replica and, depending on the engine's configuration, either delays replication or is cancelled by conflicting replay — either way the lag graph is now a business problem.
Replication lag rises under write-heavy load, so exactly when the system is busiest, replica reads become most stale.
A transaction opened on a replica cannot write, so a code path that mostly reads and occasionally writes fails only on the write, in production, in a rare branch.
Failover promotes a replica and the application keeps writing to the old primary's address, or opens read connections to a node that no longer exists.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

A replica applies the primary's change stream and serves reads from its own copy. Replication is asynchronous by default: the primary acknowledges a commit before the replica has applied it, so there is always a window in which they differ (Replication and Read Scaling in Database Engineering).
Replication lag is that window measured in time. It is small under normal load and grows with write volume, large transactions, schema changes, and anything that makes the replica's apply loop slower than the primary's write rate (Replication Lag: Reads That Are Correct and Stale in Observability & Performance).
The application-visible consequence is a violation of read-your-writes: the same session can write successfully and then read a version of the world without that write.
Not every read is equally sensitive. A read that renders a dashboard tolerates seconds of staleness; a read that a write decision depends on tolerates none.
Read-modify-write is the dangerous pattern, because staleness turns into a lost update rather than into a slightly old display (The Lost Update, Step by Step in API Design).
Routing is an application concern: two pools, or one pool with a router that inspects the operation. Transactions must be pinned to one connection for their lifetime, and any transaction that might write must be on the primary.
Failover changes which node is primary. The application must discover that — through a DNS name the platform updates, a proxy, or a driver that follows topology — rather than holding a fixed address (Active-Passive Failover in Cloud & Infrastructure).

The window, and who falls into it

Replication lag is not an error state; it is the normal operating condition of asynchronous replication. The primary commits, acknowledges, and the replica applies the change a moment later. Everything the application does in that moment is exposed to the difference.

The user most likely to notice is the one who just wrote. They are, by construction, reading the exact rows that are most recently changed, immediately after changing them. Every other reader is reading data that has been settled for a while and will not notice a thing.

UserLLMAgentToolDataDecisionHumanGuardrail

Route by freshness requirement, not by keyword

DATABASE-SPECIFICPostgres shown: write positions are LSNs and pg_last_wal_replay_lsn() reports replay progress on a replica. MySQL with GTIDs uses WAIT_FOR_EXECUTED_GTID_SET; some managed and distributed engines expose a consistency token instead. The pattern — record the write position, require the replica to have reached it — is the same in all three.

Routing on statement type is the implementation that fails, because the category that matters is not "is this a read" but "how stale may this be, and does a write depend on it". Making that explicit at the call site costs a few characters and removes the entire class of bug.

Three mechanisms give read-your-writes, in increasing order of precision: a time-based window after a write, a position-based wait until the replica has caught up to the write, and simply returning the written resource so no read-back happens. Use the third where you can.

Explicit routing with a read-your-writes guarantee

1// Freshness is a property of the call site, not of the SQL.
2type Freshness = 'any' | 'fresh' | 'own-writes'
3 
4async function query<T>(sql: string, params: unknown[], f: Freshness, ctx: Ctx): Promise<T> {
5  if (f === 'fresh') return primary.query(sql, params)
6 
7  if (f === 'own-writes') {
8    // Option A (simple): pin this user to the primary briefly
9    // after any write. Window must exceed normal lag.
10    if (ctx.wroteWithin(3_000)) return primary.query(sql, params)
11 
12    // Option B (precise): wait for the replica to reach the
13    // write position recorded at commit time.
14    const lsn = ctx.lastWriteLsn
15    if (lsn) {
16      const caught = await replica.query(
17        'SELECT pg_last_wal_replay_lsn() >= $1 AS ok', [lsn])
18      if (!caught.rows[0].ok) return primary.query(sql, params)
19    }
20  }
21 
22  // Guard: lag above the tolerance means the replica is not
23  // usable for anything, not just for own-writes.
24  if (await replicaLagSeconds() > 5) return primary.query(sql, params)
25  return replica.query(sql, params)
26}
27 
28// Call sites state the requirement:
29listPublicArticles()    // query(..., 'any', ctx)      -> replica
30getMyProfile()          // query(..., 'own-writes', ctx)
31loadOrderForUpdate()    // query(..., 'fresh', ctx)     -> primary, in a txn
32 
33// Best of all: no read-back at all.
34// PUT /profile returns the updated resource, so the client
35// never asks the database a second time.

Option A is a heuristic — it works until lag exceeds the window, which is why the lag guard below it is not optional. Option B is a real guarantee, and it costs one extra round trip to the replica to check. The comment at the bottom is the cheapest fix available and is an API design decision rather than a database one (Response Contracts Are Not Database Rows in API Design).

Which reads go where

In practice the classification is quick, because most read volume is genuinely tolerant and most sensitive reads are few. The value of writing the table down is that it makes the sensitive cases explicit instead of leaving them to whatever the ORM decided.

Note the last two rows. A read that feeds a write decision is not a read for routing purposes, and an authorization check is a read whose staleness has security consequences.

Read	Staleness tolerated	Route to	Why
Public listing, search results	Seconds	Replica	High volume, nobody can tell.
Reports, exports, analytics	Minutes	Dedicated replica	Long queries must not affect user-facing reads.
A user reading their own recent write	None	Primary, or a position-checked replica	The one case lag reliably breaks (Eventual Consistency in Practice).
Read inside a transaction that may write	None	Primary	A replica cannot write; the transaction must be pinned.
Read-modify-write (balance, counter, version)	None	Primary	Stale input becomes a lost update (Optimistic Concurrency).
Idempotency-key lookup	None	Primary	A stale miss means the operation executes twice (Idempotency Keys).
Authorization / permission check	None	Primary	A revocation must take effect immediately (Where the Check Belongs).
Cache warm-up / background reconciliation	Minutes	Replica	No user is waiting; volume is high.

How to build it

Most important first.

Classify reads explicitly rather than by SQL keyword. The category is *how stale may this be*, and it belongs at the call site: readReplica(), readPrimary(), readFresh(afterWrite).
Route to the primary for: anything inside a transaction that may write, any read-modify-write, and any read immediately following a write in the same user flow.
Implement read-your-writes with one of three mechanisms: route that user to the primary for a short window after their write; record the write position and require the replica to have caught up to it; or return the written entity in the write response so no read-back is needed at all.
The third option is usually the best and the least discussed: if a write endpoint returns the resulting resource, the client does not need to read it back and the whole problem disappears for that flow (Response Contracts Are Not Database Rows in API Design).
Send genuinely tolerant workloads to replicas: reports, exports, search-index builds, admin listings, analytics. These are also the largest consumers of read capacity, which is why the pattern works.
Use a dedicated replica for heavy analytical queries so a long report cannot affect the replica serving user traffic.
Monitor lag and treat it as a routing input: above a threshold, route reads back to the primary or degrade the feature, rather than serving arbitrarily old data.
Size pools for both the primary and each replica separately, and remember that the total across instances is what the servers see (Connection Pools).
Test with induced lag. A replica that is always a millisecond behind in staging proves nothing about the behaviour of the code path (Test Against the Real Database).

What can go wrong

Failure modes

Read-your-writes violations reported as "the save did not work", leading to duplicate submissions and duplicate records.
Lost updates from read-modify-write across primary and replica — silent, and discovered as data inconsistency much later.
Lag spiking during a large backfill or migration, which is exactly when the application is least able to tolerate stale reads (Expand and Contract Migrations).
A framework that routes by statement type and sends a SELECT ... FOR UPDATE to a replica, where it cannot do what it says.
Replicas used as a durability strategy: an asynchronous replica can be behind at the moment of failure, so a promoted replica may be missing recent commits (RPO & RTO in Cloud & Infrastructure).
Total connection count doubling because every instance now holds a pool to the primary and one per replica.

What can race

Write to primary, read from replica: the classic race, and it is not rare — it is the normal case within the lag window (Eventual Consistency in Practice).
Read-modify-write split across replica and primary loses concurrent updates, because the read saw a version older than the one the write overwrites (Optimistic Concurrency).
During failover there is a window in which two nodes may accept writes, or in which no node does; the application must handle both as transient failures (Retries).
Two requests from one user can be routed to two replicas with different lag, so the second can see less recent data than the first — monotonicity is not free either.

Security

A replica holds the same data and needs the same access controls, encryption and audit as the primary. It is a second copy of everything sensitive (Database Security Properties in Security Engineering).
Use a read-only credential for replica connections so a routing mistake cannot write, and so a compromised read path cannot escalate to a write path (Least Privilege in Security Engineering).
Stale reads can bypass a permission change: a revocation written to the primary is not effective on a replica until it has replicated. Authorization checks belong on fresh data (Where the Check Belongs).

Misreads

"Replicas are eventually consistent, so it is fine — users will not notice." Users notice exactly one thing reliably: their own write not being there. That is the case replication lag breaks first.
"Route all SELECTs to a replica." A SELECT that a write depends on must be on the primary; a SELECT ... FOR UPDATE cannot be on a replica at all. The keyword is not the category.
"Replicas are a backup." They replicate deletions and corruption faithfully and quickly. Backups are a separate mechanism (Backup Strategy in Cloud & Infrastructure).
"Adding a replica reduces load on the primary." It reduces read load and adds replication work. If the primary was write-bound, you have made it slightly worse.
"Lag is small, so we can ignore it." Lag is small under normal conditions and grows under exactly the conditions — heavy writes, migrations, backfills — where correctness matters most.

Operating it

How you see it in production

Alert on replication lag with a threshold derived from the tightest freshness requirement of anything routed to that replica, not on a round number.
Count queries by routing decision — primary versus replica — so a routing change is visible and a regression is measurable.
Log and count read-your-writes fallbacks (reads forced to the primary after a recent write); a rising number means the window is mis-tuned.
Watch replica-specific error classes: cancelled queries from replay conflicts, and connection errors after a failover.
Compare load on primary and replicas. If the primary is still saturated, the reads you moved were not the expensive ones (The Slow Query Workflow in Observability & Performance).

What changes at 10x and 100x

Read replicas scale reads and do nothing for writes. When the write path saturates, the answer is different: batching, partitioning or sharding (Partitioning and Sharding in Database Engineering).
Each additional replica adds replication load to the primary and another copy that can lag. The returns diminish and the operational surface does not.
Cross-region replicas multiply lag by network distance, turning a millisecond-scale window into a much larger one and changing which reads are safe (Cross-Region Latency Is Physics, Not Configuration in Observability & Performance).
At high scale, a cache in front of the primary often beats an additional replica for the same workload — different consistency model, different failure mode, and worth comparing rather than assuming (Cache-Aside).

What this costs

Read capacity is bought with a consistency window that the application must handle explicitly. There is no configuration that removes it while replication is asynchronous.
Synchronous replication removes the window and puts replica acknowledgement on the write path, so every write pays for it and an unhealthy replica can stall writes.
Routing logic adds a decision to every query site. Routing by SQL keyword avoids that work and gets the dangerous cases wrong.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

DATABASE-SPECIFICPostgres streaming replication exposes lag as byte positions and a replay timestamp, and lets a session wait for a specific LSN; MySQL exposes seconds-behind-master and, with GTIDs, a wait-for-position function. A distributed SQL engine may offer bounded-staleness or consistent reads by token. The mechanism you use for read-your-writes depends entirely on which of these you have.
CLOUD-SPECIFICManaged offerings differ in what a "read replica" is: some are ordinary asynchronous replicas, others share storage with the primary and lag differently, and failover behaviour, endpoint naming and whether a reader endpoint load-balances for you all vary (Mapping Services Across Cloud Providers).
SCALE-SPECIFICBelow the point where the primary is genuinely read-saturated, a replica adds a consistency problem and buys nothing. Measure first (Why Is My API Slow?).

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.