Postgres Logical Replication Use Cases, in depth: upgrades, filtered feeds, consolidation, tenant moves, bidirectional sync and their limits

The moving parts, once

On the publisher, wal_level = logical makes the WAL carry enough information to reconstruct rows. A publication names the tables to send, and optionally which operations, which rows (a WHERE clause) and which columns. When a subscriber connects, a walsender process runs the built-in pgoutput plugin through a logical replication slot. The slot records the position the subscriber has confirmed, and the publisher keeps every WAL segment newer than that position. That is the core trade: the slot is what makes replication lossless across disconnects, and it is also what fills your disk if the subscriber goes away.

On the subscriber, CREATE SUBSCRIPTION starts table synchronisation workers that copy each table's existing rows with a consistent snapshot, then an apply worker that replays committed transactions in commit order. A replication origin on the subscriber records the last applied position, so a restart resumes rather than re-applies. The streaming option controls whether large in-progress transactions are sent before commit; parallel (PostgreSQL 16+, the default since 18) applies them with parallel workers.

For UPDATE and DELETE the subscriber has to find the old row, so each published table needs a replica identity: the primary key by default, or a unique index, or REPLICA IDENTITY FULL (the whole old row, slow on large tables without a usable index). PostgreSQL logical replication and CDC covers setup and configuration in more detail; CDC via logical decoding covers the decoding internals.

Use case 1: a major-version upgrade with minutes of downtime

In-place pg_upgrade is fast, but you cannot test the new version under production traffic first, and rolling back after opening for writes is hard. With logical replication you build the new cluster beside the old one, let it catch up, test against it, and cut over in a short write pause.

-- OLD cluster (publisher). wal_level change needs a restart.
ALTER SYSTEM SET wal_level = 'logical';

-- Tables with no primary key and default replica identity cannot replicate UPDATE/DELETE.
SELECT c.oid::regclass AS table_without_identity
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.relreplident = 'd'
  AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary);

CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- NEW cluster: schema only, then subscribe (shell):
--   pg_dump --schema-only --no-publications --no-subscriptions -d app | psql -d app
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-primary dbname=app user=replicator'
  PUBLICATION upgrade_pub
  WITH (copy_data = true);

-- Wait until every table has finished its initial copy ('r' = ready).
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srsubstate <> 'r';

The initial copy can take hours on a large database, and the slot retains WAL for the whole time, so size the publisher's disk for that and watch it. For very large databases, PostgreSQL 17 added pg_createsubscriber, which turns a physical standby into a logical subscriber and so skips the copy entirely. When everything shows r and lag is small, the cutover is mechanical:

-- 1. Stop writes on OLD (maintenance mode, or revoke INSERT/UPDATE/DELETE).
-- 2. On OLD, note the final position:
SELECT pg_current_wal_lsn();                         -- e.g. 3A/1F00C8
-- 3. On NEW, wait until the subscription has received and applied past it:
SELECT subname, latest_end_lsn FROM pg_stat_subscription;
-- 4. Sequences are not replicated. Generate setval calls on OLD, run them on NEW:
SELECT format('SELECT setval(%L, %s, true);', schemaname || '.' || sequencename, last_value)
FROM pg_sequences WHERE last_value IS NOT NULL;
-- 5. On NEW, before it takes any writes:
DROP SUBSCRIPTION upgrade_sub;                       -- also drops the slot on OLD
-- 6. Point the application at NEW.

The sequence step is the one people forget: logical replication sends the rows that used sequence values, not the sequences, so the new cluster's sequences still start from 1 and the first insert after cutover collides with an existing key. Drop the forward subscription before NEW takes writes, or a reverse feed would loop. For a way back, publish on NEW (it needs wal_level = logical) and subscribe OLD with copy_data = false. Note also that since PostgreSQL 17, pg_upgrade can carry logical slots across, but only when the old cluster is already 17 or later.

Use case 2: filtered feeds for analytics and partners

Often the subscriber should not see everything: an analytics replica that needs five columns of orders and no personal data, or a partner feed restricted to one region. PostgreSQL 15 added row filters and column lists to publications, so the filtering happens on the publisher and excluded data never leaves it.

-- Analytics feed: only the columns analysts need, no draft orders (PostgreSQL 15+).
CREATE PUBLICATION analytics_pub
  FOR TABLE orders (id, tenant_id, total_cents, status, created_at) WHERE (status <> 'draft'),
            customers (id, tenant_id, country, created_at);

-- Tenant move: everything for tenant 42, and nothing else.
-- For UPDATE/DELETE, columns in the WHERE must be in the replica identity,
-- so these tables use PRIMARY KEY (tenant_id, id).
CREATE PUBLICATION tenant_42_pub
  FOR TABLE orders    WHERE (tenant_id = 42),
            customers WHERE (tenant_id = 42),
            invoices  WHERE (tenant_id = 42);

Two rules make filters behave. First, for publications that send UPDATE or DELETE, every column in a row filter must be part of the replica identity, and every column list must include the replica identity columns; otherwise the publisher cannot evaluate the filter on old rows or the subscriber cannot find them. Second, filters are evaluated on both old and new row versions, so an UPDATE that moves a row out of the filter arrives as a DELETE, and one that moves a row into it arrives as an INSERT. That keeps the subscriber consistent with the filter, but only if the replica identity carries the filter column. Since PostgreSQL 18, stored generated columns can also be published with the publish_generated_columns option instead of being recomputed on the subscriber.

The subscriber can carry indexes and materialised views tuned for reports. If a published table is partitioned, publish_via_partition_root = true sends changes as if they came from the parent, which lets the subscriber use a different partitioning scheme or none.

