How do I add read replicas to PostgreSQL for an app workload?

To add read replicas to PostgreSQL for an app workload, you typically set up physical streaming replication so one primary handles writes and one or more replicas handle read-only queries.

High-level steps

1. Pick your architecture

   • Self-managed: set up PostgreSQL primary + replica servers.
   • Managed service: use built-in read replicas from AWS RDS, Cloud SQL, Azure Database for PostgreSQL, etc. This is much easier operationally.

2. Configure the primary for replication In postgresql.conf on the primary:

   • wal_level = replica
   • max_wal_senders = N
   • max_replication_slots = N (recommended)
   • wal_keep_size = ... (helps if replicas lag)

   In pg_hba.conf, allow replication connections from the replica host(s).

3. Create a replication user

   CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strongpassword';
   

4. Take a base backup to seed the replica From the replica host:

   pg_basebackup -h PRIMARY_HOST -D /var/lib/postgresql/data -U replicator -Fp -Xs -P -R
   

   The -R option writes replica startup config automatically for newer PostgreSQL versions.

5. Start PostgreSQL on the replica

   • The replica will connect to the primary and begin streaming WAL changes.
   • Confirm it is in recovery mode:

     SELECT pg_is_in_recovery();
     

6. Route read traffic to replicas

   • Your app should send:
     • Writes to the primary
     • Reads to replicas
   • Usually you do this in the application, via a proxy, or via separate connection pools.

---

Important considerations

1. Replication is asynchronous by default

That means replicas may be slightly behind the primary, so a read immediately after a write might not show the latest data.

If your app needs read-your-writes consistency, use one of these patterns:

• Read from the primary after a write for a short time
• Wait until a replica catches up using WAL replay / LSN checks
• Use session-affinity to keep a user on the primary briefly after writes

2. Replicas are read-only

PostgreSQL replicas cannot accept writes unless you promote them to primary.

3. Indexes and query patterns matter

Replicas don’t automatically make slow queries fast. For app workloads:

• Add proper indexes
• Avoid long-running analytical queries on replicas if they impact app reads
• Consider a separate analytics database if needed

4. Monitor lag

Track:

• Replication lag
• WAL generation rate
• Replica disk and CPU usage
• Query performance on replicas

Useful checks:

-- On primary
SELECT * FROM pg_stat_replication;

-- On replica
SELECT pg_last_wal_replay_lsn();
SELECT pg_last_xact_replay_timestamp();

5. Use a failover plan

Replicas are useful for HA, but you also need:

• A promotion process
• DNS/load balancer/proxy updates
• Repointing the old primary after failover

Tools often used:

• Patroni
• repmgr
• pg_auto_failover
• Cloud provider HA/failover features

---

Common app patterns

Simple split: primary for writes, replicas for reads

• Write pool → primary
• Read pool → replicas
• Good for scaling read-heavy workloads

Proxy-based routing

Use a proxy/cluster manager to handle:

• Writer endpoint
• Reader endpoint
• Automatic failover

Examples:

• PgBouncer (connection pooling, not read/write splitting by itself)
• HAProxy
• Pgpool-II
• Cloud provider endpoints

---

Minimal example config

Primary

postgresql.conf

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 512MB

pg_hba.conf

host  replication  replicator  REPLICA_IP/32  scram-sha-256

Replica initialization

pg_basebackup -h PRIMARY_HOST -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

Then start PostgreSQL on the replica.

---

If you want, I can give you:

1. a step-by-step setup for self-managed PostgreSQL on Linux,
2. a cloud-specific guide (AWS/GCP/Azure), or
3. an app-side read/write splitting pattern in your language/framework.