DifficultyIntermediatePostgres version14+ (row filtering: 15+)Risk levelLow — replication is read-only on the subscriber by default
Physical replication copies every byte of the WAL stream. It works, it's fast, and for high-availability failover it's the right tool. But it comes with constraints: the subscriber must be the same Postgres major version, same architecture, same everything. You can't use it to migrate from Postgres 14 to 16 without downtime, and you can't use it to sync a subset of tables to an analytics replica.
Logical replication solves both problems. It decodes the WAL into row-level changes and replays them on a target database that can be a different major version, a different machine, or even a different Postgres-compatible system.
The model
Logical replication uses two objects: a publication on the source and a subscription on the target.
The publication declares what to replicate:
-- On the source database
CREATE PUBLICATION my_pub FOR TABLE orders, customers;
-- Or replicate everything
CREATE PUBLICATION my_pub FOR ALL TABLES;
The subscription connects to the source and pulls the stream:
-- On the target database
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=source-db port=5432 dbname=myapp user=replicator password=secret'
PUBLICATION my_pub;
That's the core. Postgres handles the initial snapshot (copying current data) and then streams subsequent changes. The subscription connection uses a dedicated replication slot on the source.
Row and column filtering (Postgres 15)
Postgres 15 added two capabilities that make logical replication practical for more use cases.
Row filtering — include only rows matching a WHERE clause:
CREATE PUBLICATION orders_recent
FOR TABLE orders
WHERE (created_at > now() - interval '90 days');
Column lists — replicate only specified columns:
CREATE PUBLICATION orders_pub
FOR TABLE orders (id, customer_id, status, total_cents, created_at);
This is useful when you want an analytics replica but the source table contains PII you shouldn't copy.
Use cases
Zero-downtime major version upgrade. Set up a Postgres 16 instance as a logical subscriber to your Postgres 14 source. Once it's caught up (replication lag near zero), switch your application to the new instance and drop the subscription. Downtime is measured in seconds, not hours.
Selective cross-database sync. A monolith with one large database can publish specific tables to a dedicated reporting database without splitting the application.
ETL fan-out. Publish events to a subscriber that writes to a data warehouse format or streams to Kafka via logical decoding plugins like pgoutput or wal2json.
What logical replication does not replicate
Several things are explicitly outside scope:
Sequences. The sequence value is not replicated. If the target has an auto-increment column that receives replicated rows, you need to set the sequence to a safe starting value manually, or the target will try to insert with sequence values that collide with the replicated rows.
DDL. ALTER TABLE, CREATE INDEX, and schema changes are not replicated. You must apply schema changes to both source and target manually before modifying the publication.
Large objects. pg_largeobject data is not replicated.
Truncate (pre-14). TRUNCATE is replicated in Postgres 14+. Earlier versions silently skip it.
Operational gotchas
The initial snapshot can be slow. When you create a subscription, Postgres copies the current table data as a snapshot. For large tables this takes time and holds a table-level lock during the export phase. Plan accordingly — run the initial sync during low-traffic periods.
Replication slots persist and accumulate WAL. A replication slot on the source prevents WAL deletion until the subscriber consumes it. If the subscriber falls behind or disconnects, disk usage on the source grows. Monitor pg_replication_slots and set max_slot_wal_keep_size to cap WAL retention.
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;
Table must have a replica identity. For UPDATE and DELETE to replicate, each table needs a replica identity — usually the primary key. Tables without a primary key default to REPLICA IDENTITY FULL (compare full row on update/delete), which is slow. Add a primary key before replicating those tables.
What logical replication does not replace
Physical replication is still correct for high-availability failover. It's faster, simpler, and the replicas can serve read traffic. Logical replication adds flexibility but also complexity — you're responsible for schema sync, sequence management, and monitoring the replication slot.
For small databases, pg_dump | psql is often the right answer. The initial complexity of setting up logical replication only pays off when the database is large enough that a full dump-and-restore would cause unacceptable downtime.
The API is clean. The failure modes are specific. Know them before your first production cutover.
Building something? builds.anethoth.com is a public build ledger — proof that a product is really being built. Free to list your project.