PostgreSQL中大型事务的逻辑解码

logical_decoding_work_mem=”64kB”;
postgres[89179]=# CREATE TABLE stream_test(data text);
CREATE TABLE
postgres[89179]=# BEGIN;
BEGIN
postgres[89179]=#* INSERT INTO stream_test SELECT repeat('a', 2000) || g.i FROM generate_series(1, 35) g(i);
INSERT 0 35
postgres[89179]=#* SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL,NULL, 'stream-changes', '1');
data
--------------------------------------------------
BEGIN 553
COMMIT 553
opening a streamed block for transaction TXN 554
streaming change for TXN 554
streaming change for TXN 554
streaming change for TXN 554
streaming change for TXN 554
streaming change for TXN 554
streaming change for TXN 554
…..
closing a streamed block for transaction TXN 554
(35 rows)

CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub WITH (streaming = on)

[publisher configurations]:
wal_level = logical
synchronous_standby_names = '*' [this is set so that commit will wait for response from downstream]
logical_decoding_work_mem=10MB

[publisher setup]:
CREATE TABLE publish_table(a int ,b text);
CREATE TABLE non_publish_table(a int ,b text);
CREATE PUBLICATION test_pub FOR TABLE publish_table;

[subscription setup]
CREATE TABLE publish_table(a int ,b text);
CREATE SUBSCRIPTION test_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=postgres' PUBLICATION test_pub;

[performance test]:
Execute a large transaction on the publisher and measure the total commit time (local commit + subscriber apply + ack from the subscriber)
The transaction operates on published as well as non-published tables

BEGIN;
INSERT INTO non_publish_table SELECT i, REPEAT('x', 10) FROM generate_series(1,5000000) AS i;
INSERT INTO publish_table SELECT i, REPEAT('x', 10) FROM generate_series(1,1000000) AS i;
COMMIT;

Time taken for COMMIT: 5,7087.004 ms

Enable the streaming and recheck the performance.
ALTER SUBSCRIPTION test_sub SET(STREAMING = ON)
Time taken for COMMIT: 2,3967.789 ms