PostgreSQL 17
pg_stat_subscription gained a worker type
For anyone running logical replication who has ever asked why the subscription has three processes.
Reference page, revised in place. Last updated .
Three jobs, one row shape, no label
A subscription is not a process. It is a small, changing population of them, and they do unrelated work. One of them reads the stream from the publisher and applies it in order. Some of them exist only for as long as it takes to copy a table’s existing contents before that table joins the stream. Others exist only for as long as one very large transaction is being applied in pieces. They appear and vanish on their own schedule, and before PostgreSQL 17 the view that lists them gave every one of them the same shape and left the reader to work out which was which.
The inference was not hard, but it was inference. A row carrying a relation was copying that relation. A row carrying another process’s id as its leader was helping that process with one transaction. A row carrying neither was the one steady worker. Written down like that it sounds obvious; written into a monitoring query three years ago by somebody who has since left, it reads as two columns tested for null for no stated reason, and it is precisely the kind of expression that gets simplified by a well-meaning reviewer into something wrong.
PostgreSQL 17 replaced it with a label. The Logical Replication section of the PostgreSQL 17 release notes records it in one line, and the whole change is one text column that says what the process is for. What makes it worth a page is where the column was put.
The column landed in the middle
New columns are usually appended, and consumers that read by position survive. This one is not appended.
SELECT attnum AS position, attname AS column_name
FROM pg_attribute
WHERE attrelid = 'pg_stat_subscription'::regclass AND attnum BETWEEN 1 AND 5
ORDER BY attnum;On 16.15:
position | column_name
----------+-------------
1 | subid
2 | subname
3 | pid
4 | leader_pid
5 | relid
(5 rows)On 17.11:
position | column_name
----------+-------------
1 | subid
2 | subname
3 | worker_type
4 | pid
5 | leader_pid
(5 rows)Everything from the third position onward shifted right by one. A consumer naming its columns does not care. A consumer that does not name them cares a great deal, and there are more of those than people expect, because the laziest way to snapshot a statistics view into a history table is to select everything and insert it.
The position also decides what a person sees. Anyone who reads this view by typing a star into a terminal now finds the label immediately after the subscription name, which is where it belongs, and reads the process ids one column to the right of where their fingers expect them. That is a small kindness for interactive use and a real hazard for anything that learned the shape by example rather than by reading the documentation.
A publisher, a subscriber and a table worth stalling
The fixture runs both ends in one cluster, which is the cheapest way to see three worker types without three machines. Two details make that work. The subscription is told not to create its own replication slot, because a slot created from inside the same cluster waits for the very transaction that is creating it and never finishes. And the lock that stalls the copy is left behind by a prepared transaction, because a lock held by an ordinary session disappears when that session ends, and each block here is its own session.
CREATE EXTENSION dblink;
CREATE DATABASE ledger_source;CREATE EXTENSION
CREATE DATABASESELECT dblink_exec('dbname=ledger_source', $fixture$
CREATE TABLE ledger_entry (entry_id bigserial PRIMARY KEY, posted_on date, amount_minor bigint);
INSERT INTO ledger_entry (posted_on, amount_minor)
SELECT date '2026-01-01' + (g % 240), g * 7 FROM generate_series(1, 250000) AS g;
CREATE PUBLICATION ledger_pub FOR TABLE ledger_entry;
$fixture$) AS publisher_ready;
CREATE TABLE ledger_entry (entry_id bigint PRIMARY KEY, posted_on date, amount_minor bigint); publisher_ready
--------------------
CREATE PUBLICATION
(1 row)
CREATE TABLENow the subscription is created disabled, a prepared transaction takes an exclusive lock on the destination table, and the subscription is switched on. The copy starts, cannot take its lock, and stays where it is for as long as the prepared transaction is left uncommitted.
SELECT slot_name FROM dblink('dbname=ledger_source',
$slot$SELECT slot_name FROM pg_create_logical_replication_slot('ledger_sub', 'pgoutput')$slot$)
AS t(slot_name text);
CREATE SUBSCRIPTION ledger_sub
CONNECTION 'dbname=ledger_source' PUBLICATION ledger_pub
WITH (create_slot = false, slot_name = 'ledger_sub', enabled = false, streaming = parallel);
BEGIN;
LOCK TABLE ledger_entry IN ACCESS EXCLUSIVE MODE;
PREPARE TRANSACTION 'stall_ledger_entry';
ALTER SUBSCRIPTION ledger_sub ENABLE;
SELECT pg_sleep(5); slot_name
------------
ledger_sub
(1 row)
CREATE SUBSCRIPTION
BEGIN
LOCK TABLE
PREPARE TRANSACTION
ALTER SUBSCRIPTION
pg_sleep
----------
(1 row)On 16.15 the question “what are these two processes” has to be answered from the two columns that happen to be populated.
SELECT subname,
leader_pid IS NOT NULL AS has_a_leader,
relid::regclass AS copying,
CASE WHEN relid IS NOT NULL THEN 'table synchronization'
WHEN leader_pid IS NOT NULL THEN 'parallel apply'
ELSE 'apply' END AS inferred_kind
FROM pg_stat_subscription ORDER BY inferred_kind; subname | has_a_leader | copying | inferred_kind
------------+--------------+--------------+-----------------------
ledger_sub | f | | apply
ledger_sub | f | ledger_entry | table synchronization
(2 rows)On 17.11 the server answers it directly, and the inference above becomes a column you can group by.
SELECT subname, worker_type, relid::regclass AS copying
FROM pg_stat_subscription ORDER BY worker_type; subname | worker_type | copying
------------+-----------------------+--------------
ledger_sub | apply |
ledger_sub | table synchronization | ledger_entry
(2 rows)Releasing the prepared transaction lets the copy finish. The loop waits for every relation in the subscription to reach its final state rather than sleeping for a guessed number of seconds, which is the difference between a fixture and a coin toss.
COMMIT PREPARED 'stall_ledger_entry';
DO $wait$ BEGIN
FOR i IN 1..120 LOOP
EXIT WHEN NOT EXISTS (SELECT 1 FROM pg_subscription_rel WHERE srsubstate <> 'r');
PERFORM pg_sleep(1);
END LOOP;
END $wait$;
SELECT count(*) AS rows_copied FROM ledger_entry;COMMIT PREPARED
DO
rows_copied
-------------
250000
(1 row)The third kind, which only exists under load
The copy workers are gone now and a steady subscription shows one process. The third kind appears only when the publisher sends a transaction too large to buffer, which the subscription was created to accept in parallel. The fixture forces it by lowering the decoding memory budget to something tiny and opening a transaction on the publisher that exceeds it, and stalls it the same way as before so it can be looked at.
BEGIN;
LOCK TABLE ledger_entry IN ACCESS EXCLUSIVE MODE;
PREPARE TRANSACTION 'stall_apply';
SELECT dblink_connect('source', 'dbname=ledger_source') AS connected;
SELECT dblink_exec('source', 'BEGIN') AS opened;
SELECT dblink_exec('source', $batch$INSERT INTO ledger_entry (posted_on, amount_minor)
SELECT date '2026-06-01', g FROM generate_series(1, 50000) AS g$batch$) AS inserted;
SELECT dblink_exec('source', $prep$PREPARE TRANSACTION 'big_batch'$prep$) AS held;
SELECT pg_sleep(6);BEGIN
LOCK TABLE
PREPARE TRANSACTION
connected
-----------
OK
(1 row)
opened
--------
BEGIN
(1 row)
inserted
----------------
INSERT 0 50000
(1 row)
held
---------------------
PREPARE TRANSACTION
(1 row)
pg_sleep
----------
(1 row)SELECT subname, pid IS NOT NULL AS running, leader_pid IS NOT NULL AS has_a_leader
FROM pg_stat_subscription ORDER BY has_a_leader; subname | running | has_a_leader
------------+---------+--------------
ledger_sub | t | f
ledger_sub | t | t
(2 rows)SELECT subname, worker_type, leader_pid IS NOT NULL AS has_a_leader
FROM pg_stat_subscription ORDER BY worker_type; subname | worker_type | has_a_leader
------------+----------------+--------------
ledger_sub | apply | f
ledger_sub | parallel apply | t
(2 rows)Two processes for one subscription, and on 17 no arithmetic is needed to say what the second one is. The operational value is not the label on a screen; it is that a count of subscription workers can now be grouped. “Three workers” was never an alarming number or a reassuring one. “One applying and two copying” is a subscription still catching up after a table was added. “One applying and two helping with one transaction” is a subscription meeting a bulk load on the publisher. Those two situations want different responses and produced the same number before 17.
There is a ceiling underneath all of this, and the arithmetic is easier to get wrong than it looks. The cluster has one pool of logical replication workers shared by every subscription in every database. Out of that pool each subscription takes one worker to apply with, as many as its own limit allows for copying tables, and as many again as its own limit allows for helping with large transactions. A cluster with a handful of subscriptions can therefore want considerably more workers than the number somebody set once while reading a tuning guide, and the way it tells you is not an error. It is a table that never starts copying. Grouping the view by worker type is what turns that into a number you can compare against the ceiling, which was awkward to compute before the column existed.
What a snapshot job does on the morning after
The history table is the pattern that breaks, and it breaks because of where the column landed rather than because of the column itself. Here is one shaped the way a 16 cluster would have shaped it, with a column per column of the view.
CREATE TABLE subscription_worker_history (
captured_at timestamptz DEFAULT now(),
subid oid, subname name, pid integer, leader_pid integer, relid oid,
received_lsn pg_lsn, last_msg_send_time timestamptz, last_msg_receipt_time timestamptz,
latest_end_lsn pg_lsn, latest_end_time timestamptz
);CREATE TABLEOn 16.15 the job that fills it works, as it has since the day somebody wrote it.
INSERT INTO subscription_worker_history
(subid, subname, pid, leader_pid, relid, received_lsn,
last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time)
SELECT * FROM pg_stat_subscription;
SELECT count(*) AS rows_captured FROM subscription_worker_history;INSERT 0 2
rows_captured
---------------
2
(1 row)On 17.11 the same statement, against the same table, stops:
INSERT INTO subscription_worker_history
(subid, subname, pid, leader_pid, relid, received_lsn,
last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time)
SELECT * FROM pg_stat_subscription;ERROR: INSERT has more expressions than target columns
LINE 4: SELECT * FROM pg_stat_subscription;
^This is the good outcome and it is worth saying so plainly. The error names the statement, fires on the first run after the upgrade, and cannot be mistaken for anything else. The version that should worry you is the one where the column count happens to line up: a consumer reading fields by position from a text or CSV dump of the view now finds a worker type where it expected a process id, and a process id where it expected a leader, and nothing anywhere raises an error. The defence against both is the same and it is dull: name your columns.
What to watch rather than alert on
- Copy workers that are still present long after a table was added to the publication. A copy that cannot finish usually cannot take a lock, and the lock is the thing to look at, not the worker.
- Apply workers that are absent while the subscription is enabled. An empty view for an enabled subscription means the worker is restarting in a loop, and the reason is in the log rather than in this view.
- The count of helper workers against the ceiling the subscription was given. Reaching the ceiling is not a fault; sitting at it during every bulk load means the publisher’s transactions are larger than the subscriber was configured to absorb.
Nothing here belongs in a threshold alert on its own. Lag belongs in the alert, and this view explains the lag once the alert has fired.
The reason to be firm about that is that worker counts are naturally spiky. A deployment that adds four tables to a publication produces a burst of copy workers and then does not produce them again; a nightly batch on the publisher produces helpers for an hour. Both look identical to a threshold on a raw count and neither is a fault. What is a fault is a population that does not change back, and detecting that needs a comparison over time rather than a line drawn across a graph.
What it costs to collect
Nothing gates the view and nothing needs restarting to read it. The costs are in the workers rather than in the reporting: every one of them is a process drawn from the same pool as the other background workers, and the pool has a ceiling that is shared across every subscription on the cluster. A cluster with several subscriptions each copying several tables can exhaust that ceiling, and when it does, the symptom is a copy that never starts rather than an error anybody sees.
The one genuine trap is reading the view for a subscription that is not in the database you are connected to. Subscription rows belong to the database that owns the subscription, so a cluster-wide answer means visiting each database in turn, and a monitoring job connected only to the maintenance database reports a healthy zero for a subscription that is on fire two databases over.