Read Replicas: Scale Reads Without Changing Your App

Overview

Read replicas are copies of your primary database that answer read-only queries. The primary keeps accepting writes and streams every change to the replicas — WAL records in PostgreSQL, binlog events in MySQL — which replay them in near-real-time. Because replication is asynchronous, a replica is always a little behind, and that gap (replication lag) is the one trade-off your application has to live with.

It’s the most common first step for scaling read-heavy workloads, and for good reason: analytics dashboards, search queries and API reads all move to replicas while the primary breathes. This guide walks through the whole lifecycle — configuring streaming replication, splitting reads from writes at the application, ORM and proxy levels, handling lag, monitoring, failover, and the tuning knobs that matter in production. For replication topologies beyond read replicas (multi-master, synchronous commit), see Database Replication. Runnable versions of the configs and routers in this guide live in the companion repo.

When to Use

Replicas pay off when reads dominate and a few hundred milliseconds of staleness is acceptable:

• Read queries exceed 80% of your database workload
• Analytical queries (reports, aggregations) slow down transactional writes
• You need read scaling beyond what a single instance can provide
• You want geographic read distribution — replicas in 2-3 regions close to users
• You need a hot standby for failover without dedicated standby hardware
• Your working set fits in memory but query volume exceeds CPU capacity

Before adding a replica, make sure the reads themselves are efficient — a replica just runs the same slow query somewhere else. PostgreSQL Query Optimization covers index and plan tuning first.

When NOT to Use

• Write-heavy workloads. If writes exceed ~50% of traffic, replicas add overhead without offloading anything — every write still hits the primary. Look at sharding or partitioning instead.
• Strong read-after-write consistency. If every read must see the latest write, replication lag will violate that. Stay on the primary or use a distributed SQL engine (CockroachDB, Yugabyte) built for strong consistency.
• Small, fast datasets. If the database fits in RAM and queries are already fast, scaling vertically is simpler and cheaper than running a second node.
• Tight budgets. Each replica roughly doubles your database bill — on RDS a replica costs the same as the primary. Tune queries and indexes first if cost is the constraint.
• Tiny teams. Replicas add failover procedures, monitoring and routing logic. For a non-critical internal tool, that operational weight may not be worth it.

Core Concepts

Concept	Description
Primary (Master)	The database instance that accepts writes
Replica	A read-only instance that replays changes from the primary
Replication Lag	Delay between a write on the primary and its appearance on the replica
Streaming Replication	Continuous transfer of WAL (PostgreSQL) or binlog (MySQL)
Logical Replication	Row-level replication with per-table filtering (PostgreSQL 10+)
Promotion	Converting a replica into the new primary during failover

Physical streaming replication copies WAL blocks as they arrive — fast, but it replicates the entire cluster. Logical replication ships row-level changes for selected tables, which is more flexible (different schemas, filtered columns) at the cost of extra overhead. Read replicas almost always use the physical kind; for PostgreSQL-specific depth on both, see the Complete Guide to PostgreSQL Replication.

Read Replica Architecture

Writes and consistency-critical reads stay on the primary; everything else fans out to replicas behind a routing layer:

The router can live in your code (pools, ORM hooks) or outside it (PgBouncer, ProxySQL, RDS Proxy). Both approaches are covered below.

Step-by-Step Read Replica Setup

1. Configure Streaming Replication

Set up physical streaming replication for near-real-time copies. On the primary, allow WAL senders and keep enough WAL on disk for replicas that fall behind:

# postgresql.conf (Primary)
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1024
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
hot_standby = on

# postgresql.conf (Replica)
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s

hot_standby_feedback deserves a note: it stops the primary from vacuuming rows a long-running replica query still needs, at the cost of some table bloat on the primary. Keep it on unless replica queries routinely run for many minutes.

# pg_hba.conf (Primary): Allow replication connections
host replication replicator 192.168.1.0/24 scram-sha-256

# Initialize replica with base backup
pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -v -R

On MySQL the same idea uses binlog coordinates. binlog_format = ROW is the safe default — statement-based binlogs can diverge on non-deterministic queries:

# my.cnf (MySQL Primary)
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M

# my.cnf (MySQL Replica)
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin
read_only = 1

-- MySQL: Configure replication on replica
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='primary-host',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='password',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=0;

START REPLICA;

2. Route Reads to Replicas

Direct read queries to replicas while keeping writes on the primary. The simplest version is two connection pools and a helper:

# Example: Python with read/write splitting
import psycopg2
from contextlib import contextmanager

primary_pool = psycopg2.pool.ThreadedConnectionPool(1, 10, dsn="dbname=app host=primary")
replica_pool = psycopg2.pool.ThreadedConnectionPool(1, 10, dsn="dbname=app host=replica1")

