Demystifying PostgreSQL’s Parallel Apply Workers: Performance, Pitfalls, and Configuration Best Practices

Database replication is the bedrock of modern high-availability architecture. As enterprises scale and data volume explodes, the ability to replicate data swiftly from a primary publisher to a downstream subscriber becomes a critical path for system performance.

For years, PostgreSQL database administrators have relied on logical replication to route data changes efficiently. However, large, monolithic transactions—such as mass data loads, massive batch updates, or heavy ETL pipelines—historically created bottlenecks on the subscriber side. Because a single logical replication apply worker processed transactions sequentially in strict commit order, a massive transaction could stall downstream queues and trigger replication lag.

The introduction of parallel streaming capabilities in PostgreSQL 16 sought to solve this challenge. By allowing large transactions to stream to the subscriber before they officially commit on the publisher, PostgreSQL can apply changes concurrently via dedicated worker processes. At the heart of this mechanism lies a critical configuration parameter: max_parallel_apply_workers_per_subscription.

While designed to accelerate large transaction throughput, this parameter introduces subtle complexities, silent failures, and strict prerequisites that can catch even experienced database administrators off guard. Analyzing its behavior across versions—including PostgreSQL 16, 17, 18, and the upcoming PostgreSQL 19 release—reveals how to optimize parallel apply settings safely in production environments.

Main Facts: Decoding max_parallel_apply_workers_per_subscription

To understand how PostgreSQL handles parallel logical replication, one must examine the scope and boundaries of max_parallel_apply_workers_per_subscription.

Despite its name suggesting broad parallel execution capabilities across an entire cluster, the parameter governs a much narrower scope. It defines the maximum number of large, still-open transactions that one single subscription can apply concurrently.

• Default Value: 2
• Configuration Context: SIGHUP (requires a reload rather than a full database restart)
• Valid Range: 0 to 1024

When a publisher’s reorder buffer exceeds the memory threshold defined by logical_decoding_work_mem, it begins streaming large transactions downstream before they are fully committed. On the subscriber, the leader apply worker intercepts these streams and assigns each large transaction to a parallel apply worker.

Crucially, this is a one transaction, one worker mapping. Raising this parameter does not accelerate an individual, massive transaction. Instead, it allows multiple large transactions to be processed in parallel. Ordinary, non-streamed transactions—which account for the vast majority of traffic on high-volume OLTP systems operating under the default 64MB threshold—are still handled exclusively by the leader worker in strict commit order.

When a streamed transaction finally commits on the publisher, the subscriber’s leader worker pauses to wait for the parallel worker to finish processing. This preserves overall consistency and commit order while ensuring that the bulk of the heavy lifting has already been completed in the background.

Chronology: The Evolution of Parallel Replication in PostgreSQL

The journey of parallel logical replication reflects PostgreSQL’s iterative approach to performance engineering:

• PostgreSQL 16: Introduced the parallel apply feature alongside the streaming = parallel option. However, administrators had to explicitly opt-in by configuring subscriptions with streaming = parallel.
• PostgreSQL 17: Maintained the opt-in requirement, allowing early adopters to test parallel streaming stability in production environments.
• PostgreSQL 18: Marked a major milestone by making streaming = parallel the default behavior for all new CREATE SUBSCRIPTION statements. Consequently, max_parallel_apply_workers_per_subscription began applying globally to every newly created subscription without requiring manual intervention. To prevent unexpected behavior during migrations, subscriptions upgraded via pg_upgrade preserved their existing configurations, while pg_dump in version 18 explicitly writes streaming = off to maintain backward compatibility.
• PostgreSQL 19 (Beta 3): Continues to build upon the PostgreSQL 18 framework with no structural changes to the underlying parameter logic, though edge-case performance characteristics (such as spooling recovery times) remain a point of discussion among core contributors.

Supporting Data: Benchmarking Performance and Resource Spilling

The performance impact of configuring parallel apply workers is easily measurable, particularly when systems encounter high-concurrency batch processing.

Performance Benchmarks

In benchmark tests running on PostgreSQL 18.6, executing a 400,000-row insert on the publisher followed immediately by a small, one-row transaction revealed stark contrasts in subscriber latency:

1. With Active Parallel Workers: The subsequent one-row transaction became visible on the subscriber approximately 0.2 seconds after the large transaction committed. The bulk of the 400,000 rows had already been processed in the background.
2. With Parallel Workers Disabled (max_parallel_apply_workers_per_subscription = 0): The leader process was forced to spool 267MB of data to temporary storage (base/pgsql_tmp), beginning application only after the commit signal was received. Consequently, the small one-row transaction stalled, waiting between 3.2 and 4.0 seconds to resolve.

The Problem of Silent Spilling

The official documentation notes that changes are routed to a parallel apply worker "if available." When demand exceeds the configured worker limit, excess transactions are quietly written to temporary files on disk.

