Publication/Subscription Models

The publication/subscription model is the declarative control plane of PostgreSQL logical replication architecture: a publication on the primary declares which tables and which DML operations enter the change stream, and a subscription on the consumer declares who reads it and from which replication slot. Everything else — decoding CPU, WAL retention, apply latency, fan-out topology — is a downstream consequence of how those two objects are defined. This page covers the object model, a runnable setup sequence, the parameters that govern durability and throughput, the catalog queries that expose lag, and the failure signatures that put a production primary at risk.

Get this layer wrong and the consequences are concrete, not theoretical. A publication that omits a table’s replica identity throws at decode time and stalls the whole slot; a subscription whose consumer disconnects pins restart_lsn and grows pg_wal until the primary hits disk full; a publication that streams every column of a wide table doubles egress and apply-worker CPU for data no downstream consumer reads. The design decisions below are the difference between a change-data-capture (CDC) topology that runs untouched for years and one that pages the on-call engineer weekly.

Prerequisites & Configuration Objects #

Before either object can be created, the publisher must be provisioned for logical decoding. These GUCs live in postgresql.conf; wal_level requires a restart, the rest a reload.

-- Publisher prerequisites (postgresql.conf), then restart for wal_level:
--   wal_level = logical            -- log the relation + old-tuple metadata decoding needs
--   max_wal_senders = 10           -- one walsender per active stream + failover headroom
--   max_replication_slots = 10     -- one per slot, plus temporaries and failover slots
--   max_slot_wal_keep_size = 40GB  -- PG 13+: cap WAL a stalled slot can pin (0 = unbounded)

-- Confirm the running configuration before building anything on top of it.
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders','max_replication_slots','max_slot_wal_keep_size');

Three object classes must exist before a stream flows:

• Roles. The subscription connects as a role holding the REPLICATION attribute plus SELECT on every published table — never a superuser. Least-privilege access, TLS pinning, and pg_hba.conf scoping are detailed in security boundaries & permissions; the short version is a dedicated replicator role reachable only over hostssl from the consumer’s CIDR.
• Replica identity. Every table published for UPDATE/DELETE needs a replica identity so the old row can be reconstructed on the subscriber. A primary key satisfies this by default; a table with REPLICA IDENTITY NOTHING errors the moment an UPDATE is decoded. Validate this in pre-flight, not in production.
• The slot. A subscription auto-provisions its replication slot, or you pre-create one out of band — see initializing replication slots. The slot is the durable position marker that pins WAL until the consumer confirms receipt.

Pre-flight the replica-identity constraint across the tables you intend to publish:

-- Any row returned here will break UPDATE/DELETE decoding once published.
SELECT c.relname,
       CASE c.relreplident
         WHEN 'd' THEN 'default (PK)'
         WHEN 'n' THEN 'nothing'   -- UPDATE/DELETE will error at decode time
         WHEN 'f' THEN 'full'
         WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND c.relreplident = 'n';

Step-by-Step Implementation #

The two DDL statements below have asymmetric transactional behavior that trips up infrastructure-as-code (IaC) pipelines: CREATE PUBLICATION is fully transactional but has no IF NOT EXISTS, while CREATE SUBSCRIPTION (with create_slot = true) opens a remote connection and therefore cannot run inside a transaction block at all. Idempotency has to be enforced by the deployment tooling, not by the statements.

1. Define the publication with explicit column and operation filtering. Guard it with a catalog check inside a DO block so re-runs are safe across cluster failovers and IaC drift.

-- Idempotent publication: CREATE PUBLICATION has no IF NOT EXISTS,
-- so guard it with a pg_publication catalog check.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_publication WHERE pubname = 'analytics_cdc_pub') THEN
    CREATE PUBLICATION analytics_cdc_pub
    FOR TABLE
      public.orders (id, customer_id, total, status, updated_at),
      public.inventory (sku, warehouse_id, quantity, last_synced)
    WITH (publish = 'insert, update, delete', publish_via_partition_root = true);
  END IF;
END $$;