@contextmanager
def get_db_connection(read_only=False):
    """Get connection: primary for writes, replica for reads."""
    pool = replica_pool if read_only else primary_pool
    conn = pool.getconn()
    try:
        yield conn
    finally:
        pool.putconn(conn)

def get_user(user_id):
    with get_db_connection(read_only=True) as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cursor.fetchone()

If you’re already on an ORM, use its routing hooks instead of hand-rolled pools. SQLAlchemy swaps the engine per operation type:

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import random

primary_engine = create_engine("postgresql://user:pass@primary:5432/app")
replica_engines = [
    create_engine("postgresql://user:pass@replica1:5432/app"),
    create_engine("postgresql://user:pass@replica2:5432/app"),
]

class RoutingSession:
    def __init__(self):
        self._write_session = sessionmaker(bind=primary_engine)()
        self._read_session = sessionmaker(bind=random.choice(replica_engines))()

    def execute_write(self, query, params=None):
        return self._write_session.execute(text(query), params or {})

    def execute_read(self, query, params=None):
        return self._read_session.execute(text(query), params or {})

Django ships database routers out of the box — reads and writes split by declaring it:

# routers.py
class ReadReplicaRouter:
    def db_for_read(self, model, **hints):
        return 'replica'

    def db_for_write(self, model, **hints):
        return 'default'

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == 'default'

# settings.py
DATABASE_ROUTERS = ['myapp.routers.ReadReplicaRouter']

Spring routes on the transaction annotation — @Transactional(readOnly = true) hits the replica automatically:

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource routingDataSource(
            @Qualifier("primaryDataSource") DataSource primary,
            @Qualifier("replicaDataSource") DataSource replica) {

        AbstractRoutingDataSource routing = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                    ? "replica" : "primary";
            }
        };

        Map<Object, Object> targets = new HashMap<>();
        targets.put("primary", primary);
        targets.put("replica", replica);
        routing.setTargetDataSources(targets);
        routing.setDefaultTargetDataSource(primary);
        return routing;
    }
}

Routing Strategies

Strategy	Implementation	Best For
Connection pool splitting	Separate pools for primary and replica	Simple applications
ORM integration	Django routers, Spring AbstractRoutingDataSource, SQLAlchemy	Framework-based apps
Proxy-based	PgBouncer, ProxySQL, HAProxy, RDS Proxy	Zero application changes
Driver multi-host	libpq host=a,b, Connector/J loadBalance	Basic failover, not true balancing
DNS-based	Separate endpoints (primary.db, replica.db)	Microservices

3. Offload Routing to a Proxy

When you don’t want routing logic in the app — several services, mixed languages — put a proxy in front. PgBouncer exposes each backend as a database alias and the app picks one per query:

# pgbouncer.ini
[databases]
master = host=master.db.internal port=5432 dbname=app
replica1 = host=replica1.db.internal port=5432 dbname=app
replica2 = host=replica2.db.internal port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

import psycopg2, random

def get_connection(is_write=False):
    if is_write:
        return psycopg2.connect("postgresql://user:pass@pgbouncer:6432/master")
    replica = random.choice(['replica1', 'replica2'])
    return psycopg2.connect(f"postgresql://user:pass@pgbouncer:6432/{replica}")

ProxySQL goes further for MySQL — it inspects each query and routes by rule, so SELECT ... FOR UPDATE can stay on the primary while plain reads fan out:

-- Configure ProxySQL with backend servers
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES
  (0, 'master.db.internal', 3306),
  (1, 'replica1.db.internal', 3306),
  (1, 'replica2.db.internal', 3306);

