Read/Write Splitting in Production — Scaling Reads Without Sharding

Introduction

Read/write splitting routes database writes to a primary instance and reads to replicas, scaling read capacity by 5–10x without sharding complexity. Replication lag is the core challenge: a replica that lags 500ms behind the primary can return stale data immediately after a write. This guide covers practical implementation with lag monitoring, sticky sessions for read-after-write consistency, and circuit breakers for replica failures.

Why Read Replicas Scale Read-Heavy Systems

Most production applications have 10:1 or higher read-to-write ratios. Routing all traffic to one primary instance creates a bottleneck that vertical scaling eventually cannot solve. Read replicas receive a stream of WAL (Write-Ahead Log) changes from the primary and apply them asynchronously, typically within milliseconds.

The key trade-off: replicas may serve data that is slightly behind the primary. For most reads — product listings, user profiles, analytics dashboards — this is acceptable. For read-after-write scenarios — confirming a just-submitted form — you need sticky sessions or primary reads.

# Python: simple read/write routing with psycopg2
import psycopg2
import psycopg2.pool
 
primary_pool = psycopg2.pool.ThreadedConnectionPool(
    minconn=2, maxconn=20,
    host="primary.db.example.com", dbname="myapp"
)
 
replica_pool = psycopg2.pool.ThreadedConnectionPool(
    minconn=2, maxconn=40,
    host="replica-1.db.example.com", dbname="myapp"
)
 
def execute_write(query, params=None):
    conn = primary_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute(query, params)
            conn.commit()
            return cur.fetchall() if cur.description else None
    finally:
        primary_pool.putconn(conn)
 
def execute_read(query, params=None, use_primary=False):
    pool = primary_pool if use_primary else replica_pool
    conn = pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()
    finally:
        pool.putconn(conn)

Replication Lag Monitoring

Lag is measured by comparing the primary WAL position with what the replica has applied. Query these PostgreSQL system views continuously:

-- Run on primary: see lag per replica
SELECT
  application_name,
  client_addr,
  state,
  write_lag,
  flush_lag,
  replay_lag,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_size
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;
 
-- Run on replica: see lag from this instance
SELECT
  EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())) AS lag_seconds,
  pg_is_in_recovery() AS is_replica;

Acceptable lag thresholds:

Under 10ms: excellent, read-after-write safe
10ms to 100ms: good for most read use cases
100ms to 1s: avoid for user-facing read-after-write
Over 1s: replica is degraded, route to primary

import time
import threading
 
class ReplicaLagMonitor:
    def __init__(self, replica_pool, check_interval=10):
        self.replica_pool = replica_pool
        self.lag_ms = 0
        self.healthy = True
        self._start_monitoring(check_interval)
 
    def _start_monitoring(self, interval):
        def monitor():
            while True:
                self._check_lag()
                time.sleep(interval)
        t = threading.Thread(target=monitor, daemon=True)
        t.start()
 
    def _check_lag(self):
        conn = self.replica_pool.getconn()
        try:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())) * 1000"
                )
                row = cur.fetchone()
                self.lag_ms = float(row[0] or 0)
                self.healthy = self.lag_ms < 1000
        except Exception as e:
            self.lag_ms = float('inf')
            self.healthy = False
        finally:
            self.replica_pool.putconn(conn)
 
    def should_use_replica(self, max_lag_ms=200):
        return self.healthy and self.lag_ms < max_lag_ms

Sticky Sessions for Read-After-Write Consistency

A user submits a profile update, then immediately loads their profile page. If the replica lags 50ms, they see the old data. Sticky sessions route that user to the primary for a short window after any write:

import time
 
# Per-session routing state
session_primary_until = {}  # session_id -> timestamp
 
def handle_profile_update(session_id, user_id, new_name):
    execute_write(
        "UPDATE users SET name = %s WHERE id = %s",
        (new_name, user_id)
    )
    # Force primary reads for next 500ms
    session_primary_until[session_id] = time.time() + 0.5
 
def handle_profile_read(session_id, user_id):
    force_primary = time.time() < session_primary_until.get(session_id, 0)
    return execute_read(
        "SELECT * FROM users WHERE id = %s",
        (user_id,),
        use_primary=force_primary
    )

