Skip to content
Tutorials Center
My learning

Topics

Lesson 71 of 90 ยท Partitioning and Scaling

Streaming and Logical Replication

Understand PostgreSQL replication: WAL, streaming standbys, synchronous vs asynchronous commit, replication slots, publications, subscriptions and lag monitoring.

One PostgreSQL server is a single point of failure and a single machine's worth of read capacity. Replication keeps one or more other servers up to date with a primary server. You use it for failover, for spreading read traffic, for zero-downtime upgrades and for feeding other systems.

PostgreSQL has two kinds. Streaming (physical) replication copies the server's write-ahead log byte for byte, so the standby is an exact binary copy. Logical replication sends row changes for chosen tables, so the target can be a different major version or hold only part of the data. The general ideas, including sharding, are in Replication, Read Replicas and Sharding. Replication needs several servers, so the PostgreSQL commands here are read-only. The runnable examples simulate log positions and lag with ordinary tables.

Everything starts with the WAL

Write first Every change is logged to the WAL before it counts as committed.
LSN Each WAL record has a position, the log sequence number.
Ship it Standbys receive the same WAL and replay it.
1 / 2

The write-ahead log

Before PostgreSQL changes a data page on disk, it appends a description of the change to the write-ahead log (WAL) and flushes it. After a crash it replays the log to get back to a consistent state. Replication reuses the same log: a standby receives the primary's WAL records and applies them in order.

Every position in the log has a log sequence number (LSN), shown like 0/3000148. LSNs only grow, and the distance between two of them is a number of bytes of WAL. That makes lag measurable: a standby that has replayed up to LSN A while the primary is at LSN B is B minus A bytes behind.

Streaming replication

The primary runs a walsender process per standby, and each standby runs a walreceiver. A typical setup:

INI
# primary: postgresql.conf
wal_level = replica          # the default
max_wal_senders = 10
Terminal
# on the new standby machine: clone the primary and write the standby settings
pg_basebackup -h primary.example.com -U replicator -D /var/lib/postgresql/17/main -R -X stream -P

The -R option creates a standby.signal file and writes primary_conninfo into the configuration, so the server starts in recovery mode and keeps following the primary. With hot_standby = on (the default) it also accepts read-only queries. Writes on a standby fail with a message that the transaction is read-only.

SQL
-- Am I connected to a standby?
SELECT pg_is_in_recovery();

The primary also needs a role with the REPLICATION attribute and a matching replication line in pg_hba.conf (see Roles, Privileges and GRANT).

Asynchronous or synchronous?

By default replication is asynchronous: the primary reports COMMIT to the client as soon as the commit record is on its own disk, and the standby catches up a moment later. This is fast, but if the primary dies at the wrong instant, committed transactions that never reached the standby are lost.

Synchronous replication makes the primary wait for named standbys:

SQL
-- postgresql.conf
-- synchronous_standby_names = 'ANY 1 (replica_a, replica_b)'
-- synchronous_commit = on

synchronous_commit has several levels, from weakest to strongest: off, local, remote_write (standby received it), on (standby flushed it to disk) and remote_apply (standby has applied it, so reads there see it). Stronger levels trade commit latency for durability, and a synchronous standby that stops responding can stall every commit on the primary. That is why production setups list two or more candidates and use ANY 1 or FIRST 1. You can also pick the level per transaction (SET LOCAL synchronous_commit = off;) for data you can afford to lose.

The following simulation compares what a client waits for. The numbers are made up, but the shape is real: each extra hop adds a network round trip.

SQL What does each commit wait for?

Press Run (or Ctrl + Enter) to see the result here.

Replication slots

If a standby is down for an hour, the primary may recycle WAL segments it still needs. A replication slot tells the primary "keep every WAL record this consumer has not confirmed yet":

SQL
SELECT pg_create_physical_replication_slot('replica_a');
-- then set primary_slot_name = 'replica_a' on the standby

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

Slots are convenient and dangerous. A slot whose consumer never comes back keeps WAL forever, and the primary's disk fills up. Set max_wal_keep_size/max_slot_wal_keep_size as a safety limit, monitor retained, and drop slots that nothing uses.

Logical replication

Logical replication works with publications (on the source) and subscriptions (on the target), and requires wal_level = logical:

SQL
-- source database
CREATE PUBLICATION shop_pub FOR TABLE customers, orders;

-- target database (the tables must already exist, with the same columns)
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=source.example.com dbname=shop user=replicator'
  PUBLICATION shop_pub;