Column lists (public.orders (id, customer_id, ...), PG 15+) trim the payload to the columns a consumer actually reads. publish_via_partition_root = true maps changes on child partitions to the root’s schema, so a subscriber can track a single logical table while the publisher rotates partitions underneath. If a subset of rows is enough, add a WHERE row filter (PG 15+) — but remember that for UPDATE/DELETE the filter may reference only replica-identity columns, because those are the only old-row columns guaranteed to be in WAL.

2. Pre-create the slot when you need deterministic naming. Letting the subscription auto-create the slot is convenient but yields an auto-generated name that IaC cannot reconcile after a drift. Provisioning it explicitly (and idempotently) keeps slot-to-consumer mappings stable.

-- Deterministic, idempotent slot creation on the publisher.
SELECT pg_create_logical_replication_slot('analytics_cdc_slot', 'pgoutput')
WHERE NOT EXISTS (
  SELECT 1 FROM pg_replication_slots WHERE slot_name = 'analytics_cdc_slot'
);

3. Create the subscription bound to that slot. Because this statement cannot run in a transaction block, wrap the idempotency check in the deployment script rather than SQL. Set create_slot = false when the slot already exists, and copy_data = false to skip the initial snapshot when the target is already seeded.

-- Runs outside any transaction block. Deployment tooling must check
-- pg_subscription first to make this idempotent.
CREATE SUBSCRIPTION analytics_cdc_sub
CONNECTION 'host=pub-primary port=5432 dbname=source_db user=replicator sslmode=verify-full'
PUBLICATION analytics_cdc_pub
WITH (
  copy_data = false,          -- target already seeded; skip snapshot bottleneck
  create_slot = false,        -- slot pre-created in step 2
  slot_name = 'analytics_cdc_slot',
  streaming = on,             -- PG 14+: stream large in-progress txns, don't buffer to commit
  synchronous_commit = off    -- apply-worker durability; see parameter table
);

The copy_data decision is the single highest-impact choice in this sequence. With copy_data = true, the subscriber runs a synchronized initial snapshot per table before streaming — correct for a fresh target, but a serialization bottleneck on large tables. When the target is pre-loaded (restore, dump, or a physical base backup), copy_data = false resumes streaming from the slot’s position immediately. The snapshot-versus-catch-up mechanics and their distinct failure modes are covered in the subscription sync procedures.

4. Verify the stream is live. A healthy subscription shows an apply worker with an advancing latest_end_lsn within seconds.

SELECT s.subname, s.subenabled, st.pid, st.received_lsn, st.latest_end_lsn
FROM pg_subscription s
LEFT JOIN pg_stat_subscription st ON st.subid = s.oid
WHERE s.subname = 'analytics_cdc_sub';

Parameter Reference Table #

The WITH options on each object set the durability/throughput trade-off. Values below target PostgreSQL 14–17; version-gated behavior is called out inline.

Diagnostic Queries #

Every operational question about this topology reduces to a comparison of LSNs across pg_replication_slots, pg_stat_replication, and pg_stat_subscription. Keep these in a runbook.

Slot retention pressure (publisher side). The single most important query on any logical-replication primary — how much WAL each slot is pinning:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
       confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical'
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Threshold callouts: alert when retained exceeds ~20% of the WAL volume’s free space, when wal_status is extended or unreserved (the slot is approaching or past max_slot_wal_keep_size), or when active = false persists longer than a defined window (for example, 24 hours) — an inactive slot still pins WAL.

End-to-end lag (publisher’s view of each walsender). Separates the network hop from the apply cost:

SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn))  AS network_lag,
       pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS flush_lag,
       pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) AS apply_lag
FROM pg_stat_replication;

Sustained apply_lag growth with flat network_lag points at a slow or blocked apply worker on the subscriber, not a saturated link. Exporting these as continuous metrics is the job of the asynchronous monitoring integration.

Published surface (verify what is actually in the stream). Confirms the publication’s effective table-and-column set — the source of “why isn’t my column replicating” incidents:

