Read Replicas

Read replicas are copies of the primary database for handling read traffic.

How to Create Replicas

1. Start with primary database
2. Create snapshot or backup
3. Restore to new instance
4. Configure replication
5. Verify replication is working

PostgreSQL Replication Setup

-- Primary: Enable replication
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
SELECT pg_reload_conf();

-- Create replication user
CREATE USER repl_user WITH REPLICATION;

-- Primary: pg_hba.conf
-- host replication repl_user 10.0.0.0/8 md5

-- Replica: recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=primary port=5432 user=repl_user'

MySQL Replication Setup

-- Primary: Enable binlog
[mysqld]
log-bin = mysql-bin
server-id = 1

-- Primary: Create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

-- Replica: Configure
CHANGE MASTER TO
  MASTER_HOST='primary',
  MASTER_USER='repl_user',
  MASTER_AUTO_POSITION=1;
START REPLICA;

Cloud Database Replicas

# AWS RDS
aws rds create-db-instance-read-reparator \
  --db-instance-identifier replica-1 \
  --source-db-instance-identifier primary

# Google Cloud SQL
gcloud sql instances create replica-1 \
  --master-instance-name=primary \
  --region=us-central1

# Azure
az sql db replica create \
  --name replica-1 \
  --server primary-server \
  --resource-group mygroup

Replica Monitoring

-- Check replication status
SHOW REPLICA STATUS\\G

-- Key metrics:
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Replica: 0

-- PostgreSQL
SELECT * FROM pg_stat_replication;

Replica Best Practices

1. Monitor replication lag: Set up alerts
2. Use dedicated replica instances: Don't mix read/write
3. Size replicas appropriately: Match primary or larger
4. Test failover regularly: Ensure replicas can promote
5. Use connection pooling: For replica connections

Read routing directs read queries to appropriate replicas.

Routing Strategies

1. Round Robin
   Requests distributed evenly
   Simple, no state

2. Least Lag
   Route to replica with least lag
   Better consistency

3. Geographic
   Route to nearest replica
   Lower latency

4. Weighted
   Route based on replica capacity
   Better utilization

Application-Level Routing

# Python example
class ReadRouter:
    def __init__(self, primary, replicas):
        self.primary = primary
        self.replicas = replicas
        self.counter = 0
    
    def get_connection(self, write=False):
        if write:
            return self.primary
        
        # Round robin
        replica = self.replicas[self.counter % len(self.replicas)]
        self.counter += 1
        return replica

# Usage
router = ReadRouter(primary, [replica1, replica2, replica3])
conn = router.get_connection(write=False)  # Gets replica

Connection Pooling with Replicas

# PgBouncer configuration
[databases]
primary = host=primary port=5432 dbname=mydb
replica1 = host=replica1 port=5432 dbname=mydb
replica2 = host=replica2 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Read Routing Best Practices

1. Route reads to replicas: Free primary for writes
2. Use connection pooling: Reduce connection overhead
3. Monitor replica health: Remove unhealthy replicas
4. Consider lag: Route to low-lag replicas for consistency
5. Handle failover: Redirect if replica fails

Managing replication lag ensures data consistency for reads.

What is Replication Lag

Primary: Write X = 1 at T=0
Replica: Receives X = 1 at T=100ms

Lag: 100ms

During lag:
- Primary has X = 1
- Replica has X = 0
- Reads from replica return stale data

Measuring Lag

-- PostgreSQL
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

-- MySQL
SHOW REPLICA STATUS\\G
Seconds_Behind_Replica: 5

-- Monitor continuously
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Lag Thresholds

Acceptable lag by use case:

Critical (financial): < 1 second
Important (user data): < 5 seconds
Standard (analytics): < 30 seconds
Background (logs): < 60 seconds

Alert when lag exceeds threshold

Reducing Lag

1. Optimize Replication
   - Use parallel replication
   - Optimize network
   - Reduce transaction size

2. Hardware
   - Faster disks on replica
   - More CPU on replica
   - Better network

3. Configuration
   - Increase WAL buffers
   - Tune checkpoint settings
   - Optimize vacuum settings

Handling Lag in Application

# Read-after-write consistency
def get_user(user_id, after_write=False):
    if after_write:
        # Read from primary
        return primary_db.query(user_id)
    else:
        # Read from replica (may be stale)
        return replica_db.query(user_id)

# After writing, read from primary for N seconds
def update_user(user_id, data):
    primary_db.update(user_id, data)
    set_flag(user_id, 'read_primary', ttl=5)  # 5 seconds

Lag Monitoring Best Practices

1. Continuous monitoring: Track lag over time
2. Set alerts: Notify when lag exceeds threshold
3. Dashboard visibility: Show lag in monitoring dashboard
4. Historical trends: Track lag patterns
5. Automated responses: Route to primary when lag high