Why PostgreSQL logical replication can still be slow even with workers available

The parameter's name suggests generalized parallelism in logical replication, and it's easy to assume that raising it would speed up a lagging subscriber. A recent article in the "All Your GUCs in a Row" series, published on Planet PostgreSQL, dismantles that expectation with practical tests on 18.6 and 19 beta 3. What max_parallel_apply_workers_per_subscription actually controls is much narrower: how many large, still-open transactions a subscription can apply simultaneously. Every transaction beyond that limit is written to a file and applied later, serially, by the same process that's already queuing up the rest.

For anyone administering environments with multiple subscribers in production, this distinction is what separates effective tuning from an adjustment that changes nothing on the lag graph.

What the parameter actually does

The GUC arrived in PostgreSQL 16 alongside the streaming = parallel option, default value 2, sighup context (accepts reload, no restart required), range 0 to 1024. In versions 16 and 17 you had to explicitly request streaming = parallel on the subscription; in PostgreSQL 18 this mode became the default for CREATE SUBSCRIPTION. In practice, this means any subscription created on an 18 instance without explicit configuration is already subject to this limit, without the DBA having asked for it. Subscriptions that arrive via pg_upgrade keep the old behavior, and pg_dump on 18 explicitly writes streaming = off to guarantee this on restore.

The mechanism underneath: when the publisher's reorder buffer exceeds logical_decoding_work_mem, it starts sending the subscriber the largest open transaction even before it commits. On the subscriber, the leader apply worker hands each streamed transaction to a parallel apply worker, which applies the changes as they arrive. One transaction, one worker. Raising the parameter doesn't make a single transaction apply faster; and everything that isn't streamed (with the default 64MB threshold, practically all the traffic of a typical OLTP system) keeps being applied by the leader, alone, in commit order. When a streamed transaction finally commits, the leader waits for the corresponding worker to finish before moving on, so commit order is preserved even here. The real gain is elsewhere: by the time the commit arrives, most of the heavy lifting has already been done.

The number behind the argument

The article's author measured this directly: on 18.6, a 400,000-row insert on the publisher followed by a one-row transaction, committed right after. With a parallel apply worker available, the one-row transaction became visible on the subscriber about 0.2 seconds after the large transaction's commit. With the parameter set to 0, the leader wrote 267MB to base/pgsql_tmp and only started applying at commit time; the one-row transaction waited between 3.2 and 4.0 seconds. The test machine was small and so was the transaction, but the ratio between the two scenarios (0.2s versus ~3.5s) is the concrete data point that justifies looking at this GUC when the publisher runs overlapping batch loads.

Exhausting the worker pool is silent

The documentation says changes go to a parallel worker "if available," and this parameter decides much of that availability. With the default of 2, the author kept three large transactions open at the same time on the publisher. Querying pg_stat_subscription showed two active parallel apply workers, and the third transaction went to a 133MB temporary file, with no warning at the default log level. Only with log_temp_files = 0 does a trace appear, logged when the file is removed, with STREAM COMMIT in the context. That's the signal to look for: a temporary file from a logical replication apply worker with STREAM COMMIT in the context indicates a streamed transaction that couldn't get a parallel worker.

Two counters help monitor this in production: pg_stat_database.temp_bytes on the subscriber (which sums up these same files) and stream_txns, in pg_stat_replication_slots on the publisher, which reports how many transactions were candidates for streaming. The cost of overflow is bigger than just the transaction that overflowed: in the same test on 19 beta 3, the two parallel workers sat idle for eight seconds waiting for the leader to finish reapplying the third transaction from the file.

There's a difference between hitting this parameter's limit and hitting the overall pool's limit. When the exhaustion comes from the pool configured by max_logical_replication_workers, the log is explicit: with this GUC at 4, the pool also at 4 (default) and four large transactions open, the fourth went to file and the leader logged "out of logical replication worker slots" with STREAM START in the context. The practical ordering lesson: this parameter accepts reload; the pool it draws workers from requires a restart. Raise the pool first, the GUC second.

Three conditions that void parallelism without warning

There are three situations in which every streamed transaction goes to file, regardless of the configured value, and none of them are announced in the log:

• Publisher on a version older than 16. The author pointed a default 18.6 subscription at a 15.19 publisher: pg_subscription.substream stayed at p, no parallel worker showed up, and the leader applied everything alone. This is exactly the scenario of migrating a 14 or 15 primary via logical replication, so the new server's default behavior won't show up during that specific migration.
• Any subscription table outside the r state. While a table is still in initial sync, or after an ALTER SUBSCRIPTION ... REFRESH PUBLICATION that added a new table, the entire subscription falls back to serial application, even with idle workers sitting in the pool.
• A pending SKIP on the subscription. An ALTER SUBSCRIPTION ... SKIP turns off parallel apply until the skip is consumed, because the leader needs the whole transaction in hand to decide whether it's the one to be skipped. The author cites this point from the documentation, without having tested it directly.

Zero as a valid value, and the math of retained workers

Workers that finish are reused, and each retained one occupies a slot in the pool while the subscription runs. The number retained is half the configured value, rounded down: 2 keeps one, 3 keeps one, 4 keeps two, and 1 keeps none. At 1, every large transaction pays the cost of starting a new process and a 16MB shared memory queue; in the author's own timing test, the run that had to start a worker took 0.9 seconds instead of 0.2. That's a reasonable trade-off on a subscriber with dozens of subscriptions competing for a tight pool, and a terrible idea in any other scenario.

Zero is a real configuration, not an accidental shutdown: it turns streaming = parallel into streaming = on for every subscription on the server, via reload, without touching the catalog. The right moment to use it is during a replication conflict. When a change fails inside a parallel apply worker, the log carries the remote transaction's ID, but no LSN, which prevents using ALTER SUBSCRIPTION ... SKIP directly. Zeroing the parameter globally forces the retry to run on the leader from the spool file, and then the context carries the LSN needed for the SKIP to work. The documented, more surgical route is to change the streaming option of the specific affected subscription, fixing one without impacting the others.

What's left out

If the problem is lag caused by a high volume of ordinary transactions, not by concurrent large transactions, this parameter fixes nothing: through version 19, nothing in PostgreSQL core parallelizes that path, and a single process keeps applying those transactions in order. The author's recommendation, one that holds up well in any production environment with multiple subscriptions, is to leave the value at 2 and only raise it when temporary apply worker files show up with STREAM COMMIT in the context, a sign that the publisher is running more than two batch transactions simultaneously. And, when raising it, first increase max_logical_replication_workers by the same proportion for each affected subscription, because the pool doesn't grow on its own.

Source 1: Planet PostgreSQL (https://postgr.es/p/9vf)