For instance, with the default limit of 2, opening three large transactions simultaneously results in the following state visible in pg_stat_subscription:

 subname |  worker_type   |  pid  | leader_pid
---------+----------------+-------+------------
 sub1    | apply          | 12066 |
 sub1    | parallel apply | 12088 |      12066
 sub1    | parallel apply | 12097 |      12066

While two transactions secure dedicated workers, the third transaction’s 133MB payload spills directly into a temporary file (base/pgsql_tmp/pgsql_tmp12066.0.fileset/...). At default logging levels, PostgreSQL remains completely silent about this fallback.

Administrators must configure log_temp_files = 0 to capture these events in the system logs upon file removal:

[12066] logical replication apply worker LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12066.0.fileset/16416-759.changes.0", size 133488942
[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

Furthermore, resource spooling carries a heavy performance penalty. In subsequent PostgreSQL 19 beta 3 tests, parallel workers sat idle for up to eight seconds waiting for the leader process to finish replaying a spilled transaction from disk.

Official Responses and Operational Pitfalls

Beyond worker exhaustion, database administrators frequently encounter scenarios where parallel workers fail to start entirely—often without throwing explicit warning errors. Three primary conditions bypass parallel apply functionality:

1. Publisher Version Compatibility

The publisher instance must be running PostgreSQL 16 or newer. Pointing a modern PostgreSQL 18.6 subscription at a legacy PostgreSQL 15.19 publisher results in pg_subscription.substream reporting p, yet no parallel workers will ever spawn. The leader process will silently spool all incoming data. This is a common trap during major version upgrades where legacy primaries are temporarily paired with modern subscribers via logical replication.

2. Table Synchronization States

Every individual table bound to the subscription must be in a fully synchronized state (r). If even one table remains in copying mode—whether during an initial table sync or immediately after executing an ALTER SUBSCRIPTION ... REFRESH PUBLICATION command—the entire subscription falls back to sequential processing. Observers have noted leader processes spooling hundreds of megabytes to disk while idle parallel workers sat empty in the pool simply because a newly added table remained in state d.

3. Pending Subscription Skips

Any pending ALTER SUBSCRIPTION ... SKIP command temporarily disables parallel apply execution for the affected transaction stream. The leader worker requires absolute, sequential control over the transaction blocks to accurately identify and bypass the target LSN (Log Sequence Number).

Implications and Configuration Best Practices

Configuring parallel replication safely requires balancing resource allocation against operational visibility.

Worker Recycling and Shared Memory Costs

PostgreSQL retains finished workers in an internal pool for future reuse. Each retained worker consumes a pool slot for the entire lifespan of the subscription. The number of workers kept is calculated as half of max_parallel_apply_workers_per_subscription (rounded down):

• Setting of 2 retains 1 worker.
• Setting of 3 retains 1 worker.
• Setting of 4 retains 2 workers.
• Setting of 1 retains none.

Configuring a setting of 1 forces every large transaction to spin up a brand-new operating system process and allocate a fresh 16MB shared memory queue. In benchmarking, a cold-start worker added roughly 0.9 seconds of overhead compared to the 0.2-second response time of a warm, pooled worker.

Troubleshooting with 0

Setting max_parallel_apply_workers_per_subscription to 0 acts as a global kill switch, instantly downgrading streaming = parallel to traditional streaming = on behavior across all subscriptions via a simple SIGHUP reload. This is particularly useful when troubleshooting problematic transactions. For example, if an error occurs within a parallel worker, logs provide the remote transaction ID but omit the critical LSN required by recovery commands like ALTER SUBSCRIPTION ... SKIP:

CONTEXT:  processing remote data for replication origin "pg_16416" during message type "INSERT" for replication target relation "public.t2" in transaction 827

Temporarily setting the parameter to 0 forces the transaction to run sequentially through the leader process, generating the comprehensive LSN context required for administrative intervention (... in transaction 827, finished at 1/81661148).

Recommendations for Production Deployments

1. Leave the Default at 2 (Unless Bottlenecked): For standard workloads, the default configuration provides an optimal balance of throughput and resource consumption.
2. Scale Worker Pools Proportionally: If you choose to increase max_parallel_apply_workers_per_subscription to handle multiple concurrent bulk loads, ensure you increase max_logical_replication_workers first. Remember that configuration parameter adjustments require a server restart, whereas worker limits require only a reload.
3. Monitor Lock Wait Logs: With log_lock_waits enabled (which becomes standard behavior in PostgreSQL 19), parallel apply workers waiting on heavyweight locks held by the leader may generate log entries if transactions remain idle on the publisher longer than deadlock_timeout. Recognize that these entries typically indicate normal synchronization coordination rather than actual deadlocks.

Ultimately, max_parallel_apply_workers_per_subscription is a powerful surgical instrument for high-throughput data pipelines. When managed with an understanding of its prerequisites, silent fallback behaviors, and resource costs, it ensures that your PostgreSQL replication architecture remains robust, responsive, and resilient against massive transactional loads.