Read Replica Patterns

Production engineering guide for read replica patterns covering patterns, implementation strategies, and operational best practices.

Read replicas are database nodes that maintain a copy of a primary database’s data, optimized for read-heavy workloads. They matter when query latency must scale beyond a single database instance, when read traffic exceeds write traffic, or when database availability and failover are critical. This page details the patterns engineers use to deploy, manage, and tune read replicas across diverse environments, focusing on what works, what breaks, and how to anticipate and fix the common pitfalls.

Deploying Read Replicas

When a database becomes a bottleneck under read load, the first step is to spin up one or more read replicas. The most common approach uses streaming replication via WAL (Write-Ahead Log) logs. The primary database writes all changes to a WAL stream, and replicas apply these changes asynchronously.

To set up a replica, the process begins with a base backup of the primary. This can be done with pg_basebackup for PostgreSQL:

pg_basebackup -D /var/lib/postgresql/15/main -h primary.example.com -U replication_user -P -X stream

The -X stream flag enables WAL streaming, and -P shows progress. After the backup, the replica is started with pg_ctl, and it connects to the primary via replication = on in postgresql.conf:

listen_addresses = '0.0.0.0'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

The replica connects using a replication slot, which prevents the primary from discarding WAL segments before the replica has processed them:

CREATE REPLICATION SLOT my_replica_slot
  LOGICAL
  WITH (startup = '12345678901234567890', streaming = true);

A failure at this stage is common: the replica starts but never catches up. The root cause is usually a mismatch in wal_keep_size or missing replication slot configuration. When the primary runs out of WAL segments, it forces a checkpoint and the replica lags behind. The solution is to monitor pg_stat_replication on the primary and pg_stat_wal_receiver on the replica.

Routing Reads to Replicas

Once replicas are online, the next challenge is routing read queries to them. A basic approach uses a load balancer that forwards read queries to a pool of replicas. The routing logic must account for replica lag, which can cause stale reads.

A common configuration uses a round-robin load balancer with health checks. For example, with HAProxy:

backend read_replicas
  balance roundrobin
  option httpchk GET /health
  server replica-1 10.0.1.10:5432 check port 5432
  server replica-2 10.0.1.11:5432 check port 5432
  server replica-3 10.0.1.12:5432 check port 5432

The /health endpoint returns 200 OK if the replica is up and within a threshold of lag. The health check query is:

SELECT
  now() AS current_time,
  pg_last_wal_receive_lag() AS receive_lag,
  pg_last_wal_replay_lag() AS replay_lag,
  pg_is_in_recovery() AS in_recovery
FROM pg_stat_wal_receiver;

The replica is considered healthy if replay_lag < 5000 milliseconds (5 seconds). A silent failure is when the load balancer assumes replicas are healthy but the replay_lag exceeds 30 seconds. The system serves stale data, and users see outdated results without any alerting.

A more sophisticated pattern uses application-level routing via a read-only connection pool. Applications connect to a dedicated pool of database connections, each tagged with application_name = 'read-only'. The pool uses connection strings that include host lists:

host=primary.example.com,replica-1.example.com,replica-2.example.com
port=5432
dbname=app
user=app_user
application_name=read-only
target_session_attrs=read-only

With target_session_attrs=read-only, the connection pooler (e.g., PgBouncer or PgPool-II) routes each new connection to a replica that is currently in recovery and ready for reads. If no replica is ready, it waits or retries.

Handling Replica Lag and Staleness

Replica lag is not just a performance issue—it’s a correctness problem. A query executed on a replica may return data that is seconds to minutes out of date. To mitigate this, applications can query a "near-ideal" replica using pg_current_wal_lsn() and pg_last_wal_receive_lag().

The pattern involves fetching the current WAL position on the primary and routing reads to the replica that has caught up to that position:

-- On primary: get current WAL position
SELECT pg_current_wal_lsn() AS current_lsn;

