pgsql: Perform apply of large transactions by parallel workers. - Mailing list pgsql-committers

Perform apply of large transactions by parallel workers.

Currently, for large transactions, the publisher sends the data in
multiple streams (changes divided into chunks depending upon
logical_decoding_work_mem), and then on the subscriber-side, the apply
worker writes the changes into temporary files and once it receives the
commit, it reads from those files and applies the entire transaction. To
improve the performance of such transactions, we can instead allow them to
be applied via parallel workers.

In this approach, we assign a new parallel apply worker (if available) as
soon as the xact's first stream is received and the leader apply worker
will send changes to this new worker via shared memory. The parallel apply
worker will directly apply the change instead of writing it to temporary
files. However, if the leader apply worker times out while attempting to
send a message to the parallel apply worker, it will switch to
"partial serialize" mode -  in this mode, the leader serializes all
remaining changes to a file and notifies the parallel apply workers to
read and apply them at the end of the transaction. We use a non-blocking
way to send the messages from the leader apply worker to the parallel
apply to avoid deadlocks. We keep this parallel apply assigned till the
transaction commit is received and also wait for the worker to finish at
commit. This preserves commit ordering and avoid writing to and reading
from files in most cases. We still need to spill if there is no worker
available.

This patch also extends the SUBSCRIPTION 'streaming' parameter so that the
user can control whether to apply the streaming transaction in a parallel
apply worker or spill the change to disk. The user can set the streaming
parameter to 'on/off', or 'parallel'. The parameter value 'parallel' means
the streaming will be applied via a parallel apply worker, if available.
The parameter value 'on' means the streaming transaction will be spilled
to disk. The default value is 'off' (same as current behaviour).

In addition, the patch extends the logical replication STREAM_ABORT
message so that abort_lsn and abort_time can also be sent which can be
used to update the replication origin in parallel apply worker when the
streaming transaction is aborted. Because this message extension is needed
to support parallel streaming, parallel streaming is not supported for
publications on servers < PG16.

Author: Hou Zhijie, Wang wei, Amit Kapila with design inputs from Sawada Masahiko
Reviewed-by: Sawada Masahiko, Peter Smith, Dilip Kumar, Shi yu, Kuroda Hayato, Shveta Mallik
Discussion: https://postgr.es/m/CAA4eK1+wyN6zpaHUkCLorEWNx75MG0xhMwcFhvjqm2KURZEAGw@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/216a784829c2c5f03ab0c43e009126cbb819e9b2

Modified Files
--------------
doc/src/sgml/catalogs.sgml                         |   11 +-
doc/src/sgml/config.sgml                           |   28 +-
doc/src/sgml/logical-replication.sgml              |   22 +-
doc/src/sgml/monitoring.sgml                       |    5 +
doc/src/sgml/protocol.sgml                         |   29 +-
doc/src/sgml/ref/create_subscription.sgml          |   24 +-
doc/src/sgml/system-views.sgml                     |   14 +-
src/backend/access/transam/xact.c                  |   24 +-
src/backend/commands/subscriptioncmds.c            |   67 +-
src/backend/libpq/pqmq.c                           |   18 +-
src/backend/postmaster/bgworker.c                  |    3 +
src/backend/postmaster/interrupt.c                 |    5 +-
.../libpqwalreceiver/libpqwalreceiver.c            |    6 +-
src/backend/replication/logical/Makefile           |    1 +
.../replication/logical/applyparallelworker.c      | 1630 ++++++++++++++++++++
src/backend/replication/logical/decode.c           |    5 +-
src/backend/replication/logical/launcher.c         |  220 ++-
src/backend/replication/logical/meson.build        |    1 +
src/backend/replication/logical/origin.c           |   26 +-
src/backend/replication/logical/proto.c            |   37 +-
src/backend/replication/logical/reorderbuffer.c    |    5 +-
src/backend/replication/logical/tablesync.c        |   25 +-
src/backend/replication/logical/worker.c           | 1386 ++++++++++++++---
src/backend/replication/pgoutput/pgoutput.c        |   22 +-
src/backend/storage/ipc/procsignal.c               |    4 +
src/backend/storage/lmgr/lmgr.c                    |   46 +
src/backend/tcop/postgres.c                        |    3 +
src/backend/utils/activity/wait_event.c            |    6 +
src/backend/utils/adt/lockfuncs.c                  |   16 +-
src/backend/utils/misc/guc_tables.c                |   12 +
src/backend/utils/misc/postgresql.conf.sample      |    1 +
src/bin/pg_dump/pg_dump.c                          |    6 +-
src/bin/psql/describe.c                            |   21 +-
src/include/catalog/catversion.h                   |    2 +-
src/include/catalog/pg_subscription.h              |   21 +-
src/include/commands/subscriptioncmds.h            |    2 +
src/include/replication/logicallauncher.h          |    1 +
src/include/replication/logicalproto.h             |   28 +-
src/include/replication/logicalworker.h            |    9 +
src/include/replication/origin.h                   |    2 +-
src/include/replication/pgoutput.h                 |    2 +-
src/include/replication/reorderbuffer.h            |    4 +-
src/include/replication/walreceiver.h              |    2 +-
src/include/replication/worker_internal.h          |  227 ++-
src/include/storage/lmgr.h                         |    5 +
src/include/storage/lock.h                         |   17 +-
src/include/storage/procsignal.h                   |    1 +
src/include/utils/wait_event.h                     |    2 +
src/test/regress/expected/subscription.out         |   46 +-
src/test/regress/sql/subscription.sql              |    6 +-
src/test/subscription/t/015_stream.pl              |  274 +++-
src/test/subscription/t/016_stream_subxact.pl      |  130 +-
src/test/subscription/t/017_stream_ddl.pl          |    3 +
.../subscription/t/018_stream_subxact_abort.pl     |  220 ++-
.../subscription/t/019_stream_subxact_ddl_abort.pl |    3 +
src/test/subscription/t/022_twophase_cascade.pl    |    2 +
src/test/subscription/t/023_twophase_stream.pl     |  525 ++++---
src/tools/pgindent/typedefs.list                   |    7 +
58 files changed, 4511 insertions(+), 759 deletions(-)