Read replicas

Backend Fundamentals · Databases · 8 min read

1. What problem does this solve?

• Problem: A single database instance becomes a bottleneck when many read requests compete with writes. Reads can block writes, and a single point of failure can bring the whole service down.
• Why it matters:
  • Throughput – Heavy read traffic can saturate CPU, I/O, or network of the primary node.
  • Availability – If the primary crashes, the entire application stops serving data.
  • Latency – Reads from a distant primary can be slow; a closer replica can reduce round‑trip time.
• What happens if we ignore it:
  • Write latency increases because the primary must lock rows for every read.
  • The application may become unresponsive under load.
  • A single failure can cause downtime.
• Real‑world usage:
  • E‑commerce sites use read replicas to serve product catalogs while writes update inventory.
  • Social networks replicate user profiles to serve feeds.
  • SaaS dashboards read analytics from replicas while writes happen in the background.

2. Mental model

Analogy

Think of a library that has one master copy of a book (the primary). Every time someone wants to read the book, they must go to the master copy. If many people come at once, the master copy becomes a bottleneck. To solve this, the library creates duplicate copies (read replicas) that people can read from freely. The master still updates the original, and the duplicates are periodically refreshed.

Engineering model

• Primary (master): Handles all writes and is the source of truth.
• Replica(s): Read‑only copies that receive changes from the primary via replication streams.
• Replication lag: The time between a write on the primary and its appearance on a replica.
• Consistency model: Usually eventual – replicas may be slightly stale.

3. Deep explanation

4. How it works

1. Primary writes: Application sends a write to the primary. The primary writes to its WAL and applies the change.
2. WAL streaming: The primary streams WAL entries to each replica over a persistent TCP connection.
3. Replica replay: Each replica receives WAL entries, replays them, and updates its local copy.
4. Read routing: Application routes read queries to replicas (often via a load balancer or a routing library).
5. Consistency check: If a read must be strongly consistent, the application can read from the primary or use a read‑your‑writes pattern (e.g., read from primary after a write).
6. Failover: If the primary fails, a replica can be promoted to primary (often automatically by an orchestrator).

sequenceDiagram
    participant App as Application
    participant Primary as Primary DB
    participant Rep1 as Replica 1
    participant Rep2 as Replica 2

    App->>Primary: WRITE (INSERT)
    Primary->>Primary: WAL entry
    Primary->>Rep1: WAL stream
    Primary->>Rep2: WAL stream
    Rep1->>Rep1: Replay WAL
    Rep2->>Rep2: Replay WAL

    App->>Rep1: READ (SELECT)
    App->>Rep2: READ (SELECT)

5. Practical implementation

Node.js + PostgreSQL example

// db.ts
import { Pool } from 'pg';

// Primary pool (writes)
export const primaryPool = new Pool({
  connectionString: process.env.DATABASE_URL, // e.g., postgres://user:pass@primary:5432/app
  application_name: 'app-primary',
});

// Replica pool (reads)
export const replicaPool = new Pool({
  connectionString: process.env.DATABASE_REPLICA_URL, // e.g., postgres://user:pass@replica:5432/app
  application_name: 'app-replica',
});

// service.ts
import { primaryPool, replicaPool } from './db';