The subscription first copies the existing rows, then streams changes. Important rules:

  • DDL is not replicated. Create or alter tables on both sides yourself, usually target first for new columns.
  • Sequences are not replicated. The target's sequence values stay where they were; set them before switching writers.
  • UPDATE and DELETE need a replica identity: normally the primary key, otherwise REPLICA IDENTITY FULL (slower).
  • The target is a normal writable database. Conflicting local writes can stop the subscription until you fix them.
  • It works across major versions and operating systems, which makes it a tool for upgrades (see High Availability and Upgrades).

Monitoring lag

On the primary, pg_stat_replication shows how far each standby has got:

SQL
SELECT application_name,
       state,
       sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       replay_lag                                       -- time-based lag
FROM pg_stat_replication;

The columns sent_lsn, write_lsn, flush_lsn and replay_lsn mark the stages of the pipeline. On a standby, pg_last_wal_replay_lsn() and pg_last_xact_replay_timestamp() tell you what it has applied. A growing gap between flush_lsn and replay_lsn means the standby receives data but cannot apply it fast enough, often because long queries on the standby conflict with replay.

This simulation computes the same byte lag from a table of positions:

SQL Replica lag in bytes and pending WAL

Press Run (or Ctrl + Enter) to see the result here.

replica_b has received most of the data but applied far less, a classic sign of replay being blocked on the standby. replica_c is slightly behind the primary's current position but fully caught up with what was sent.

Using read replicas in applications

Replicas scale reads, but asynchronous replicas are slightly stale, and that causes bugs that only appear under load.

  • Read your own writes. A user saves a profile, the next request reads from a replica that has not seen the change yet, and the old data appears. Route a user's reads to the primary for a short time after a write, or use remote_apply.
  • Route by tolerance. Reports, search pages and dashboards can accept staleness. Checkout and account pages cannot.
  • Standby query conflicts. A long query on a standby can be cancelled when replay needs to remove rows it still reads. hot_standby_feedback and max_standby_streaming_delay trade that against bloat on the primary or lag.

Frameworks support this pattern directly. In Laravel, for example, a connection can list read hosts and a write host, and sticky sends reads to the writer after a write in the same request:

PHP
'pgsql' => [
    'read'   => ['host' => ['replica-1.internal', 'replica-2.internal']],
    'write'  => ['host' => ['primary.internal']],
    'sticky' => true,
    'driver' => 'pgsql',
    'database' => 'shop',
],

Sharding and scaling out

Replication copies all the data to more servers, so it scales reads and availability, but not writes and not data size: every server still holds everything, and every write still goes to one primary. When one machine can no longer hold the data or absorb the writes, the next step is sharding: splitting the data so that different servers own different rows.

Partitioning is not sharding

The two words are often mixed up:

Partitioning Sharding
Where the pieces live In one database on one server On several servers
Who routes queries PostgreSQL's planner The application, a proxy or an extension
Cross-piece joins and transactions Normal Hard, slow or restricted
Main goal Manageability, pruning, cheap deletes Capacity for data size and write throughput

Declarative partitioning is where you should start, and it is often all you need. Shard only when a single well-tuned server (see Configuration Tuning and Query Tuning Patterns) with partitions and read replicas can no longer cope.

Choose the cheap options first

  1. Tune and add indexes. Most "we need to scale" problems are missing indexes or heavy queries.
  2. Scale up. Modern servers have hundreds of gigabytes of memory and fast NVMe disks; a bigger machine is the cheapest fix.
  3. Offload reads to replicas, and cache with an application cache.
  4. Archive old data, or drop old partitions (see Partition Management).
  5. Only then shard.

Ways to shard PostgreSQL

Application-level sharding. The application picks a shard key (customer id, tenant id), maps it to a database and connects there. It works with plain PostgreSQL, needs no extensions, and gives you full control, at the price of writing the routing, migrations across all shards and a plan for resharding. A multi-tenant SaaS (see Project: Multi-Tenant SaaS) is the best fit, because a tenant's rows rarely need to be joined with another tenant's.

Citus. An extension (open source) that turns a group of PostgreSQL servers into a distributed database: a coordinator node accepts SQL and distributes it to worker nodes.

SQL
-- PostgreSQL with the Citus extension
CREATE EXTENSION citus;

SELECT create_distributed_table('orders', 'tenant_id');       -- spread rows by tenant
SELECT create_distributed_table('order_items', 'tenant_id',
                                colocate_with => 'orders');   -- keep related rows together
SELECT create_reference_table('countries');                   -- small table copied to every node