The window (500ms here) should exceed your typical replication lag by 3–5x to provide safety margin.

Circuit Breaker for Replica Failures

When a replica becomes unavailable or excessively lagged, automatically fall back to the primary rather than returning errors:

import time
 
class ReplicaCircuitBreaker:
    CLOSED = "closed"
    OPEN = "open"
    HALF_OPEN = "half_open"
 
    def __init__(self, failure_threshold=5, reset_timeout=30):
        self.state = self.CLOSED
        self.failures = 0
        self.failure_threshold = failure_threshold
        self.reset_timeout = reset_timeout
        self.last_failure = 0
 
    def call(self, replica_fn, fallback_fn):
        if self.state == self.OPEN:
            if time.time() - self.last_failure > self.reset_timeout:
                self.state = self.HALF_OPEN
            else:
                return fallback_fn()
 
        try:
            result = replica_fn()
            if self.state == self.HALF_OPEN:
                self.state = self.CLOSED
                self.failures = 0
            return result
        except Exception:
            self.failures += 1
            self.last_failure = time.time()
            if self.failures >= self.failure_threshold:
                self.state = self.OPEN
            return fallback_fn()
 
breaker = ReplicaCircuitBreaker()
 
def read_user(user_id):
    return breaker.call(
        replica_fn=lambda: execute_read("SELECT * FROM users WHERE id = %s", (user_id,)),
        fallback_fn=lambda: execute_read("SELECT * FROM users WHERE id = %s", (user_id,), use_primary=True)
    )

Drizzle ORM Read Replica Configuration

Drizzle lets you explicitly choose the database instance per query, making routing transparent in your application code:

import postgres from 'postgres'
import { drizzle } from 'drizzle-orm/postgres-js'
import { users } from './schema'
import { eq } from 'drizzle-orm'
 
const primaryClient = postgres(process.env.DATABASE_URL_PRIMARY)
const replicaClient = postgres(process.env.DATABASE_URL_REPLICA)
 
const primaryDb = drizzle(primaryClient)
const replicaDb = drizzle(replicaClient)
 
// Writes always go to primary
async function updateUser(userId, data) {
  return primaryDb.update(users)
    .set(data)
    .where(eq(users.id, userId))
    .returning()
}
 
// Reads route to replica by default
async function getUser(userId) {
  return replicaDb.select()
    .from(users)
    .where(eq(users.id, userId))
}

When NOT to Use Read Replicas

Read replicas are not appropriate for every read. Avoid them when:

You need read-after-write consistency without sticky sessions (financial confirmations, inventory checks)
The query is part of a larger transaction that already holds a primary connection
Your replica lag is consistently above 500ms — the data is too stale to be useful
You are checking for uniqueness before a write (two replicas may both return "not found")

Use replicas for: product listings, user profile reads, analytics dashboards, report generation, and search result pages — all scenarios where a few hundred milliseconds of staleness has no business impact.

Key Takeaways

Read replicas scale read-heavy workloads 5–10x without schema changes or sharding complexity
Replication lag under 100ms is acceptable for most use cases; over 1 second indicates a degraded replica that should be bypassed
Sticky sessions route users to the primary for 300–500ms after any write, preventing stale read-after-write experiences
Use a circuit breaker to automatically fail over to the primary when a replica is unavailable or unhealthy
Monitor lag continuously with pg_stat_replication on the primary and pg_last_xact_replay_timestamp() on replicas
Drizzle ORM supports explicit per-query routing; route reads to replicaDb and writes to primaryDb in application code
PgBouncer in transaction mode reduces connection count on both primary and replicas by multiplexing application connections
Never use a replica for uniqueness checks before writes — use the primary to avoid race conditions

Introduction#

Why Read Replicas Scale Read-Heavy Systems#

Replication Lag Monitoring#

Sticky Sessions for Read-After-Write Consistency#

Circuit Breaker for Replica Failures#

Drizzle ORM Read Replica Configuration#

When NOT to Use Read Replicas#

Key Takeaways#