How does replication work in PostgreSQL?

Question

Accepted Answer

**Replication** copies data from a **primary** database to one or more **replica** (standby) servers — for high availability (failover if the primary fails) and read scaling (distribute read queries). PostgreSQL primarily uses **streaming replication** (physical) based on the write-ahead log (WAL).

## Streaming (physical) replication

```text
Primary  ──(streams WAL: the write-ahead log of changes)──▶  Replica(s)
  ✓ The primary handles all WRITES
  ✓ Replicas continuously apply the primary's WAL → stay in sync (near-real-time)
  ✓ Replicas can serve READ queries ("hot standby") → scale reads
```

The primary streams its **WAL** (write-ahead log — a record of every change) to replicas, which apply it to stay synchronized. Replicas are read-only copies that can serve read queries.

## The benefits: HA and read scaling

```text
HIGH AVAILABILITY → if the primary fails, a replica can be PROMOTED to primary (failover)
  → minimal downtime, no data loss (with synchronous replication)
READ SCALING → route read-only queries to replicas → offload the primary
  → handle more read traffic (great for read-heavy workloads)
BACKUPS → take backups from a replica without loading the primary
```

## Synchronous vs asynchronous replication

```text
ASYNCHRONOUS (default) → the primary commits without waiting for replicas
  ✓ faster writes (no wait)   ✗ a tiny window of potential data loss on failover
SYNCHRONOUS → the primary waits for a replica to confirm before committing
  ✓ no data loss on failover  ✗ slower writes (waits for the replica)
→ Choose based on the durability vs performance trade-off.
```

## Logical replication (a different model)

```text
LOGICAL replication → replicates specific TABLES/changes (not the whole cluster), based
on a publish/subscribe model. Enables: selective replication, cross-version, and
replicating to different schemas → good for migrations, integrations, partial replication.
```

## High availability in practice

```text
Tools like Patroni, repmgr, or cloud-managed Postgres automate FAILOVER (detecting
primary failure and promoting a replica) → near-automatic high availability.
A load balancer / connection router directs writes to the primary, reads to replicas.
```

## Why it matters

Replication is important for building **highly-available and scalable** PostgreSQL deployments, so understanding it is valuable senior-level knowledge for production database architecture.

The two core benefits are critical for serious applications: **high availability** (if the primary database fails, a replica can be promoted to take over, minimizing downtime — essential for systems that must stay available) and **read scaling** (routing read-only queries to replicas offloads the primary, letting the system handle far more read traffic — valuable for read-heavy workloads).

Understanding **streaming replication** (the primary streaming its WAL to replicas that stay synchronized and can serve reads) is the foundation.

Knowing the **synchronous vs asynchronous** trade-off is important: asynchronous (the default — faster writes but a small window of potential data loss on failover) versus synchronous (no data loss but slower writes) — a durability-vs-performance decision affecting reliability.

Understanding **logical replication** (replicating specific tables via pub/sub, useful for migrations, cross-version, and partial/selective replication) broadens the options.

Knowing that **HA tooling** (Patroni, repmgr, managed cloud Postgres) automates failover, and that read/write routing directs traffic appropriately, is practical architectural knowledge.

Since high availability and scalability are essential requirements for production database systems, and since replication (with its HA failover, read scaling, the sync/async durability trade-off, and logical replication for flexibility) is the primary mechanism PostgreSQL provides for them, understanding replication is important senior-level knowledge for architecting reliable, scalable Postgres deployments — a frequently-relevant concern for production systems and a topic demonstrating understanding of database availability and scaling that's expected for senior database/backend roles.