export async function createUser(name: string, email: string) {
  const client = await primaryPool.connect();
  try {
    await client.query('BEGIN');
    const res = await client.query(
      'INSERT INTO users(name, email) VALUES($1, $2) RETURNING id',
      [name, email]
    );
    await client.query('COMMIT');
    return res.rows[0].id;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

export async function getUser(id: number) {
  // Reads go to replica
  const res = await replicaPool.query('SELECT * FROM users WHERE id = $1', [id]);
  return res.rows[0];
}

Key points

• Separate pools keep connections isolated; writes never hit replicas.
• application_name helps DB admins identify traffic sources.
• In production, use a load balancer (e.g., PgBouncer) to distribute read traffic across many replicas.

Docker Compose for a simple replica setup

# docker-compose.yml
version: '3.9'
services:
  db-primary:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: app
    volumes:
      - db-primary-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  db-replica:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: app
    volumes:
      - db-replica-data:/var/lib/postgresql/data
    depends_on:
      - db-primary
    command: >
      postgres -c wal_level=replica
               -c max_wal_senders=5
               -c hot_standby=on
    ports:
      - "5433:5432"

volumes:
  db-primary-data:
  db-replica-data:

After starting, configure the replica to connect to the primary:

-- On db-replica
CREATE PUBLICATION app_publication FOR ALL TABLES;
CREATE SUBSCRIPTION app_subscription
  CONNECTION 'host=db-primary port=5432 user=postgres password=secret dbname=app'
  PUBLICATION app_publication;

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

What changes at scale?

• Architecture decisions
  • Decide between physical vs. logical replication.
  • Choose a failover orchestrator (Patroni, Stolon, etc.).
  • Integrate with CI/CD to roll out replication config changes safely.
• Trade‑offs
  • Synchronous replication gives strong consistency but hurts write latency.
  • Asynchronous replication is cheaper but requires careful lag monitoring.
  • Multi‑region replicas improve latency for global users but increase replication traffic.
• Bottlenecks
  • Network bandwidth between primary and replicas.
  • Disk I/O on the primary when many replicas are connected.
  • CPU on replicas when replaying large WAL streams.
• Failure scenarios
  • Network partition: replicas may become isolated; need split‑brain detection.
  • Primary crash: automated promotion must be fast and safe.
• Monitoring
  • Metrics: replication lag, WAL size, pg_stat_replication status.
  • Alerts: lag > 5 s, replication connection lost, disk space low.
• Cost/performance
  • Replicas cost compute and storage; balance read capacity vs. budget.
  • Use spot instances or cheaper regions for read replicas if latency tolerable.
• When NOT to use
  • If the workload is write‑heavy and reads are negligible.
  • If strict read consistency is required (e.g., banking).
  • If the database vendor does not support efficient replication (e.g., some NoSQL).

9. Interview questions

10. Hands‑on task

Task: Add a second read replica to the existing Docker‑Compose PostgreSQL setup and modify the Node.js service to route reads to the replica pool. Then, simulate a replication lag by inserting a large batch of rows on the primary and observe the lag using pg_stat_replication.

Steps:

1. Update docker-compose.yml to add a second replica (db-replica-2).
2. Configure the second replica to subscribe to the primary.
3. In db.ts, add a second replica pool (replicaPool2).
4. In service.ts, modify getUser to randomly pick between replicaPool and replicaPool2.
5. Run the batch insert script on the primary.
6. Query pg_stat_replication on the primary to see lag for each replica.
7. Log the lag in your application and create an alert if lag > 2 s.

11. Quick revision

• Read replicas are read‑only copies that receive changes from a primary via WAL streaming.
• Asynchronous replication gives low write latency but eventual consistency.
• Replication lag is the time difference between primary writes and replica updates.
• Use separate connection pools for reads and writes.
• Monitor pg_stat_replication and set alerts on lag.
• Failover requires promotion of a replica; use orchestrators like Patroni.
• Replicas consume disk and network; balance cost vs. capacity.
• Do not route writes to replicas; they will reject them.
• Logical replication allows table‑level filtering and cross‑version replication.
• Use SSL/TLS for secure replication traffic.
• Load balancers (PgBouncer, HAProxy) distribute read traffic across replicas.

12. References

• PostgreSQL documentation – Replication: https://www.postgresql.org/docs/current/warm-standby.html
• PostgreSQL documentation – Logical Replication: https://www.postgresql.org/docs/current/logical-replication.html
• PgBouncer documentation: https://www.pgbouncer.org/
• Patroni documentation: https://patroni.readthedocs.io/
• Docker Compose PostgreSQL example: https://docs.docker.com/compose/compose-file/
• Prometheus PostgreSQL exporter: https://github.com/prometheus-community/postgres_exporter