Thread

Re: Logical replication prefetch

Konstantin Knizhnik <knizhnik@garret.ru> — 2025-07-26T13:27:23Z

Completely rewritten version of prefetch patch.
Now prefetch workers do not try to apply LR application and then 
rollback transaction.
They just perform indexes lookup and so prefetch index and referenced 
heap pages.
So no any hacks are needed to prevent lock conflicts and WAL logging.

Performance results are the following (test scenario was explained in 
previous message as well as used schell scripts):

update:
    prefetch (2):           5:20
    prefetch (4):           3:20
    prefetch (8):           2:05
    no prefetch:           8:30

insert:
    pk (4) prefetch:      9:55
    pk+sk(4) prefetch: 5:20
    pk+sk(8) prefetch: 3:08
    no prefetch:           9:20

The number in parentheses specifies number of prefetch workers.
For example to spawn 8 prefetch workers I used the following settings in 
postgresql.conf.replica:

prefetch_replica_identity_only=off
max_worker_processes=16
max_logical_replication_workers=16
max_parallel_apply_workers_per_subscription=8
max_parallel_prefetch_workers_per_subscription=8
port=54322


Also I run continuous test with long (3 hours) updates workload on 
publisher with logical replication to subscriber.
And with 8 prefetch workers replica is able to caught up with primary 
where 10 backends are performing update!
After the end of this updates replica was at the same state as primary 
while without prefetch it proceed only 1/2 of
generated WAL and it takes another 5:30  hours to catch up.