Database Read Replicas: Setup, Routing, and Consistency Tradeoffs

Most applications are read-heavy. A typical web app serves 10 to 100 reads for every write. When your primary database becomes the bottleneck, the most effective scaling strategy is often the simplest: add read replicas. They absorb read traffic, reduce load on the primary, and provide a foundation for high availability.

What Are Read Replicas?#

A read replica is a copy of your primary database that receives changes asynchronously (or synchronously, depending on configuration). Clients send writes to the primary and reads to one or more replicas.

        Writes                    Reads
Client ────────▶ Primary DB ──────────▶ Replica 1
                     │                   Replica 2
                     │                   Replica 3
                     └── Replication ──▶ (async WAL / binlog)

Key Properties#

• Read-only — Replicas accept only read queries. Write attempts are rejected.
• Eventually consistent — Replicas may lag behind the primary by milliseconds to seconds.
• Independently scalable — Add more replicas to handle more read throughput.
• Geographically distributable — Place replicas in regions close to your users for lower latency.

Read Replica Setup#

PostgreSQL Streaming Replication#

PostgreSQL uses Write-Ahead Log (WAL) streaming to replicate data:

On the primary, configure postgresql.conf:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

Create a replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Update pg_hba.conf to allow the replica to connect:

host replication replicator replica_ip/32 scram-sha-256

On the replica, create a base backup and configure postgresql.conf:

primary_conninfo = 'host=primary_ip user=replicator password=secure_password'
hot_standby = on

Create a standby.signal file in the data directory to start in replica mode. The replica connects to the primary and streams WAL records continuously.

MySQL Read Replicas#

MySQL uses binary log (binlog) replication:

On the primary, enable binlog in my.cnf:

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

On the replica:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary_host',
    SOURCE_USER = 'repl_user',
    SOURCE_PASSWORD = 'secure_password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

GTID-based replication simplifies failover because each transaction has a globally unique identifier — no need to track binlog file positions manually.

Amazon Aurora Replicas#

Aurora decouples storage from compute. Replicas share the same storage volume as the primary, which means:

• No replication lag for storage — Replicas read from the same distributed storage layer.
• Replica lag is typically under 20ms — Only the page cache needs to be updated.
• Up to 15 read replicas per cluster.

aws rds create-db-instance \
    --db-instance-identifier my-read-replica \
    --db-cluster-identifier my-aurora-cluster \
    --db-instance-class db.r6g.xlarge \
    --engine aurora-postgresql

Aurora also provides a reader endpoint that automatically load-balances connections across all available replicas.

Replication Lag Monitoring#

Replication lag is the delay between a write on the primary and its visibility on a replica. Monitoring it is critical because stale reads cause subtle bugs.

PostgreSQL#

-- On the primary: check replication status
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- On the replica: check lag in seconds
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

MySQL#

SHOW REPLICA STATUS\G
-- Key field: Seconds_Behind_Source

Alert Thresholds#

Set up alerts in your monitoring tool (Prometheus, CloudWatch, Datadog) to catch lag spikes before they affect users.

Routing Queries to Replicas#

Sending reads to replicas and writes to the primary requires a routing layer. Several approaches exist:

Application-Level Routing#

The application decides where each query goes:

class DatabaseRouter:
    def __init__(self, primary_dsn, replica_dsns):
        self.primary = create_connection(primary_dsn)
        self.replicas = [create_connection(dsn) for dsn in replica_dsns]
        self._index = 0

    def get_read_connection(self):
        conn = self.replicas[self._index % len(self.replicas)]
        self._index += 1
        return conn

    def get_write_connection(self):
        return self.primary

Frameworks like Django, Rails, and Spring support read/write splitting out of the box or via plugins.

Proxy-Based Routing#

A database proxy sits between the application and the databases, routing queries automatically:

Application → ProxySQL / PgBouncer → Primary (writes)
                                    → Replica pool (reads)

ProxySQL (MySQL) parses queries and routes SELECT statements to replicas. PgBouncer (PostgreSQL) can be paired with custom routing rules or tools like Odyssey for similar behavior.

