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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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
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
$ /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.