Set Up Database Read Replicas for Scaling

Overview

Read replicas are copies of your primary database that handle read-only traffic, offloading the primary instance. They’re the most common scaling strategy for read-heavy workloads: analytics dashboards, search queries, and API reads can all be directed to replicas while writes go to the primary.

This recipe walks through setting up read replicas, splitting reads from writes, monitoring replication lag, and handling stale reads in PostgreSQL, MySQL, and cloud-managed databases.

When to Use

Reach for this recipe when your primary database CPU or I/O is saturated by read queries, when you need to run analytical reports without slowing production writes, when you want geographic read locality by placing replicas near users, or when your workload is read-heavy (more than 80% reads) with moderate write volume.

• If your primary is saturated by read queries, tune the queries first with PostgreSQL Query Optimization.
• For analytics that shouldn’t impact production writes, read replicas keep reporting work away from the primary.
• To get geographic read locality, place replicas close to your users.
• When your workload is mostly reads with moderate writes, replicas are usually the fastest scaling win.

Solution

Python (SQLAlchemy with read/write splitting)

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

# Primary for writes, replicas for reads
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._replica = random.choice(replica_engines)
        self._read_session = sessionmaker(bind=self._replica)()

    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 {})

    def commit(self):
        self._write_session.commit()

# Usage
session = RoutingSession()
users = session.execute_read("SELECT * FROM users WHERE active = true")
session.execute_write(
    "UPDATE users SET last_login = NOW() WHERE id = :id", {"id": 1}
)
session.commit()

JavaScript (Prisma with read replicas)

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL, // primary
    },
  },
});

// Prisma extension for read replicas (preview feature)
const prismaWithReplicas = prisma.$extends({
  query: {
    $allModels: {
      async findUnique({ model, operation, args, query }) {
        // Route reads to replica
        return query(args);
      },
    },
  },
});

// For raw query splitting
async function executeRead(sql) {
  // Connect to replica pool
  const replicaPool = new Pool({ connectionString: process.env.REPLICA_URL });
  return replicaPool.query(sql);
}

async function executeWrite(sql, params) {
  return prisma.$executeRawUnsafe(sql, ...params);
}

Java (Spring Boot with AbstractRoutingDataSource)

@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;
    }
}

@Service
public class UserService {
    @Transactional(readOnly = true)
    public List<User> findAll() {
        // Automatically routed to replica
        return userRepository.findAll();
    }

    @Transactional
    public User save(User user) {
        // Routed to primary
        return userRepository.save(user);
    }
}

Explanation

Read replicas rely on streaming replication. Physical replication copies WAL (Write-Ahead Log) blocks directly; it’s fast but replicates the whole database. Logical replication replicates row-level changes; it’s selective but carries more overhead.

Replication lag is the time between a write on the primary and that same change appearing on the replica. Network latency, replica load, and large transactions all add to it. Your application has to either handle stale reads or route consistency-critical queries back to the primary.

Variants

Different stacks approach read replicas in slightly different ways. The table below compares replication types, lag monitoring, and read-routing options.

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

Choose PostgreSQL or MySQL if you want full control. Use cloud-managed services if you prefer operational simplicity, or CockroachDB if you want automatic multi-active replicas. I’ve used PostgreSQL streaming replication in production for years and it’s been rock solid once you get the monitoring right.

When Not to Use

• Write-heavy workloads: If your workload is more than 50% writes, replicas won’t help. Each write goes to the primary anyway, and replicas add overhead without offloading anything. I learned this the hard way on a logging service that was 70% writes; adding replicas just increased primary load.
• Strong consistency requirements: If your app needs read-after-write consistency on every query, replicas introduce stale-read risk. Use the primary for everything or switch to CockroachDB/Yugabyte for distributed strong consistency.
• Small datasets: If your database fits in RAM and queries are fast, scaling vertically (more CPU, more RAM) is simpler and cheaper than adding replicas.
• Budget constraints: Each replica roughly doubles the database cost. On AWS RDS, a db.r6g.large replica costs the same as the primary. If budget is tight, tune queries and add indexes first.
• Operational complexity: Replicas add failover procedures, monitoring dashboards, and connection routing logic. If your team is small, the operational overhead might not be worth it.

