Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions - Mailing list pgsql-hackers

I was running some tests on this patch. I was generally trying to see how the patch affects logical replication when doing bulk inserts. This issue has been raised in the past, for eg: this [1].

My test setup is:

1. Two postgres servers running - A and B

2. Create a pgbench setup on A. (pgbench -i -s 5 postgres)

3. replicate the 3 tables (schema only) on B.

4. Three publishers on A for the 3 tables of pgbench; pgbench_accounts, pgbench_branches and pgbench_tellers;

5. Three subscribers on B for the same tables. (streaming on and off based on the scenarios described below)

run pgbench with : pgbench -c 4 -T 100 postgres

While pgbench is running, Do a bulk insert on some other table not in the publication list (say t1); INSERT INTO t1 (select i FROM generate_series(1,10000000) i);

Four scenarios:

1. Pgbench with logical replication enabled without bulk insert

Avg TPS (out of 10 runs): 641 TPS

2.Pgbench without logical replication enabled with bulk insert (no pub/sub)

Avg TPS (out of 10 runs): 665 TPS

3, Pgbench with logical replication enabled with bulk insert

Avg TPS (out of 10 runs): 278 TPS

4. Pgbench with logical replication streaming on with bulk insert

Avg TPS (out of 10 runs): 440 TPS

As you can see, the bulk inserts, although on a totally unaffected table, does impact the TPS. But what is good is that, enabling streaming improves the TPS (about 58% improvement)

[1] - https://www.postgresql.org/message-id/flat/CAMsr%2BYE6aE6Re6smrMr-xCabRmCr%3DyzXEf2Yuv5upEDY5nMX8g%40mail.gmail.com#dbe51a181dd735eec8bb36f8a07bacf5