How to Route Reads to Replicas and Writes to Primary in PostgreSQL

1. Replication Type in Postgres

PostgreSQL uses physical streaming replication (the standard and most common method).

• One Primary (read-write) + One or more Replicas (read-only hot standbys).
• This is a primary-replica (master-slave) architecture — not active-active.

2. How Replication Works

1. The primary records all changes in the WAL (Write-Ahead Log).
2. Replicas connect to the primary and stream the WAL changes in near real-time.
3. Replicas apply the changes and stay in sync.

• Lag: Replicas are usually a few milliseconds to seconds behind (depending on load, network, etc.).
• You can promote a replica to become the new primary during failover.

3. Is it Time-Based?

No, it's not time-based (like scheduled batch jobs).
It is event-driven / continuous streaming:

• Changes are replicated as soon as they are written to the WAL on the primary.
• Very low latency in good conditions.

You can configure synchronous replication if you want zero data loss (primary waits for replica confirmation before committing), but this slows down writes.

4. Scaling Benefits with Replicas

• Read scaling: Offload SELECT queries to replicas (use connection routing or different connection strings).
• High Availability: If primary fails, promote a replica (manual or with tools like Patroni).
• Not write scaling: Writes still go only to the primary.

5. In Kubernetes Context (Your Setup)

• Use a StatefulSet with multiple replicas.
• Primary is usually the first pod (postgres-0).
• Replicas stream from it.
• You need:
  • A Service pointing to the primary for writes.
  • Another Service (or labels) for read-only replicas.

This is why many people use operators (CloudNativePG, Crunchy, Zalando) — they automate primary election, connection routing, failover, etc.