RE: Conflict detection for update_deleted in logical replication

Dear hackers,

I've created a new script which simulates that user reduce the workload on the
publisher side. Attached zip file contains a script, execution log and pgbench
outputs. Experiments were done with v24 patch set.

Abstract
======

In this test the conflict slot could be invalidated as expected when the workload
on the publisher was high, and it would not get invalidated anymore after reducing
the workload. This shows even if the slot has been invalidated once, users can
continue to detect the update_deleted conflict by reduce the workload on the publisher.
Also, the transaction per second on the subscriber side can be mostly same as
retain_conflict_info = off case after reducing workload on the pub.

Workload
========
v23_measure.sh is a script I used. It is bit complex but mostly done below things:

1. Construct a pub-sub replication system.
2. Run a pgbench (tcp-b like workload) on both nodes. Initially the parallelism
of pgbench is 30 on both nodes. While running the benchmark TPS has been replicated
once per 1 second.
3. Check the status of the conflict slot periodically.
4. If the conflict slot is invalidated, stop the pgbench for both nodes.
5. Disable the retain_conflict_info option and wait until the conflict slot is dropped.
6. Wait until all the changes on the publisher is replicated to the subscriber.
7. Enable the retain_conflict_info and wait until the conflict slot is created.
8. Re-run the pgbench on both nodes. At that time, the parallelism for the publisher
side is cut in half.
9. loop step 3-8 until the total benchmark time becomes 900s.

Parameters
==========

Publisher GUCs:
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
wal_level = logical

Subscriber GUCs:

autovacuum_naptime = '30s'
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
track_commit_timestamp = on

max_conflict_retention_duration is varied twice, 60s and 120s.

Results for max_conflict_retention_duration = 60s
================================

Parallelism of the publisher side is reduced till 30->15->7->3 and finally the
conflict slot is not invalidated. Below tables show 1) parallelism of the bgbench run,
2) time period for the parallelism, and 3) observed TPS of each iterations.

Publisher side
nclients Ran duration (s) TPS
30 80 34587.9
15 83 19148.2
7 87 9609.1
3 647 4120.7

subscriber side
nclients Ran duration (s) TPS
30 80 10688
30 83 10834
30 87 12327.5
30 647 33300.1

For 30/15/7 cases, the conflict slot has been invalidated around 80s later, but
it can survive for parallelism = 3. At that time the TPS on the subscriber side
becomes mostly same as the publisher (nclients=30).

Results for max_conflict_retention_duration = 120s
=================================

The trend was mostly same as 60s case.

Publisher side
nclients Ran duration TPS
30 155 28979.3
15 157 19333.9
7 196 9875.2
3 389 4539

subscriber side
nclients Ran duration TPS
30 155 5925
30 157 6912
30 196 9157.1
30 389 35736.6

Noticed
=====

While creating the script, I found that step 6 (Wait until all the changes on the
publisher is replicated to the subscriber) was necessary. If it was skipped,
the slot would be invalidated soon. This is because the remained changes are not
replicated to the subscriber side yet and the catchup is delayed due to them.

Best regards,
Hayato Kuroda
FUJITSU LIMITED