-- Routing rules: SELECT goes to replicas, locking reads to master
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT.*FOR UPDATE', 0, 1),
  (2, 1, '^SELECT', 1, 1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

On AWS, RDS Proxy does the pooling managed-service way — it sits in front of your primary and replicas and absorbs connection storms without app changes:

# CloudFormation snippet for RDS Proxy
Resources:
  ReadWriteProxy:
    Type: AWS::RDS::DBProxy
    Properties:
      DBProxyName: app-proxy
      EngineFamily: POSTGRESQL
      RoleArn: !GetAtt ProxyRole.Arn
      Auth:
        - AuthScheme: SECRETS
          SecretArn: !Ref DBSecretArn
      TargetGroupName: default
      ConnectionPoolConfiguration:
        MaxConnectionsPercent: 80
        IdleClientTimeout: 1800

4. Handle Replication Lag

Replication lag is the main thing that breaks naive replica setups. A user updates their profile, immediately reloads the page, and the read lands on a replica that hasn’t replayed the write — they see the old value and file a bug. Routing around this means either measuring lag before each read or pinning post-write reads to the primary:

# Example: Lag-aware routing
class LagAwareRouter:
    def __init__(self, primary, replica, max_lag_seconds=5):
        self.primary = primary
        self.replica = replica
        self.max_lag = max_lag_seconds

    def get_replica_lag(self):
        """Check current replication lag in seconds."""
        cursor = self.replica.cursor()
        cursor.execute("""
            SELECT
                EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
            AS lag_seconds
        """)
        return cursor.fetchone()[0] or 0

    def route_query(self, query, requires_freshness=False):
        """Route to replica if lag is acceptable and freshness not required."""
        if requires_freshness:
            return self.primary

        lag = self.get_replica_lag()
        if lag > self.max_lag:
            return self.primary

        return self.replica

For the common case — read-back-after-write — a fallback wrapper is enough:

def read_after_write(conn_master, conn_replica, query, params, max_wait=2.0):
    """Read from replica with fallback to master if lag is too high."""
    with conn_master.cursor() as cur:
        cur.execute("""
            SELECT EXTRACT(EPOCH FROM (now() - replay_lag))::float
            FROM pg_stat_replication LIMIT 1
        """)
        lag = cur.fetchone()[0] or 0

    if lag > max_wait:
        with conn_master.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()

    try:
        with conn_replica.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()
    except Exception:
        with conn_master.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()

Strategies for Handling Lag

Approach	How It Works	Trade-off
Session stickiness	Read from primary after a write in the same session	Slightly more primary load
Lag threshold	Route to primary if lag exceeds X seconds	Simple, but can spike primary load
Eventual consistency	Accept stale reads, document it	Best performance, but user-facing inconsistency
Read-after-write redirect	Track recently modified keys, route those to primary	Complex, requires application logic
Causal consistency	Track LSN per session, wait for replica to catch up	PostgreSQL 14+ logical replication

In practice, session stickiness plus a lag threshold covers most apps. Causal consistency is the elegant answer but you pay for it in tracking infrastructure.

5. Monitor Lag and Health

Track replica health and lag from day one — without it you’re flying blind:

-- PostgreSQL: lag per replica on the primary
SELECT
    client_addr,
    state,
    sent_lsn,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;

-- WAL receiver status on the replica itself
SELECT status, receive_start_lsn, written_lsn, flushed_lsn
FROM pg_stat_wal_receiver;

-- Replication slot lag (unconsumed WAL builds up if a slot stalls)
SELECT slot_name, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

-- MySQL: check replica lag
SHOW REPLICA STATUS\G
-- Key fields: Seconds_Behind_Source (< 5),
-- Replica_IO_Running and Replica_SQL_Running (both Yes)

-- Connection health via performance schema
SELECT
    channel_name,
    service_state,
    last_error_number,
    last_error_message
FROM performance_schema.replication_connection_status;

Alert on the two failure modes that actually hurt — lag creeping past your threshold and replication silently stopping:

# Prometheus rules for replication monitoring
groups:
  - name: replication_alerts
    rules:
      - alert: HighReplicationLag
        expr: |
          pg_stat_replication_pg_wal_lsn_diff / 1024 / 1024 > 100
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replication lag exceeds 100MB"

      - alert: ReplicationStopped
        expr: |
          pg_stat_replication_state == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Replication has stopped"

Key Metrics to Monitor

• Lag in bytes/seconds: How far behind is the replica?
• Replication state: Is it running, paused, or stopped?
• Lag trend: Is lag growing steadily? That means the replica can’t keep up
• Query latency on the replica: Are reads still fast under load?
• Connection count: Is the replica near max_connections?

6. Plan for Failover

When the primary dies, one of those replicas becomes the new primary — and it’s better to have rehearsed that move before 3am:

# PostgreSQL: Manual promotion
pg_ctl promote -D /var/lib/postgresql/data

# Or using repmgr (automated failover tool)
repmgr standby promote

# MySQL: Promote replica to primary
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL read_only = OFF;

Failover Approaches

Approach	RTO	Complexity	Best For
Manual promotion	5-30 min	Low	Small teams, non-critical systems
Automated tools	30-120s	Medium	repmgr, Patroni, orchestrator
Managed service	0-60s	None	RDS Multi-AZ, Cloud SQL HA
Synchronous replication	0s (no data loss)	High	Financial systems (latency for safety)

Remember that a promoted replica means your write endpoint moves — apps pointing at the old primary need a DNS switch, a proxy, or a config rollout. Managed services hide most of this; self-managed setups should test the full path, not just the promote command.

Production Tuning

A replica that’s lagging or starved of connections is worse than no replica. These are the knobs worth checking once replicas are live:

Size the replica count from data, not vibes. If lag consistently exceeds your threshold, either the replicas are under-provisioned or you’ve got more than the primary can feed:

SELECT application_name, client_addr,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       EXTRACT(EPOCH FROM replay_lag) AS lag_seconds
FROM pg_stat_replication;

Spread replicas across availability zones. One zone outage shouldn’t take down all your read capacity — and multi-AZ replicas double as DR:

# AWS RDS: read replicas in different AZs
ReadReplica1:
  Type: AWS::RDS::DBInstance
  Properties:
    SourceDBInstanceIdentifier: !Ref MasterInstance
    AvailabilityZone: us-east-1b
    DBInstanceClass: db.r6g.large

Find the queries that belong on a replica. pg_stat_statements shows which hot reads generate the most load — those are the first candidates for replica routing:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT%'
ORDER BY calls DESC
LIMIT 20;

Pool connections per replica. Replicas have the same max_connections ceiling as the primary; PgBouncer or RDS Proxy in front of each replica keeps connection storms from taking it down. See Connection Pooling for pool sizing.

What Works

• Start with one replica. One well-configured replica solves 80% of read scaling needs. I’ve rarely needed more than 2-3 even on high-traffic apps — a 1:3 to 1:5 primary-to-replica ratio is a sane ceiling.
• Set lag thresholds per use case. Around 1-5 seconds for user-facing reads, up to a minute for analytics. Alert on whichever is tightest.
• Read back from the primary after a write. The classic production bug is a user updating data and seeing the old value because the read hit a lagging replica.
• Use replicas for reporting and analytics. Isolate expensive queries from the primary — that’s often the whole point.
• Test failover before you need it. Promote a replica in staging quarterly. When the primary fails, you want muscle memory, not a wiki page you’ve never run.
• Keep replica hardware equal to the primary. A weaker replica lags during peaks exactly when you need it most.
• Monitor lag from day one. pg_stat_replication on PostgreSQL, SHOW REPLICA STATUS on MySQL — without monitoring you only learn about lag from user complaints.

Common Mistakes

• Routing all reads to replicas. Session state, recently modified data and critical reads belong on the primary.
• Ignoring replication lag. Users report “bugs” that are actually stale reads.
• A single replica with no failover plan. If it dies, read capacity drops to zero — and if it’s also your standby, you’re left without DR.
• Running writes on replicas. Accidental writes break replication and force a re-initialization. I once watched a migration routed to a replica leave the schema mysteriously unchanged.
• Over-replicating. Every replica adds WAL sender load on the primary. Ten replicas on a modest primary just makes the primary the bottleneck again.
• Using replicas for write scaling. Replicas only scale reads — for write scaling look at sharding.

Variants

• Cascading replication: Replica → Replica → Replica for geographic distribution or fan-out beyond the primary’s WAL sender capacity
• Multi-primary (master-master): Writes accepted on several nodes — powerful but conflict resolution is hard; use with caution
• Logical replication: Selective table/column replication (PostgreSQL 10+, MySQL binlog filtering) for partial copies and version-zero-downtime upgrades
• Cross-region replicas: Cloud-managed replicas in other regions for global read latency and DR
• Delayed replica: A replica intentionally hours behind — your insurance against DROP TABLE mistakes

Each engine ships replication differently:

Database	Replication Type	Lag Monitoring	Read Routing
PostgreSQL	Streaming / Logical	pg_stat_replication	PgBouncer, custom proxy
MySQL	Binlog (async/semi-sync)	SHOW REPLICA STATUS	ProxySQL, MaxScale
Cloud RDS	Managed streaming	CloudWatch / Cloud Monitoring	RDS Proxy, custom
CockroachDB	Multi-active (Raft)	Built-in	Automatic

See Also

• Database Replication — Primary-Replica, Multi-Primary
• Complete Guide to PostgreSQL Replication
• Database Sharding Implementation — when replicas aren’t enough and writes need scaling too
• Connection Pooling Deep Dive
• PostgreSQL Query Optimization — fix slow reads before scaling them

Further Reading

• PostgreSQL streaming replication docs — official guide to hot standbys and streaming setup
• MySQL replication documentation — binlog, async and semi-sync modes
• AWS RDS Read Replicas — managed replicas, cross-region, promotion
• PgBouncer documentation — pool modes and multi-backend configuration
• ProxySQL documentation — query rules for MySQL read/write splitting

Conclusion

Read replicas are the simplest durable way to scale database reads — but they’re not free. They trade a little consistency (lag) for a lot of read capacity, and the teams that get bitten are the ones who deployed them without lag monitoring or a failover drill. Configure streaming replication, pick a routing layer that matches your stack, alert on lag before users notice it, and rehearse promotion. Do that and a couple of replicas will carry 10x read growth without touching your data model.