Read replicas setup for database read scaling

Setting Up Read Replicas for Database Read Scaling

Read Replicas — database replicas that accept only SELECT queries. Allow horizontal scaling of read load without changing application architecture.

Use cases

• Separate load: API requests read from replicas, transactions write to master
• Analytical queries (complex JOINs, aggregations) go to dedicated replica
• Different applications use different replicas (CMS backend, public API, reports)

PostgreSQL: multiple replicas

# Create replica via pg_basebackup
pg_basebackup -h master-host -U replication_user \
  -D /var/lib/postgresql/14/main -P -Xs -R

Routing in application

Laravel (PHP)

// config/database.php
'pgsql' => [
    'read' => [
        ['host' => '192.168.1.11'],
        ['host' => '192.168.1.12'],
    ],
    'write' => [
        'host' => '192.168.1.10',
    ],
    'sticky' => true,
]

Node.js (Sequelize)

const sequelize = new Sequelize('myapp', 'user', 'password', {
  dialect: 'postgres',
  replication: {
    read: [
      { host: '192.168.1.11', port: 5432 },
      { host: '192.168.1.12', port: 5432 },
    ],
    write: { host: '192.168.1.10', port: 5432 },
  },
})

pgBouncer read/write split

# pgbouncer.ini
[databases]
myapp = host=192.168.1.10 port=5432 dbname=myapp
myapp_read = host=192.168.1.11,192.168.1.12 port=5432 dbname=myapp

Manage replication lag

Critical issue: write → immediate read may read stale data if replica lags.

-- On master: send LSN to client
SELECT pg_current_wal_lsn();

Pattern in application:

def read_after_write(lsn):
    replica = get_replica()
    if replica.is_caught_up(lsn):
        return replica.execute(query)
    else:
        return master.execute(query)

AWS RDS Read Replicas

aws rds create-db-instance-read-replica \
  --db-instance-identifier myapp-replica-1 \
  --source-db-instance-identifier myapp-master \
  --db-instance-class db.r6g.large

Monitoring replicas

-- On master: status of all replicas
SELECT application_name, client_addr, state,
       round(EXTRACT(EPOCH FROM replay_lag)) as replay_lag_sec
FROM pg_stat_replication;

Timeline

Setup of two Read Replicas with load balancing in application — 1–2 business days.