PostgreSQL and Databases in general

You can control the degree of parallelism using the max_parallel_apply_workers_per_subscription parameter. By default, this is set to 2, allowing two large transactions to be applied in parallel per subscription.

How Parallel Apply Works

Parallel apply significantly improves the efficiency of large transaction handling by not waiting for the COMMIT message from the publisher before applying changes. Previously, the subscriber would write all streamed data to temporary files and apply them only after receiving the commit record.

Architecture Overview

When the first stream of a large transaction arrives, the leader apply worker (LA) assigns a parallel apply worker (PA)—if available. The LA sends changes to the PA via a dedicated shared memory queue (shm_mq). The PA applies the changes and waits for the next stream. This worker remains assigned until the transaction is committed.

At commit time, the LA waits for the PA to finish to ensure commit order is preserved, which is critical to avoid:

• Transaction dependencies: e.g., inserting a row in one transaction and updating it in another.
• Deadlocks: e.g., transactions updating the same rows/tables in reverse order.

Locking Considerations

Parallel apply introduces potential deadlocks, especially when transactions are independent on the publisher but become interdependent on the subscriber due to schema differences (e.g., constraints, indexes).

To detect such deadlocks between LA and PA workers, PostgreSQL uses heavy-weight locks during:

• PA waiting for the next stream
• LA waiting for PA to finish

Example 1: Deadlock Due to Conflicting Changes

Consider a scenario where a table lacks a unique key on the publisher but has one on the subscriber. If:

• PA is applying TX-1
• LA is applying TX-2

...and both transactions conflict due to the unique constraint, a deadlock can occur:

• LA waits for a lock on the unique index
• PA waits for the next stream from LA
• → Deadlock cycle: LA → PA → LA

PostgreSQL detects and reports such deadlocks. Users can skip problematic transactions using: ALTER SUBSCRIPTION ... SKIP ... command.

Example 2: Deadlock Due to shm_mq Buffer Full

In a variant of the previous example:

• PA-1 and PA-2 apply TX-1 and TX-2 respectively.
• PA-2 waits for PA-1 to finish.
• PA-1 waits for input from LA.
• If the shm_mq buffer between LA and PA-2 is full, LA cannot send messages.

To avoid this:

• LA uses non-blocking writes with a timeout.
• If the timeout is exceeded, LA serializes pending messages to a file.
• PA-2 reads from the file, freeing LA to continue processing PA-1.

This mechanism prevents deadlocks caused by full shm_mq buffers.

Performance

 create table tbl_parallel_test(c1 int, c2 char(1000)); 
 insert into tbl_parallel_test values(generate_series(1,1000000),'aaaaa'); 
 Analyze tbl_parallel_test; 
 Explain analyze select * from tbl_parallel_test where c1 < 10000 and 
 c2 like '%bb%'; 
 QUERY PLAN 
 ------------------------------------------------------------------------------------------------------------- 
 Seq Scan on tbl_parallel_test 
 (cost=0.00..157858.09 rows=1 width=1008) 
 (actual time=378.414..378.414 rows=0 loops=1) 
 Filter: ((c1 < 10000) AND (c2 ~~ '%bb%'::text)) 
 Rows Removed by Filter: 1000000 
 Planning time: 0.075 ms 
 Execution time: 378.431 ms 
 (5 rows) 

 set max_parallel_degree = 6; 
 Explain analyze select * from tbl_parallel_test where c1 < 10000 
 and c2 like '%bb%'; 
 QUERY PLAN 
 ------------------------------------------------------------------------------------------------------------- 
 Gather (cost=1000.00..29701.57 rows=1 width=1008) 
 (actual time=182.708..182.708 rows=0 loops=1) 
 Number of Workers: 5 
 -> Parallel Seq Scan on tbl_parallel_test 
 (cost=0.00..28701.47 rows=1 width=1008) 
 (actual time=179.496..1081.120 rows=0 loops=1) 
 Filter: ((c1 < 10000) AND (c2 ~~ '%bb%'::text)) 
 Rows Removed by Filter: 1000000 
 Planning time: 0.078 ms 
 Execution time: 200.610 ms 
 (7 rows) 

 create or replace function calc_factorial(a integer, fact_val integer) 
 returns integer 
 as $$ 
 begin 
 perform (fact_val)!; 
 return a; 
 end; 
 $$ language plpgsql PARALLEL Safe; 

 Explain analyze select * from tbl_parallel_test where 
 c1 < calc_factorial(10000, 10) 
 and c2 like '%bb%'; 
 QUERY PLAN 
 -------------------------------------------------------------------------------- 
 Gather (cost=1000.00..75154.99 rows=1 width=1008) 
 (actual time=120566.456..120566.456 rows=0 loops=1) 
 Number of Workers: 5 
 -> Parallel Seq Scan on tbl_parallel_test 
 (cost=0.00..74154.89 rows=1 width=1008) 
 (actual time=119635.421..359721.498 rows=0 loops=1) 
 Filter: ((c2 ~~ '%bb%'::text) AND (c1 < calc_factorial(10000, 10))) 
 Rows Removed by Filter: 1000000 
 Planning time: 54.904 ms 
 Execution time: 120622.631 ms 
 (7 rows) 

 Alter Function calc_factorial(integer, integer) PARALLEL Unsafe; 
 Explain analyze select * from tbl_parallel_test where 
 c1 < calc_factorial(10000, 10) 
 and c2 like '%bb%'; 
 QUERY PLAN 
 -------------------------------------------------------------------------------- 
 Seq Scan on tbl_parallel_test 
 (cost=0.00..407851.91 rows=1 width=1008) 
 (actual time=33166.138..33166.138 rows=0 loops=1) 
 Filter: ((c2 ~~ '%bb%'::text) AND (c1 < calc_factorial(10000, 10))) 
 Rows Removed by Filter: 1000000 
 Planning time: 0.162 ms 
 Execution time: 33166.208 ms 
 (5 rows) 

 shared_buffers=32GB; min_wal_size=5GB; max_wal_size=10GB 
 checkpoint_timeout =30min; max_connections=300; 
 max_worker_processes=100; 

 create table tbl_perf(c1 int, c2 char(1000)); 
 insert into tbl_perf values(generate_series(1,30000000),'aaaaa'); 
 Explain analyze select c1 from tbl_perf where 
 c1 > calc_factorial(1,10ドル) and 
 c2 like '%aa%'; 

 --"to select 1% of rows, below query can be used" 
 Explain analyze select c1 from tbl_perf where 
 c1 > calc_factorial(29700000,10) and 
 c2 like '%aa%';" 
 --"to select 10% of rows, below query can be used" 
 Explain analyze select c1 from tbl_perf where 
 c1 > calc_factorial(27000000,10) and 
 c2 like '%aa%';" 
 --"to select 25% of rows, below query can be used" 
 Explain analyze select c1 from tbl_perf where 
 c1 > calc_factorial(22500000,10) and 
 c2 like '%aa%';"