Read Replicas: Scaling Reads, Replication Lag, and Failover

Read replicas are copies of your primary database that serve read queries. They are the most common and cost-effective way to scale database read throughput. This article covers setup, load balancing, lag monitoring, and failover strategies.

How Read Replicas Work

The primary database streams changes to replicas via the write-ahead log (WAL). In PostgreSQL, this is called streaming replication. The replica continuously applies WAL records to stay current.

PostgreSQL Streaming Replication Setup

On the primary:

postgresql.conf

wal_level = replica

max_wal_senders = 5

wal_keep_size = 1024

Create a replication user:

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'secure_password';

Allow replication in pg_hba.conf:

host replication replicator replica-host/32 md5

On the replica:

pg_basebackup -h primary-host -D /var/lib/postgresql/data \

-U replicator -P -v --wal-method=stream

Create a standby.signal file and start PostgreSQL. The replica streams continuously.

MySQL Replica Setup

my.cnf on primary

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_format = ROW

CREATE USER 'replicator'@'%' IDENTIFIED BY 'secure_password';

GRANT REPLICATION SLAVE ON . TO 'replicator'@'%';

On the replica:

CHANGE MASTER TO

MASTER_HOST='primary-host',

MASTER_USER='replicator',

MASTER_PASSWORD='secure_password',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=0;

START SLAVE;

SHOW SLAVE STATUS\G;

Load Balancing Strategies

Application-level read/write splitting is the most common pattern:

import psycopg2

class DatabaseRouter:

def init(self, primary_dsn, replica_dsns):

self.primary = psycopg2.connect(primary_dsn)

self.replicas = [psycopg2.connect(dsn) for dsn in replica_dsns]

self.round_robin = 0

def get_connection(self, read_only=False):

if read_only and self.replicas:

conn = self.replicas[self.round_robin % len(self.replicas)]

self.round_robin += 1

return conn

return self.primary

def execute_read(self, query, params=None):

conn = self.get_connection(read_only=True)

with conn.cursor() as cur:

cur.execute(query, params or ())

return cur.fetchall()

def execute_write(self, query, params=None):

conn = self.get_connection(read_only=False)

with conn.cursor() as cur:

cur.execute(query, params or ())

conn.commit()

A proxy layer like PgBouncer, ProxySQL, or HAProxy handles routing transparently:

ProxySQL query rules

mysql_query_rules:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\- rule_id: 1

active: 1

match: "^SELECT .*"

destination_hostgroup: 1 # replicas

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\- rule_id: 2

active: 1

destination_hostgroup: 0 # primary

Replication Lag

Replication lag is the time between a commit on the primary and its visibility on a replica. Causes include:

Large transactions on the primary that must be applied in full on replicas.
Replica hardware that is slower than the primary.
Network latency between primary and replica.
Long-running queries on the replica competing for I/O.

Monitoring Lag

PostgreSQL offers precise lag metrics:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- On the replica

SELECT pg_last_wal_receive_lsn(),

pg_last_wal_replay_lsn(),

pg_wal_lsn_diff(pg_last_wal_receive_lsn(),

pg_last_wal_replay_lsn()) AS replay_lag_bytes;

In MySQL:

SHOW SLAVE STATUS\G

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Focus on: Seconds_Behind_Master, Relay_Log_Space

Set up alerts. A lag of more than a few seconds (or minutes for analytics workloads) indicates a problem.

Handling Stale Reads

Applications that route reads of recently written data back to the primary avoid inconsistent behavior. One pattern is to mark rows with a timestamp and route queries for recent rows accordingly:

def get_order(order_id):

Orders updated within the last 30 seconds route to primary

order = router.execute_read(

"SELECT created_at FROM orders WHERE id = %s", (order_id,)

)

if order and (datetime.utcnow() - order[0]) < timedelta(seconds=30):

conn = router.get_connection(read_only=False)

Query primary

else:

conn = router.get_connection(read_only=True)

Failover Strategies

When the primary fails, one replica must become the new primary:

PostgreSQL

Promote a replica to primary

pg_ctl promote -D /var/lib/postgresql/data

Or via pg_rewind for a clean re-sync:

After promotion, rewind old primary to follow new primary

pg_rewind --target-pgdata=/var/lib/postgresql/data \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--source-server='host=new-primary-db ...'

Managed Failover

Tools like Patroni automate failover:

patroni.yml

scope: myapp

consul:

host: consul.service.consul:8500

postgresql:

use_pg_rewind: true

use_slots: true

parameters:

max_connections: 200

Patroni uses a distributed consensus store (etcd, Consul, Zookeeper) to elect a new leader when the current leader fails. The election typically completes in under 30 seconds.

Best Practices

Use at least two replicas for redundancy.
Test failover regularly in staging environments.
Align replica hardware to the primary to avoid replay stalls.
Monitor replication slots to prevent WAL accumulation on the primary.
Usehot_standby_feedback in PostgreSQL to prevent query cancellations on replicas due to vacuum conflicts.
Consider cascading replication for multi-region setups: primary in us-east-1 streams to a regional replica in us-west-2, which then feeds application replicas in the same region.

Read replicas are a proven, low-risk approach to scaling reads. Combined with proper monitoring and automated failover, they form the foundation of a highly available database architecture.