-- On replica: check if it has caught up
SELECT
  pg_current_wal_lsn() AS replica_lsn,
  pg_last_wal_receive_lag() AS receive_lag,
  pg_last_wal_replay_lag() AS replay_lag,
  pg_is_in_recovery()
FROM pg_stat_wal_receiver;

A read query can be routed to the replica whose replica_lsn is within 100 MB of current_lsn. This requires the application to maintain a cache of replica status, updated via periodic health checks.

The most common failure here is timing: the primary writes a transaction and immediately returns, but the replica has not yet applied it. A query for a recently inserted record may return NULL even though the record exists. The solution is to add a read_committed transaction on top of the replica connection:

SET transaction_isolation = 'read_committed';
SET application_name = 'read-only';

This ensures that each query sees a consistent snapshot of the data as of the moment it began, even if the replica continues receiving WAL updates.

Scaling and Failover

As read load grows, the number of replicas can be scaled dynamically. A common approach is to use Kubernetes with a StatefulSet for replicas, managed by a custom controller that watches the primary’s pg_stat_replication and adds replicas as needed.

A replica is added by creating a new Pod with a Deployment manifest:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: db-replica
spec:
  replicas: 3
  serviceName: db-replica
  selector:
    matchLabels:
      app: db-replica
  template:
    metadata:
      labels:
        app: db-replica
    spec:
      containers:
      - name: postgres
        image: postgres:15
        ports:
        - containerPort: 5432
        env:
        - name: POSTGRES_USER
          value: replication_user
        - name: POSTGRES_PASSWORD
          value: secret
        - name: POSTGRES_DB
          value: app
        - name: POSTGRES_HOST
          value: primary.example.com
        volumeMounts:
        - name: data
          mountPath: /var/lib/postgresql
      volumes:
      - name: data
        persistentVolumeClaim:
          claimName: db-replica-pvc

The controller polls pg_stat_replication on the primary every 30 seconds. If num_sync_replicas < 2 and lag > 10s, it scales up the StatefulSet by one replica. If lag < 1s for 5 minutes, it scales down.

Failover to a replica is triggered when the primary becomes unavailable. The process involves promoting a replica to primary and updating application configuration.

The promotion is done via pg_ctl:


pg_ctl promote -D /var/lib/postgresql/15/main

The replica then starts writing WAL and becomes the new primary. The application must update its connection strings to point to the new primary. The most common failure is forgetting to update the pg_hba.conf and postgresql.conf files on the promoted replica. The promoted instance may not accept connections from the application tier due to missing host entries in pg_hba.conf:

# pg_hba.conf
host    all             all             0.0.0.0/0               md5
host    replication     replication_user        0.0.0.0/0               md5

Another silent failure is the pg_is_in_recovery() state remaining true after promotion. The application reads from the new primary, but the replica is still in recovery. The solution is to ensure pg_ctl runs with --wait and the postgresql.conf includes:

hot_standby = on
max_standby_streaming_wal_senders = 10

Monitoring and Alerting

Monitoring read replicas requires tracking several metrics, all of which can fail silently if not explicitly observed.

The core metrics are:

A simple monitoring query for a replica:

SELECT
  pg_current_wal_lsn() AS current_lsn,
  pg_last_wal_receive_lag() AS receive_lag_ms,
  pg_last_wal_replay_lag() AS replay_lag_ms,
  pg_is_in_recovery() AS in_recovery,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections
FROM pg_stat_wal_receiver;

Alerts should trigger on:

A common failure is not alerting on pg_is_in_recovery(). When a replica is promoted but the application assumes it’s a primary, pg_is_in_recovery() returns true, and the application may route writes to it, causing write conflicts.

Common Pitfalls and Fixes

Summary

Read replica patterns are not just about adding nodes. They require careful attention to replication setup, routing logic, lag handling, scaling, failover, and monitoring. The patterns described here are not tied to any vendor’s product or UI. They are the universal mechanics of database replication across systems that use WAL streaming, connection pooling, and stateful service deployment. By following these patterns, engineers ensure that read replicas deliver both performance and correctness.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.