Viewpg_stat_subscriptionIntroducedPostgres 10RiskPublisher WAL retention grows until subscriber catches up or replication breaks
Logical replication has a failure mode that physical replication lacks: a subscriber that falls behind doesn't immediately break anything visible. Queries on the publisher keep working. The replica keeps accepting connections. The only signal is that replication lag grows silently until the publisher's WAL retention becomes a disk crisis.
pg_stat_subscription is where you watch for this. One row per logical replication worker. The columns you need:
subid / subnameThe subscription OID and name. If you have multiple subscriptions on a single subscriber, you'll see multiple rows here.pidThe PID of the logical replication apply worker. NULL when the worker isn't running — which is itself an alert condition.received_lsnThe last LSN received from the publisher. This is what the subscriber has acknowledged receiving.last_msg_send_time / last_msg_receipt_timeTimestamps of the last heartbeat exchange. The age of last_msg_receipt_time tells you whether the network connection is alive, independent of whether there's anything to replicate.latest_end_lsn / latest_end_timeThe LSN and timestamp of the last message the subscriber sent back to the publisher confirming it has applied changes up to that point.
There are three lag dimensions, and they measure different things.
Receive lag: pg_current_wal_lsn() - received_lsn on the publisher side. How far behind the subscriber is in receiving WAL from the publisher. A large receive lag means the publisher is accumulating WAL it can't release.
Apply lag: received_lsn - latest_end_lsn. The gap between what the subscriber has received and what it has confirmed applying. This grows when the subscriber is receiving data faster than it can apply it — common during bulk inserts or when the apply worker is blocked.
Network / availability lag: now() - last_msg_receipt_time. How long since the subscriber last heard from the publisher. If this is more than a few heartbeat intervals (default: 10 seconds), the connection is gone.
The publisher-side risk is slot retention. Each logical replication slot holds WAL on the publisher until the subscriber confirms it has applied up to that LSN. If a subscriber falls far behind — or stops entirely — the publisher cannot release WAL, and disk fills. Check pg_replication_slots on the publisher: if confirmed_flush_lsn is far behind pg_current_wal_lsn() and the corresponding active column is false, the slot is accumulating.
Common failure modes that pg_stat_subscription will surface:
- Schema mismatch on ALTER TABLE: You altered a table on the publisher without running the same DDL on the subscriber first. The apply worker exits with an error. The
pidcolumn goes NULL and stays NULL. The subscription will not self-heal until you fix the schema divergence manually. - Privilege revocation: The replication user lost SELECT on a table being replicated. Same symptom: apply worker exits, pid goes NULL.
- Table added without ALTER SUBSCRIPTION REFRESH: You added a new table to the publication but didn't run
ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION. The new table simply isn't replicated, silently. pg_stat_subscription won't tell you about missing tables — only about workers that exist.
What the view does not show: per-table apply throughput, conflict details before Postgres 15, bandwidth consumption. For conflict details in Postgres 15+, check pg_stat_subscription_stats which tracks apply errors per subscription. For per-table progress, you'd need application-level instrumentation or a logical replication plugin that exposes more detail.
Operational guidance: compare received_lsn vs latest_end_lsn for apply lag. Monitor last_msg_receipt_time age for network and availability issues — alert if it's older than 2 heartbeat intervals. Alert on NULL pid if the subscription should be running. Alert on publisher pg_replication_slots where active = false and wal_status = 'extended'. The receiver falling behind silently is the pathology; the view is the instrument you use to catch it early.
Building something? Document your progress at builds.anethoth.com — proof that a product is really being built.