The name promises parallel apply, and anyone watching a subscriber fall behind will find it and hope. It delivers something narrower. max_parallel_apply_workers_per_subscription is the number of large, still-open transactions that one subscription can apply at the same time. The next one past that number is written to a file and applied later, by the one process everything else is queued behind.
Default 2, context sighup, range 0 to 1024. It arrived in PostgreSQL 16 along with streaming = parallel, and nothing about it changes through 19 beta 3. In 16 and 17 you had to ask for streaming = parallel. PostgreSQL 18 made it the default for CREATE SUBSCRIPTION, so on 18 this parameter applies to every subscription you create without thinking about it. Subscriptions that come through pg_upgrade keep what they had; pg_dump in 18 writes streaming = off explicitly to make sure of it.
The tour of the subscriber’s worker pool is in max_logical_replication_workers, so here is only the part this parameter touches. When the publisher’s reorder buffer goes over logical_decoding_work_mem, it starts sending its largest transaction downstream before that transaction has committed. On the subscriber, the leader apply worker hands each such transaction to one parallel apply worker, which applies the changes as they arrive. One transaction, one worker. A larger setting does not make any single transaction apply faster, and everything that isn’t streamed (with the default 64MB threshold, nearly everything on an OLTP system) is still applied by the leader, alone, in commit order. When a streamed transaction does commit, the leader waits for its worker to finish before going on, so commit order holds there too. What you gain is that most of the big transaction has already been applied by the time its commit arrives.
That is measurable. On 18.6 I ran a 400,000-row insert on the publisher and committed a one-row transaction right behind it. With a pooled parallel apply worker, the one-row transaction was visible on the subscriber about 0.2 seconds after the big commit. With this parameter at 0, the leader spooled 267MB to base/pgsql_tmp, began applying it only at commit, and the one-row transaction waited between 3.2 and 4.0 seconds. That was a small test machine and a small transaction. Scale it to your own batch jobs.
Running out is silent
The documentation for streaming = parallel says changes go to a parallel apply worker “if available,” and this parameter is most of what decides that. With the default of 2, I held three large transactions open on the publisher at once:
1 postgres=# SELECT subname, worker_type, pid, leader_pid FROM pg_stat_subscription; 2 subname | worker_type | pid | leader_pid 3 ---------+----------------+-------+------------ 4 sub1 | apply | 12066 | 5 sub1 | parallel apply | 12088 | 12066 6 sub1 | parallel apply | 12097 | 12066
Two got workers. The third went to pgsql_tmp12066.0.fileset/16416-759.changes.0, all 133MB of it, and at the default log level the subscriber said nothing. With log_temp_files set to 0 there is one trace, written when the file is removed:
1 [12066] logical replication apply worker LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp12066.0.fileset/16416-759.changes.0", size 133488942 2 [12066] logical replication apply worker CONTEXT: processing remote data for replication origin "pg_16416" during message type "STREAM COMMIT" in transaction 759, finished at 0/1D1C1B00
A temporary file from a logical replication apply worker with STREAM COMMIT in its context line is a streamed transaction that did not get a parallel worker. pg_stat_database.temp_bytes on the subscriber counts the same files, and stream_txns in the publisher’s pg_stat_replication_slots tells you how many transactions were candidates in the first place. The spill costs more than the transaction that spilled: in the same test on 19 beta 3, both parallel workers sat for eight seconds waiting on the leader while it replayed the third transaction from its file.
The refusal is only noisy when it comes from the outer pool. I raised this parameter to 4, left max_logical_replication_workers at its default of 4, and opened four large transactions. The leader plus three workers filled the pool, the fourth transaction went to a file, and this time the leader logged out of logical replication worker slots with STREAM START in the context. This parameter takes a reload. The pool it draws from takes a restart. Raise the pool first.
Workers that never start
Three conditions send every streamed transaction to a file no matter what this is set to, and none of them is announced.
The publisher has to be 16 or later. I pointed a default 18.6 subscription at a 15.19 publisher: pg_subscription.substream said p, no parallel worker ever appeared, and the leader spooled everything. This is exactly the arrangement you build to upgrade a 14 or 15 primary by logical replication, so the new server’s default will not do for that migration what it does afterwards.
Every table in the subscription has to be in state r. While one is still copying, whether during the initial sync or after ALTER SUBSCRIPTION ... REFRESH PUBLICATION adds a table, the whole subscription falls back. I watched a leader spool 133MB with an idle parallel worker sitting in its pool, because one newly added table was still at d.
And a pending ALTER SUBSCRIPTION ... SKIP turns parallel apply off until the skip is consumed, because the leader needs the whole transaction in hand to know whether it is the one to skip. (That one is from the source. I did not test it.)
One and zero
As the tour post covers, finished workers are kept for reuse, and each one kept holds a pool slot for as long as the subscription runs. The number kept is half of this setting, rounded down: 2 keeps one, 3 keeps one, 4 keeps two, and 1 keeps none. At 1, every large transaction pays for a new process and a new 16MB shared memory queue; the one run in my timing test that had to start its worker took 0.9 seconds instead of 0.2. That is a fair trade on a subscriber with dozens of subscriptions and a tight pool, and a poor one anywhere else. (Lowering the setting does not evict a worker already in the pool. It takes one more transaction and then exits.)
Zero is a real setting. It turns streaming = parallel into streaming = on for every subscription on the server, with a reload, without touching a catalog, and the time you want that is a conflict. When a change fails inside a parallel apply worker, the log gives you the remote transaction ID and no LSN:
1 CONTEXT: processing remote data for replication origin "pg_16416" during message type "INSERT" for replication target relation "public.t2" in transaction 827
ALTER SUBSCRIPTION ... SKIP wants an LSN. I set this to 0 and reloaded; the next retry ran in the leader from a spool file, and its context line ended in transaction 827, finished at 1/81661148. The documented route is to change the subscription’s own streaming option, which does the same thing to one subscription instead of all of them. Use that when only one is broken. Either way, put it back.
One piece of log noise to recognize. Between chunks of a streamed transaction, a parallel apply worker waits on a heavyweight lock held by the leader; that is deliberate, so the deadlock detector can see a cycle that runs through both of them. With log_lock_waits on, which I recommended and which 19 does by default, any large transaction that goes quiet on the publisher for longer than deadlock_timeout produces this on the subscriber:
1 LOG: process 14458 still waiting for AccessShareLock on remote transaction 836 of subscription 16416 of database 5 after 1000.130 ms 2 DETAIL: Process holding the lock: 12926. Wait queue: 14458.
Nothing is contended. The worker is waiting for the publisher’s transaction to do something.
Leave it at 2. The reason to raise it is apply-worker temporary files at STREAM COMMIT on a subscriber whose publisher runs more than two bulk transactions at once (overlapping batch jobs, a parallel loader). Set it to that number, and first give max_logical_replication_workers that many more slots for each subscription it applies to. If what you are chasing is lag from a high rate of ordinary transactions, this is not your parameter, and through 19 nothing in core is. One process applies those.