Use case 3: consolidation and fan-in

Several services or regional databases each own a slice of data, and you want one place to query across them. Each source publishes; the central server holds one subscription per source. The design work is in keys: if two sources both have orders.id = 1001, the second insert fails with a unique violation and stops that subscription. Use globally unique keys (UUIDs, or a source identifier in the primary key) or land each source in its own schema on the subscriber, then union them in views. Schema changes are the other cost: DDL is not replicated, so every source and the hub must be changed in a coordinated order, subscriber first when adding columns.

Use case 4: moving one tenant to a new cluster

Multi-tenant systems outgrow a cluster, and the usual fix is moving the biggest tenants. With the tenant publication above, the new cluster copies one tenant's rows, then follows its changes while the tenant keeps working. Cutover is a write pause scoped to one tenant: block its writes, wait for the LSN, update routing, unblock, then delete its old rows in batches. The prerequisite is the replica identity rule: tables whose primary key does not include tenant_id can still be copied, but updates and deletes cannot be filtered correctly, which is a strong argument for tenant-leading primary keys from day one.

Use case 5: bidirectional sync, carefully

Two nodes that both accept writes and replicate to each other is the use case with the most appeal and the most risk. Before PostgreSQL 16 it looped: a change applied from A was published again by B and sent back. PostgreSQL 16 added the origin subscription option; with origin = none a subscriber asks only for changes that originated locally on the publisher.

-- Two nodes, each publishing its own local writes (PostgreSQL 16+).
-- On node A:
CREATE PUBLICATION pub_a FOR TABLE items;
-- On node B:
CREATE PUBLICATION pub_b FOR TABLE items;
CREATE SUBSCRIPTION b_from_a CONNECTION 'host=node-a dbname=app user=repl'
  PUBLICATION pub_a WITH (origin = none, copy_data = false);
-- On node A:
CREATE SUBSCRIPTION a_from_b CONNECTION 'host=node-b dbname=app user=repl'
  PUBLICATION pub_b WITH (origin = none, copy_data = false);
-- origin = none: only forward changes that did not themselves arrive by replication,
-- so a change made on A goes to B once and does not loop back.

Loops are solved; conflicts are not. If both nodes update the same row, or insert the same key, there is no global ordering. PostgreSQL 18 detects and logs conflict types such as insert_exists, update_origin_differs, update_missing and delete_missing, and counts them in pg_stat_subscription_stats. Some are applied or skipped and the stream continues; insert_exists, update_exists and multiple_unique_conflicts raise an error and stop apply until you intervene. There is no built-in last-writer-wins or merge. Make bidirectional safe by partitioning ownership: each node writes only its own region's rows or its own key ranges, so conflicts cannot happen by construction.

What it does not do

TRUNCATE is replicated. Failover deserves a note: before PostgreSQL 17, logical slots lived only on the primary, so promoting a physical standby broke every subscription. PostgreSQL 17 lets a subscription be created with failover = true and lets standbys synchronise those slots, with synchronized_standby_slots on the primary keeping the standby ahead of the subscriber. For durability trade-offs between the two replication styles, see database replication architecture.

Failure modes and operating it

-- Publisher: how much WAL is each logical slot holding back?
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- Subscriber: errors and (PostgreSQL 18) per-type conflict counters.
SELECT * FROM pg_stat_subscription_stats;

-- A transaction that stops apply (e.g. insert_exists) can be skipped, losing ALL its changes:
ALTER SUBSCRIPTION upgrade_sub SKIP (LSN '0/14C0378');

The disk-filling slot is the classic outage: a subscriber is dropped, paused or broken, its slot stays, and the publisher keeps WAL until the volume fills and the primary stops. Set max_slot_wal_keep_size (PostgreSQL 13+) so a runaway slot is invalidated rather than taking the primary down, and, since PostgreSQL 18, idle_replication_slot_timeout to invalidate slots left inactive. An invalidated slot means that subscriber must be rebuilt, which is the right failure to choose. Alert on retained WAL, not on time lag alone.

The stuck-apply outage is quieter: one conflicting row stops the apply worker, which retries the same transaction forever while lag climbs. Watch the subscriber log and the error counters, fix the data (usually delete or update the conflicting row on the subscriber) and let apply continue. ALTER SUBSCRIPTION ... SKIP exists but discards the whole transaction, including rows that did not conflict, so it trades a visible stop for invisible divergence. Also watch for initial copies competing with production I/O (limit max_sync_workers_per_subscription) and REPLICA IDENTITY FULL tables scanning on every update. Compare row counts or checksums of key tables periodically; healthy-looking replication can still have diverged.

Choosing

Reach for logical replication when the copy must differ from the source: another major version, a subset, another schema layout, writable, or fed from several places. Reach for physical replication when you need an identical hot standby for failover; it is simpler, ships everything including DDL, and is what HA tooling expects. Reach for an external CDC pipeline built on the same decoding when the destination is not PostgreSQL. Many production systems run all three.

What to do next

1. Run the replica-identity query on your largest database and fix tables without a primary key before you need logical replication.
2. Set max_slot_wal_keep_size on every publisher and add an alert on retained WAL per logical slot.
3. Rehearse a major-version upgrade on a copy: build the subscriber, wait for ready, run the cutover script including sequences, and time the write pause.
4. For filtered feeds, check that every filter column is in the replica identity and that column lists include it.
5. Write down your DDL rollout order (subscriber first for additions) and add it to your migration tooling.
6. If you plan bidirectional sync, design write ownership so conflicts cannot occur, and alert on conflict counters if you are on PostgreSQL 18.