Queries that filter on the distribution column go to one worker; joins between tables co-located on the same key are done locally on each worker; reference tables can be joined anywhere. Queries that cross shards still work, but cost more. Availability varies by version and hosting, so check the extension's current documentation and your managed provider's support.

Foreign-table sharding. With postgres_fdw a partitioned table can have foreign tables as partitions, each living on a different server (see Foreign Data Wrappers):

SQL
-- PostgreSQL, run on the coordinating server
CREATE TABLE events (
  id bigint, tenant_id int, payload jsonb
) PARTITION BY HASH (tenant_id);

CREATE FOREIGN TABLE events_p0
  PARTITION OF events FOR VALUES WITH (MODULUS 2, REMAINDER 0)
  SERVER shard_a;

CREATE FOREIGN TABLE events_p1
  PARTITION OF events FOR VALUES WITH (MODULUS 2, REMAINDER 1)
  SERVER shard_b;

This is easy to try, and partition pruning sends a query that filters on tenant_id to one shard. But it has limits: cross-server transactions are not atomic in the usual sense, foreign partitions cannot hold unique indexes that span partitions, and push-down of aggregates and joins is limited. Treat it as a light-weight option rather than a full distributed database.

Managed distributed offerings exist from cloud vendors, and some projects layer PostgreSQL-compatible distributed databases on top of other storage engines. These are different systems with their own trade-offs; test your queries against them rather than assuming full compatibility.

Choosing a shard key

The shard key decides everything. A good key:

  • appears in almost every query's WHERE clause, so most queries touch one shard;
  • has many distinct values and an even spread of data and traffic, avoiding "hot" shards (a single huge customer can still overload one shard);
  • is part of the primary key and foreign keys, so related rows stay together.

A key that is wrong for your access patterns turns most queries into cross-shard scatter-gather queries, which is slower than the single server you started with.

What sharding costs you

  • Joins and aggregates across shards are slow or unsupported.
  • Transactions across shards need two-phase commit or application compensation (see Transactions and ACID).
  • Unique constraints and sequences must include the shard key, or use globally unique ids such as UUIDs (see UUIDs and Identity Columns).
  • Schema changes and backups multiply by the number of shards.
  • Resharding (changing the number of shards) means moving data, and the routing rule decides how much has to move.

The last point is easy to underestimate. This program routes tenants with the naive rule hash % number_of_shards and with a rule that uses a fixed number of small logical buckets mapped to servers, then adds a shard and counts how many tenants have to move:

Python How much data moves when you add a shard?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With the naive rule almost every tenant changes shard (roughly four fifths when going from 4 to 5 servers), so nearly all data would have to move. With fixed buckets, only the share the new server takes over moves, about one fifth. That is why serious sharding schemes, including Citus's shard counts, use many more logical shards than servers.

A scaling ladder

Scale PostgreSQL step by step

Tune and index Fix slow queries first.
Partition Keep big tables manageable.
Read replicas Spread read traffic.
Shard Only when one primary is not enough.

Each step costs more complexity than the one before it.

1 / 2

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is an LSN?

    A log sequence number identifies a position in the WAL. The difference between two LSNs is a number of bytes, which is how replication lag is measured.
  2. 2. With asynchronous streaming replication, what can happen if the primary crashes right after a commit?

    The primary acknowledges the commit once its own WAL is flushed. The standby may be a few records behind, so a failover can lose the most recent transactions.
  3. 3. Which statement about logical replication is correct?

    Logical replication sends row changes for published tables and works between major versions. DDL and sequences are not replicated, and wal_level must be logical.
  4. 4. Why can an unused replication slot be dangerous?

    A slot tells the primary to retain all WAL the consumer has not confirmed. If the consumer never returns, the retained WAL grows without limit unless you cap it or drop the slot.
  5. 5. A user updates their email and the next page still shows the old one. What is the likely cause?

    Asynchronous replicas lag slightly. Read-your-own-writes needs sticky routing to the primary or a stronger synchronous_commit level.

Summary

  • Replication reuses the write-ahead log; positions in it are LSNs and differences are bytes.
  • Streaming replication creates a read-only physical copy; logical replication copies chosen tables as row changes.
  • Asynchronous commit is fast but can lose recent transactions on failover; synchronous commit waits for standbys at the cost of latency.
  • Replication slots keep WAL for consumers, so cap and monitor them.
  • Logical replication does not copy DDL or sequences, and needs replica identities for updates and deletes.
  • Watch pg_stat_replication, and design applications for slightly stale replicas.

Next: Backup, Restore and Point-in-Time Recovery.