Read replicas are usually the first horizontal scaling step a team takes with their relational database. The primary handles writes; one or more replicas handle reads. Throughput scales for free — until the inevitable bug report comes in. "I just updated my email, but the profile page still shows the old one." Welcome to replication lag.
The replication topology gives you scaling. The routing decisions — which reads go to replicas, which reads must go to the primary — determine whether your users notice. This post is about the routing patterns that actually work in production.
Why Replication Lag Exists
Asynchronous replication is the default for almost every relational database in production. The primary commits the transaction and the replica applies it later. "Later" is usually milliseconds but can stretch to seconds or longer under load, and during certain operations (large schema changes, batch jobs) can stretch to minutes.
The lag is not a bug to be fixed; it is the cost of the throughput you bought. Synchronous replication (the primary waits for the replica to acknowledge) eliminates the lag but reintroduces a write latency penalty. Most teams pick async and route around it.
The Two Dangerous Reads
Two kinds of reads cause user-visible problems:
Read-your-own-writes. A user makes a write, then immediately reads back. If the read goes to a lagging replica, they see the old value.
Read-after-write within a flow. Code writes a row, then reads from another table that has a foreign key to it. If the read goes to a lagging replica, the row may not exist yet.
A simple "route reads to replica, writes to primary" implementation will fail both cases. You need rules.
Routing Patterns
Sticky-to-primary for a Window
After a write, route subsequent reads from the same user to the primary for some short window — typically a few seconds. The window is long enough to cover replication lag and short enough that most reads still go to replicas.
class ReplicaRouter
{
public function read(string $userId): Connection
{
if (Cache::get("recent-write:{$userId}")) {
return DB::connection('primary');
}
return DB::connection('replica');
}
public function recordWrite(string $userId): void
{
Cache::put("recent-write:{$userId}", true, 5); // 5 second window
}
}
Most production read/write splitting boils down to this pattern. It is simple, requires no database support, and covers the common case.
Bind to a Specific Connection per Request
A request that writes earlier reads from the primary for the rest of its lifetime. No per-user state needed; just remember within the request.
class ConnectionResolver
{
private bool $hasWritten = false;
public function getReadConnection(): Connection
{
return $this->hasWritten
? DB::connection('primary')
: DB::connection('replica');
}
public function markAsWritten(): void
{
$this->hasWritten = true;
}
}
Cleaner than the sticky-to-primary cache, but it does not survive across requests. A user who writes in one request and reads in a later request still has the staleness problem. The two patterns combine well.
Read with Causality Token
Some databases (including newer versions of PostgreSQL and most NoSQL engines) expose a token that represents "the state of the database at this commit." Pass it on subsequent reads; the database (or your routing) guarantees the read is at least as fresh as the token.
In PostgreSQL, this works with the WAL LSN (pg_current_wal_lsn()) and the pg_wait_for_replay_lsn() function. A read with a recent LSN waits on the replica until it has caught up.
// After write
$lsn = DB::selectOne('SELECT pg_current_wal_lsn() AS lsn')->lsn;
session(['db_lsn' => $lsn]);
// On next read
$lsn = session('db_lsn');
DB::connection('replica')->select('SELECT pg_wal_replay_wait_for_lsn(?)', [$lsn]);
$result = DB::connection('replica')->select('SELECT * FROM users WHERE id = ?', [$userId]);
More precise than time-based windows but adds complexity. Worth it when the precision really matters.
Operation-Class Routing
Classify reads by criticality. "Show the user their bank balance" goes to the primary. "Show the homepage feed" goes to the replica. The classification is in the application code, not in some clever router.
// In OrderRepository
public function findForCustomerView(string $id): ?Order
{
// Reads from primary — the customer just placed this
return DB::connection('primary')->table('orders')->find($id);
}
public function findForAnalyticsExport(string $id): ?Order
{
// Replica is fine — analytics tolerates a few seconds of lag
return DB::connection('replica')->table('orders')->find($id);
}
The downside is discipline. Every new query has to decide. The upside is correctness — no clever inference layer can be wrong.
Load Balancing Across Replicas
With multiple replicas, you need a strategy for distributing reads.
Random. Simplest, works when replicas are roughly equivalent.
Least connections. Pick the replica with the fewest open connections. Better when query latency varies.
Geographic. Route reads to the geographically nearest replica. Critical for multi-region setups.
Lag-aware. Avoid replicas with replication lag above a threshold. Useful when one replica is significantly behind (large transactions, network problems).
A proxy like PgBouncer (with custom routing rules), HAProxy, or AWS RDS Proxy can apply these strategies without changing the application. ProxySQL for MySQL has the most mature lag-aware routing.
When Replicas Become a Problem
Replicas are not free. Things that surprise teams:
- Replication lag during big writes. A
DELETE FROM logs WHERE created_at < ?that affects 10 million rows will cause every replica to lag while it applies the same delete. Plan around bulk operations. - Schema migrations. Some migrations require taking a replica out of rotation while it applies. Online schema change tools (pt-osc, gh-ost) help but are not free.
- Replica failover. When the primary fails, one replica is promoted. Existing connections to the old primary error out; the application needs to handle reconnection.
- Connection pool sizing. Total connections across primary and replicas can exceed the database's limit. Pool sizing has to account for the topology.
Honest Defaults
For most applications, the right starting point:
- One primary, two replicas (one for redundancy)
- Reads go to replicas by default
- Writes go to the primary, obviously
- After a write, route subsequent reads from the same session to the primary for 5 seconds
- Critical reads ("show the user their own balance") are explicitly routed to the primary
- Heavy reads (reports, exports) are explicitly routed to the replica
This handles the common cases without needing causality tokens or sophisticated routing infrastructure. When the application outgrows it, you can add LSN-based reads or move specific workloads to a dedicated replica.
Anti-Patterns
- All reads to replica, always. Looks clean, breaks user-visible features.
- Manual routing in every query. Looks safe, becomes a nightmare to maintain.
- Replicas as a substitute for caching. Replicas are still a database query. A cache is faster and cheaper for hot reads.
- Treating replication lag as a database problem. It is an application problem — the database is doing what you asked. The routing has to handle it.
The goal of read/write splitting is to get scaling without users noticing. The patterns here are how production systems actually achieve that — not by avoiding lag, but by designing around it.
Sizing up read replicas as the next step for a database that's running hot? We help teams pick the routing rules that make replication lag invisible to users. scopeforged.com