-- pg_publication_tables expands FOR TABLE, FOR ALL TABLES, and column lists.
SELECT pubname, schemaname, tablename, attnames, rowfilter
FROM pg_publication_tables
WHERE pubname = 'analytics_cdc_pub';

Subscriber apply status. Runtime LSNs live on pg_stat_subscription; the definition on pg_subscription:

SELECT s.subname, s.subenabled, st.pid, st.received_lsn,
       st.latest_end_lsn, st.last_msg_receipt_time
FROM pg_subscription s
LEFT JOIN pg_stat_subscription st ON st.subid = s.oid;

A NULL pid on an enabled subscription means the apply worker has crashed or is in a restart loop — cross-check the server log for the failing transaction’s LSN.

Failure Modes & Gotchas #

1. Slot exhaustion / disk pressure. A disconnected or stalled consumer stops advancing confirmed_flush_lsn; restart_lsn freezes and WAL accumulates until the primary hits disk full. Root cause: an unacknowledged slot pins every segment newer than restart_lsn. Remediation: set max_slot_wal_keep_size (PG 13+) so a runaway slot is invalidated instead of taking the primary offline; alert on the retention query above; and drop genuinely orphaned slots only after confirming the consumer is permanently decommissioned.

2. Apply-worker crash on constraint or missing column. An UPDATE arrives for a row that violates a subscriber constraint, or references a column the subscriber’s schema lacks; the apply worker errors, exits, and PostgreSQL restarts it into the same failing transaction — a crash loop that freezes latest_end_lsn. The server log names the exact failing transaction ID and LSN. Remediation: patch the subscriber schema, or as a last resort skip the transaction with pg_replication_origin_advance() (or ALTER SUBSCRIPTION … SKIP on PG 15+). Set disable_on_error = true (PG 15+) so the subscription stops cleanly instead of looping.

3. Replica-identity mismatch. UPDATE/DELETE on a table with REPLICA IDENTITY NOTHING, or default identity with no primary key, errors at decode time on the publisher and stalls the slot for all subscribers. Remediation: run the pre-flight identity query before publishing; set REPLICA IDENTITY FULL on keyless tables at the cost of larger WAL.

4. Silent operation drop. A publication created WITH (publish = 'insert, update') never streams deletes — no error, just missing rows on the target that diverge slowly. Remediation: treat the publish set as a contract, assert it in the published-surface query, and default to all four operations unless a target explicitly must not see deletes.

5. Large-transaction head-of-line blocking. Logical decoding is transactional and in-commit-order: a change is not streamed until its COMMIT is decoded, so a multi-hour bulk load on the publisher pins restart_lsn and inflates apply lag for every consumer of that slot. Remediation: enable streaming = on (PG 14+) or parallel (PG 16+) to stream in-progress transactions; keep publisher transactions short; monitor pg_stat_activity for old xact_start.

Integration Touchpoints #

The publication is where topology is decided, but its behavior is defined by objects on either side of it. The slot it binds is provisioned and recovered through initializing replication slots, and the byte-level batching, reorder-buffer, and spill behavior that governs how changes reach the slot is WAL stream mechanics. The durability the publisher chooses here sets the data-loss window that tuning synchronous_commit for logical replication trades against throughput.

When the consumer is a native subscriber, the initial baseline is driven by the subscription sync procedures. When the consumer is an event-streaming pipeline instead, the same publication and slot are reused by a Debezium connector, whose changes fan out through Kafka event routing — at which point idempotency and offset management move from the SQL apply worker into the streaming layer. The decision of whether logical or physical replication is the right substrate for a given target is worked through in logical vs physical replication differences.

Frequently Asked Questions #

Related guides #

• Replication slot types — the durable state anchors every subscription binds to.
• WAL stream mechanics — how decoding batches, buffers, and spills the changes a publication emits.
• Security boundaries & permissions — least-privilege roles, TLS, and pg_hba.conf for replication users.
• Logical vs physical replication differences — choosing the right substrate for each target.
• Subscription sync procedures — driving and recovering the initial snapshot a subscription consumes.
• PostgreSQL Logical Replication Architecture & Fundamentals — the architecture this model sits within.