DNS-Based Routing#

Cloud providers offer DNS endpoints that abstract routing:

• Aurora: Writer endpoint and reader endpoint.
• RDS: Primary endpoint and read replica endpoints.
• Cloud SQL: Primary IP and read replica IPs.

The downside is DNS TTL. When a replica fails, DNS updates may take seconds to minutes to propagate.

Consistency Tradeoffs#

Read replicas introduce eventual consistency. This creates real problems if you are not deliberate about which queries go where.

The Read-After-Write Problem#

A user updates their profile, then immediately refreshes the page. If the refresh hits a replica that has not received the write yet, the user sees stale data:

1. Client → POST /profile (goes to primary) → 200 OK
2. Client → GET /profile  (goes to replica)  → returns OLD data

Solutions#

Route to primary after writes — For a short window after a write, send that user's reads to the primary:

def get_connection(user_id, operation):
    if operation == "write":
        cache.set(f"recent_write:{user_id}", True, ttl=5)
        return primary

    if cache.get(f"recent_write:{user_id}"):
        return primary  # Read from primary for 5s after a write

    return random.choice(replicas)

Causal consistency tokens — The write response includes a replication position. The read request sends that position, and the replica waits until it has caught up before responding.

Session stickiness — Pin a user's session to the primary for a configurable window after any write operation.

Monotonic Reads#

Ensure a single user never sees data go backward in time. If replica A shows version 5 of a record, then replica B shows version 3, the user perceives data loss. Pin each session to a single replica to guarantee monotonic reads.

Failover and Promotion#

When the primary fails, a replica must be promoted to take over writes. This is the foundation of high availability.

Manual Promotion#

PostgreSQL:

-- On the replica to be promoted
SELECT pg_promote();

MySQL:

STOP REPLICA;
RESET REPLICA ALL;
-- The replica is now a standalone primary

Automated Failover#

Cloud-managed databases handle promotion automatically:

• Aurora: Promotes the replica with the highest priority (lowest tier number). Failover typically completes in under 30 seconds.
• RDS Multi-AZ: Automatic failover to a standby instance (synchronous replication, not a read replica).
• Cloud SQL: High-availability configuration with automatic failover.

For self-managed deployments, tools like Patroni (PostgreSQL) and Orchestrator (MySQL) automate leader election and promotion.

Post-Promotion Checklist#

After promoting a replica:

1. Update connection strings or DNS to point to the new primary.
2. Reconfigure remaining replicas to follow the new primary.
3. Verify replication is streaming on all replicas.
4. Check for data consistency — the promoted replica may have been slightly behind.
5. Investigate the original primary failure before bringing it back (as a replica).

Scaling Patterns#

Tiered Replicas#

Not all reads have the same requirements. Create replica tiers:

Primary → Tier 1 Replicas (low-lag, user-facing reads)
       → Tier 2 Replicas (analytics, reporting, batch jobs)
       → Tier 3 Replicas (backups, data exports)

Cross-Region Replicas#

Place replicas in multiple regions to serve geographically distributed users:

Primary (us-east-1) → Replica (eu-west-1)
                    → Replica (ap-southeast-1)

Cross-region replication adds latency to the replication stream (50–200ms depending on distance), so these replicas are best for read-heavy, lag-tolerant workloads.

Read Replica Load Balancing#

Distribute read traffic across replicas using:

• Round-robin — Simple, equal distribution.
• Least-connections — Route to the replica with the fewest active queries.
• Lag-aware — Skip replicas that exceed a lag threshold.

Conclusion#

Read replicas are one of the most practical tools for scaling database-backed applications. They reduce primary load, improve read latency, and provide a foundation for high availability. The key to success is understanding the consistency tradeoffs, monitoring replication lag aggressively, and designing your query routing layer to handle the read-after-write problem gracefully.

---

This is article #365 on Codelit.io — your deep-dive resource for system design, backend engineering, and infrastructure patterns. Explore more at codelit.io.