The team needs a read replica for analytics. Somebody pulls up the Postgres docs, sees “streaming replication” and “logical replication” listed under “Replication,” and picks one based on which sounds more flexible. Three months later they discover that logical replication does not replicate DDL by default, the analytics queries are running on a stale snapshot, and the replica needs babysitting through every schema migration.

Streaming and logical are not interchangeable; they solve different problems. Picking the right one (for a read replica, an HA standby, or a cross-version migration) is one of those database decisions where five minutes of clarity saves five months of wrong-tool-for-the-job. This post is the difference, in operational terms.

What each does, briefly

Streaming replication ships the write-ahead log (WAL) byte-for-byte from the primary to the replica. The replica replays the WAL and is, at any given moment, a binary-identical copy of the primary. Every table, every index, every system catalog. Replicas can be promoted to primary in a failover. Default mode is read-only.

Logical replication publishes row-level changes on a per-table basis, decoded from the WAL. The subscriber is a fully separate Postgres instance, possibly a different major version, possibly with different schema, with its own indexes. You choose which tables to replicate.

The mental model: streaming is “make this exact server somewhere else.” Logical is “send me these specific tables’ changes.”

When to use streaming

Streaming is the right choice for:

High-availability standby. You want a hot replica that can be promoted in seconds if the primary fails. Same version, same schema, same data. Streaming with synchronous_commit or quorum-based commit gives you this.

Read scaling for the same workload. Analytics queries on the same schema as the primary. Streaming lag is typically milliseconds; the replica is “live” for read purposes.

Backups via pg_basebackup. A streaming replica that is paused or lagged becomes a point-in-time backup source.

The downside: streaming is all-or-nothing. You cannot replicate a subset of tables. The replica must be the same major version. A standby cannot accept writes.

When to use logical replication

Logical is the right choice for:

Cross-version upgrades. Promote logical replication to use as a zero-downtime migration tool. The new version subscribes to the old; once caught up, you switch traffic to the new and decommission the old. This is how you upgrade Postgres 14 to 15 without a maintenance window.

Selective replication. Replicate only the tables that downstream services need. A reporting database does not need every internal queue table.

Multi-source / fan-out. One subscriber consuming from multiple publishers, or one publisher fanning out to many subscribers with different schemas.

Cross-cloud or cross-network. The subscriber is a normal Postgres connection; it works anywhere TCP works. Streaming requires more network access (replica connects on the replication port, sometimes a separate network).

The downside: logical replication does not replicate DDL. Schema changes have to be applied on both sides manually or via a tool. Sequences are not replicated. Large transactions stream all-or-nothing, which can produce unexpected lag.

Setting up streaming

On the primary (postgresql.conf):

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

pg_hba.conf allows the replica to connect as a replication user.

On the replica:

pg_basebackup -h primary -D /var/lib/postgresql/data -U replica_user -P -W -R

-R writes a standby.signal file. Start the replica; it streams from the primary automatically.

For monitoring, the replica exposes its lag:

-- On the replica:
SELECT
  pg_last_wal_receive_lsn() AS receive_lsn,
  pg_last_wal_replay_lsn()  AS replay_lsn,
  pg_last_xact_replay_timestamp() AS replay_ts,
  now() - pg_last_xact_replay_timestamp() AS lag;

Alert on lag > 30s. Anything more than that means the replica cannot keep up with writes (disk, network, or query contention).

Setting up logical replication

On the publisher (postgresql.conf):

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Create a publication:

CREATE PUBLICATION mypub FOR TABLE users, orders, items;
-- or for all tables: FOR ALL TABLES;

On the subscriber:

CREATE SUBSCRIPTION mysub
  CONNECTION 'host=publisher.example.com user=repl_user dbname=appdb password=...'
  PUBLICATION mypub;

The subscriber takes an initial snapshot of each table, then streams subsequent changes. Initial sync of a large table is slow, so plan for it.

Monitor the slot:

SELECT slot_name, active, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

A growing lag means the subscriber is falling behind or has disconnected. Critical: an inactive slot pins WAL forever. If a subscriber goes away permanently, drop the slot or your primary fills up its disk.

The four operational gotchas

1. DDL is not replicated by logical. When you alter a table on the publisher, the subscriber does not learn about it. Apply DDL on both sides manually, in sync. Tools like pg-easy-replicate help with major-version upgrades by ordering DDL correctly.

2. Sequences are not replicated. New IDs are assigned on the publisher; the subscriber’s sequence does not advance. After failover, you must explicitly bump the subscriber’s sequences:

SELECT setval('users_id_seq', (SELECT max(id) FROM users) + 1);

3. Large transactions buffer. Logical replication ships transactions atomically. A 10 GB UPDATE on the publisher is held in the slot until commit, then streamed all at once. Spikes in lag, network, and subscriber memory. Postgres 14+ supports streaming of in-progress transactions if both sides are 14+ and the publication enables it.

4. Inactive slots pin WAL. Already mentioned but worth repeating. If a subscriber crashes and never returns, the slot stays. WAL accumulates. Disk fills. Your primary database goes down.

-- Run periodically:
SELECT slot_name FROM pg_replication_slots WHERE NOT active;

Alert on any non-active slot older than a day.

A practical decision tree

Ask three questions:

1. Do you need the replica as a failover target? Yes → streaming. (Logical replication does not give you a failover-ready standby.)

2. Do you need to replicate only some tables, or to a different schema, or across major versions? Yes → logical.

3. Otherwise (general-purpose read replica, same schema, same version)? Streaming. It is simpler, more battle-tested, and has fewer operational gotchas.

For most teams, the answer is streaming. Logical replication is the right tool when the use case actually requires it: version upgrades, cross-DB sync, selective replication. Reaching for logical first because it sounds flexible is usually a mistake.

Cross-version upgrades with logical

The single best use of logical replication is the green-blue Postgres upgrade. Step-by-step:

1. Spin up the new Postgres version, empty.
2. Apply DDL (schema) to the new version.
3. Create a publication on the old, a subscription on the new.
4. Wait for initial sync to complete.
5. Wait for ongoing sync lag to drop to zero.
6. Pause writes briefly (10s).
7. Bump sequences on the new version.
8. Switch application to the new database.
9. Tear down the old once you are confident.

This pattern moves you from version 14 to 16 in one move with seconds of downtime. The naive “stop, dump, restore on the new” version takes hours of downtime for a large database.

What about pglogical, BDR, and other extensions?

pglogical is a more flexible logical replication system, predating Postgres’ built-in support. It is still maintained but the built-in is sufficient for most needs.

BDR (Bi-Directional Replication) is multi-master Postgres, meaning multiple writeable replicas. Operationally complex; almost nobody actually needs it. The right answer for “I need writes from two places” is usually “shard the writes by tenant” or “use a write-ahead queue,” not “run multi-master.”

For most workloads, streaming or built-in logical is enough.

The takeaway

Streaming is “an exact copy of this database.” Logical is “selected changes from these tables.” HA standby and same-schema read replicas → streaming. Cross-version upgrades, selective replication, cross-cloud → logical.

The operational gotchas of logical (DDL, sequences, slot management) are real and bite teams who pick logical because it sounds more flexible. Pick the simpler tool when both fit. The next time someone says “we need a read replica,” the answer is “streaming, unless we have a specific reason logical is required.”

A note from Yojji

The kind of database operations work that turns a Postgres major-version upgrade from a maintenance window into a routine deploy (logical replication setup, slot monitoring, sequence reconciliation) is the kind of long-haul backend engineering Yojji’s teams ship for clients.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. They specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and the kind of database engineering that decides whether your data is robust or one bad slot away from a disk-full incident.