Does PostgreSQL support multi-threaded replication?

Question

Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ?

I'm asking this since Mariadb support this and currently I'm learning both these dbs.

Answer 1

The standard streaming replication on PostgreSQL is single threaded and there is no way to change this. However the question is why would you want to?

PostgreSQL's streaming replication works through the write ahead log which is kinda of like a set of instructions "change block 3525 to this", "change block 2424 to this", etc. This makes the replication process very fast as it not re-executing SQL, its simply doing the same data file updates that the master has done. Typically the slowness with replication on PostgreSQL is IO bound rather than CPU bound so executing the recovery in parallel will actually make it slower not faster. This is because the effect of interleaving the IO requests will potentially make sequential IO random. Of course modern SAS drives should be able to reorder the requests for data back into sequential order.

I suspect the question came about because of the statement shipping type of replication MySQL and MariaDB can do, in this case yes of course it makes perfect sense to interleave the statements as there will be a much higher CPU cost and running this on multi-core hardware will offer a great performance improvement.

Answer 2

PostgreSQL in current version (13) has single process for replaying Write Ahead Log (WAL) files. Though, base database replication can be done in parallel.

PostgreSQL has concepts of replication slots. Each slot could be used to stream WALs to another (standby) PostgreSQL instance. Maximum number of slots can be checked using show max_wal_senders;.

You can even go further and create a tree-like replication with single master, in case the primary could not handle large number of streaming slots.

But we still have one slot dedicated to one replica (standby) server.

WALs are binary files, each of 16 MB size (by default). You can use pg_waldump to inspect WAL file

$ /usr/lib/postgresql/13/bin/pg_waldump 13/main/pg_wal/0000000500007F2400000099 -n 5
rmgr: Heap        len (rec/tot):     59/  4367, tx: 1918841963, lsn: 7F24/9900AA00, prev 7F24/99008A28, desc: LOCK off 19: xid 1918841963: flags 0x00 LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16436/1129985291 blk 70795 FPW
rmgr: Heap        len (rec/tot):     59/  8139, tx: 1918841963, lsn: 7F24/9900BB10, prev 7F24/9900AA00, desc: LOCK off 50: xid 1918841963: flags 0x00 LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16436/1129985291 blk 101296 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx: 1918841963, lsn: 7F24/9900DAF8, prev 7F24/9900BB10, desc: COMMIT 2021-08-30 03:22:38.801477 UTC
rmgr: Heap        len (rec/tot):    121/   121, tx: 1918841964, lsn: 7F24/9900DB20, prev 7F24/9900DAF8, desc: INSERT off 47 flags 0x04, blkref #0: rel 1663/16436/1129985298 blk 1744836
rmgr: Btree       len (rec/tot):     64/    64, tx: 1918841964, lsn: 7F24/9900DBA0, prev 7F24/9900DB20, desc: INSERT_LEAF off 244, blkref #0: rel 1663/16436/1130829680 blk 457959
rmgr: Btree       len (rec/tot):     53/  8013, tx: 1918841964, lsn: 7F24/9900DBE0, prev 7F24/9900DBA0, desc: INSERT_LEAF off 61, blkref #0: rel 1663/16436/1130830062 blk 1054560 FPW

As you can see it ain't SQL, it's a bunch of statements directly database storage, that needs to be replayed sequentially (in order to maintain consistency).

Some tools, like repmgr, will create 2 slots when performing full DB cloning. The first slot will be used for pg_basebackup and then later for WALs streaming. Once base backup is completed, WALs copied while copying the whole database, will be replayed and later only single streaming slot will be used to retrieve new WAL changes.

pg_stat_replication on primary server can be used to inspect the replication state:

$ psql -c "select state, write_lag, replay_lag, flush_lag from pg_stat_replication;"
   state   |         write_lag    |   replay_lag    |    flush_lag    
-----------+-----------------+-----------------+-----------------
 backup    |                  |                 | 
 streaming |  14:16:56.785663 | 33:10:45.033382 | 14:16:56.896178

streaming slot is lagging behind because backup hasn't finished yet.

When you're creating new database cluster, you can specify WAL segment size (later can't be changed):

pg_createcluster 13 main -- --wal-segsize=32

This might slightly improve I/O, data will be transferred in larger chunks (32 MB in this case). There will be less files in pg_wal directory. For large DB clusters with huge INSERTs/UPDATEs it could mean less frequent syncing to disk. With existing cluster you can check the size using pg_controldata command:

$ /usr/lib/postgresql/12/bin/pg_controldata /var/lib/postgresql/12/main | grep "WAL segment"
Bytes per WAL segment:                16777216

pgBackRest or pg_probackup could be used to parallelize the backup phase. Backups will be streamed using multiple threads/processes (try not to overload the primary server), though there might be still a bottleneck with single process for replaying WALs.

This might become an issue on a multi-core system (e.g. AMD) where many processes can be used to write data (different transactions, different tables). But only single core is utilized for replaying changes. This could cause substantial replica lag and possibly filling up the disk with WALs.