Best Practices

I always monitor replication lag and alert when it exceeds my use case threshold (1 to 5 seconds for user-facing reads, up to 60 seconds for analytics). Route time-sensitive reads to the primary. When a user updates their profile, I read it back from the primary to avoid stale data. Use connection pooling per replica instead of opening direct connections; PgBouncer or ProxySQL work well. See Connection Pooling for a sample configuration. I distribute replicas across availability zones so a single zone failure doesn’t take down my reads. Test failover procedures regularly. Replicas can be promoted to primary during outages, so make sure the process actually works. See Retry Logic for resilience patterns.

Common Mistakes

• Assuming replicas are instantly consistent: I’ve been bitten by this one. Always account for replication lag in read-after-write scenarios. The replica might be 200ms behind and your user sees stale data.
• Sending writes to replicas: Replicas are read-only; writes will fail or be silently ignored. I once saw a junior dev route a migration to a replica and wondered why the schema changes didn’t stick.
• Ignoring replica lag monitoring: Users see stale data without anyone knowing. Set up alerts on lag before you need them, not after.
• Over-replicating: Each replica adds load to the primary. I’ve seen teams spin up 10 replicas and wonder why the primary is now CPU-bound. Find the right ratio (usually 1:3 to 1:5).
• No failover plan: When the primary fails, promote a replica quickly. Practice this regularly. I run failover drills monthly and they’ve saved me during real outages.

Tuning

Tune the replica count with pg_stat_replication

If replication lag consistently exceeds 5 seconds, you may have too many replicas or insufficient replica hardware. Query pg_stat_replication to see the lag per replica and decide.

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;

Place replicas in different availability zones

This gives you both read scaling and disaster recovery:

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

ReadReplica2:
  Type: AWS::RDS::DBInstance
  Properties:
    SourceDBInstanceIdentifier: !Ref MasterInstance
    AvailabilityZone: us-east-1c
    DBInstanceClass: db.r6g.large

Enable hot_standby_feedback on replicas

This stops the master from vacuuming rows that the replicas are still reading:

-- On replica postgresql.conf
hot_standby_feedback = on

Keep enough WAL on the master

Tune max_wal_senders and wal_keep_size so the primary retains enough WAL for its replicas:

-- postgresql.conf
max_wal_senders = 10
wal_keep_size = 1024  -- MB

Route read-heavy queries to replicas

Check pg_stat_statements for the most frequent read queries and send them to replicas:

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

Key Takeaways

• Start with one replica and scale based on monitoring. I’ve never needed more than 5 replicas even for high-traffic apps, and most workloads are fine with 2-3.
• Monitor replication lag from day one. I use pg_stat_replication on PostgreSQL and SHOW REPLICA STATUS on MySQL. Without monitoring, you’re flying blind.
• Route read-after-write queries to the primary. This is the most common bug I see in production: a user updates data, reads it back, and sees the old value because the read hit a replica that hasn’t caught up yet.
• Use connection pooling (PgBouncer, ProxySQL) per replica. Direct connections exhaust the replica’s max_connections quickly under load.
• Practice failover drills monthly. When the primary fails at 3am, you want muscle memory, not a wiki page you’ve never tested.

See Also

• PostgreSQL streaming replication docs - official documentation for streaming replication setup.
• MySQL replication docs - official MySQL replication guide covering binlog, async, and semi-sync modes.
• PgBouncer documentation - connection pooling configuration for PostgreSQL with replica support.
• ProxySQL documentation - read/write splitting and query routing for MySQL.
• AWS RDS Read Replicas - managed read replicas in AWS RDS.
• CockroachDB architecture - multi-active replicas with Raft consensus.
• Database Deadlocks and Retries - handling concurrent access issues in replicated setups.
• Connection Pooling - pool configuration for primary and replica connections.