Primary-Replica Replication and Read Replicas

1. Introduction

Replication copies data from a primary (read-write) server to one or more replicas (read-only). PostgreSQL supports streaming replication (real-time WAL shipping), logical replication (selective table-level sync via pgoutput), and file-based log shipping. Read replicas scale read workloads horizontally and provide hot standby for failover.

2. Why It Matters

A single PostgreSQL instance has finite CPU, memory, and IOPS. Read-heavy applications (dashboards, APIs, reporting) can overwhelm a primary. Replicas distribute read traffic, and if the primary fails, a replica can be promoted to take its place. Without replication, a single server failure means complete downtime and potential data loss.

3. Real-World Analogy

Think of a primary as the head chef writing recipes, and replicas as sous-chefs who read those recipes to prepare dishes for customers. The head chef is the only one who modifies recipes (writes), but many sous-chefs can read them simultaneously (reads). If the head chef is unavailable, a sous-chef can step up (promote).

4. How It Works

• Streaming Replication (physical): The primary ships WAL records to replicas in real-time. Replicas replay the WAL to stay in sync. Synchronous mode waits for replica ACK before COMMIT returns (zero data loss). Asynchronous mode may lose a few transactions on failover.
• Logical Replication (PG 10+): Uses a publication/subscription model. The primary publishes specific tables; subscribers choose which to replicate. Supports cross-version replication and selective table syncing.
• Read Replicas (Hot Standby): Replicas accept read queries (hot_standby = on). They apply WAL with a delay (max_standby_streaming_delay) to avoid canceling long-running queries.

5. Internal Architecture

The primary has a wal_sender process for each connected replica. Each replica runs a wal_receiver that writes incoming WAL to the replica's pg_wal directory, then a startup process replays it. Replication lag is measured as the byte/second difference between primary and replica WAL positions. Synchronous replication uses a synchronous_standby_names list to determine which replicas must acknowledge before COMMIT.

6. Visual Explanation

The diagram shows a primary server with two streaming replicas, WAL flow via wal_sender/wal_receiver, and a load balancer routing writes to the primary and reads to replicas.

7. Practical Example

8. Common Mistakes

• Ignoring replication lag: Reads from a lagging replica return stale data. Monitor lag and route critical reads to the primary.
• Running long queries on replicas: Long reads conflict with WAL replay, causing "canceling statement due to conflict with recovery" errors.
• Synchronous replication with distant replicas: Network latency adds directly to COMMIT latency. Use async for cross-region replicas.
• Not testing failover: Regularly test manual promotion (pg_promote()) to ensure replicas can take over.

9. Quick Quiz

10. Scenario-Based Challenge

Design a 3-node PostgreSQL cluster for an e-commerce platform. The primary handles all writes (orders, payments). Two replicas serve read traffic (product search, order history). Configure synchronous replication to ensure zero data loss for financial transactions, while keeping async replication for reporting replicas. Design the connection routing strategy.

11. Debugging Exercise

12. Interview Questions

• Q: What's the trade-off between synchronous and asynchronous replication?
  A: Synchronous guarantees zero data loss (replica confirms before COMMIT returns) but adds latency. Asynchronous has lower latency but may lose unconfirmed transactions on primary failure.
• Q: Can you write to a read replica?
  A: No. Hot standby replicas are read-only. Attempting writes returns an error. To write, you must promote the replica to primary using pg_promote() or pg_ctl promote.

13. Production Considerations

• Replication slots: Use physical replication slots to prevent the primary from recycling WAL before replicas consume it. Monitor slot lag to prevent disk fill-up.
• Connection pooling with replicas: Use PgBouncer or ProxySQL to route read queries to replicas automatically.
• Monitoring: Alert on pg_stat_replication.replay_lag exceeding your SLA threshold.
• Cascading replication: Replicas can replicate to other replicas, forming a tree. Useful for geographic distribution.