Logical Replication Best Practices

Watch for DDL limitations; schema changes are hard. These rules keep logical replication stable across upgrades and schema evolution.

How to Use This List

• Review before adding tables to a publication.
• Use as a checklist during major version blue/green cutovers.
• Pair with alerts on logical slot lag bytes.
• Share with application teams who own DDL migrations.

A - Schema and Identity

• Every published table has a primary key or explicit REPLICA IDENTITY. Updates and deletes replicate reliably.
• Prefer REPLICA IDENTITY DEFAULT (PK) over FULL when possible. FULL increases WAL volume.
• Create subscriber schema before CREATE SUBSCRIPTION. Tables, types, and indexes must exist on subscriber.
• Treat DDL as a coordinated migration on both sides. Logical replication does not apply ALTER TABLE automatically.
• Use expand-contract for column changes. Add column on subscriber first, then publisher, then backfill.

B - Publications and Subscriptions

• Prefer explicit table lists over FOR ALL TABLES in production. Avoid accidental replication of sensitive tables.
• Run ALTER SUBSCRIPTION ... REFRESH PUBLICATION after ADD TABLE. New tables do not copy otherwise.
• Document row filters as immutable predicates. Volatile functions break publication definitions.
• One logical slot per subscription; name slots clearly. Orphan slots fill publisher disk.
• Drop subscriptions with DROP SUBSCRIPTION to clean publisher slots. Manual slot drops if connection blocked.

C - Operations and Lag

• Alert on logical slot confirmed_flush_lsn lag bytes. Same disk risk as physical slots.
• Pause subscriptions (enabled = false) during subscriber maintenance. Resume with lag check.
• Keep subscriber tables read-only for published data. Conflicts stop apply workers.
• Sync sequences before cutover in major upgrades. Prevent duplicate key errors on PG 18.4 green.
• Do not route subscriptions through PgBouncer transaction pool. Use direct PostgreSQL connections.

D - Topology and HA

• Do not replace physical HA with logical only for tier-0. Keep Patroni physical standby for failover.
• Size max_replication_slots and max_wal_senders for logical + physical consumers. Combined load on primary.
• Verify Patroni 3.x slot sync after failover. Logical slots must exist on new primary.
• Test major upgrade path in staging quarterly. Measure freeze window and sequence sync.

E - Security and Compliance

• TLS on subscription CONNECTION string (sslmode=verify-full). Encrypt cross-network replication.
• Dedicated replication user per subscriber. Least privilege on publisher.
• Column lists for PII minimization on downstream. Row filters are not a substitute for authz on subscriber.
• Audit who can CREATE PUBLICATION. Publications expose change streams.

FAQs

Related

• Logical Replication Basics - setup
• Publications & Subscriptions - filters
• Upgrade with Logical Replication - major version cutover
• Replication Slots - slot monitoring

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.