Logical Replication handling of large transactions: Hou Zhijie & Hayato Kuroda - PGCon 2023

Logical Replication handling of large transactions: Effective strategies for replication, streaming, and storage.

Key takeaways

Logical decoding and replication handle large transactions by streaming chunks and storing them in temporary files or disk.
The max-parallel-apply-workers-per-subscription GUC parameter controls the number of parallel workers per subscription.
In parallel mode, the apply worker receives messages and applies them immediately, without waiting for commit messages.
The streaming subscription parameter specifies whether in-progress transactions can be streamed.
Logical decoding can be fine-grained controlled over both the physical and logical applications.
The pg_logical_sort function can be used to extract changes from the publication.
The create-publication command is used to create a publication and the create-subscription command is used to create a subscription.
The pg_start_activity and pg_start_subscription system views provide information about the status of logical decoding and replication.
The pg_logical_decoding_workman parameter sets the maximum amount of memory used by the logical decoding workman.
The pg_publication system catalog stores information about publications and subscriptions.
The pg_publication_system catalog stores information about system views and functions.
The pg_logical_decoding function can be used to decode and apply changes to the database.
The pg_apply function can be used to apply changes to the database.
The pg_stream function can be used to stream changes to the database.
The pg_skip function can be used to skip transactions in the replication process.
The pg_commit function can be used to commit transactions in the replication process.
The pg_abort function can be used to abort transactions in the replication process.
The pg_prepare function can be used to prepare transactions in the replication process.
The pg_commit_prepared function can be used to commit prepared transactions in the replication process.
The pg_stream_commit function can be used to stream and commit transactions in the replication process.
The pg_stream_abort function can be used to stream and abort transactions in the replication process.
The pg_stream_prepare function can be used to stream and prepare transactions in the replication process.
The pg_stream_commit_prepared function can be used to stream and commit prepared transactions in the replication process.
The pg_logical_decoding_workman parameter sets the maximum amount of memory used by the logical decoding workman.
The pg_logical_decoding_workman function can be used to decode and apply changes to the database.
The pg_apply function can be used to apply changes to the database.
The pg_stream function can be used to stream changes to the database.
The pg_skip function can be used to skip transactions in the replication process.
The pg_commit function can be used to commit transactions in the replication process.
The pg_abort function can be used to abort transactions in the replication process.
The pg_prepare function can be used to prepare transactions in the replication process.
The pg_commit_prepared function can be used to commit prepared transactions in the replication process.
The pg_stream_commit function can be used to stream and commit transactions in the replication process.
The pg_stream_abort function can be used to stream and abort transactions in the replication process.
The pg_stream_prepare function can be used to stream and prepare transactions in the replication process.
The pg_stream_commit_prepared function can be used to stream and commit prepared transactions in the replication process.

Logical Replication handling of large transactions: Hou Zhijie & Hayato Kuroda - PGCon 2023

More talks