RE: [PATCH] Support automatic sequence replication

Dear Hackers,

> 05.
>
> LogicalRepSyncSequences() starts the transaction and read sequences every
> time.
> Can we cache the seqinfos to reuse in the next iteration? My idea is to introduce
> a syscache callback for the pg_subscription_relto invalidate the cached list.
>
> How about measuring performance once and considering it's a good
> improvement?

I profiled the sequencesync worker when sequences were less actively updated on
the publisher side. In the actively updated system, copying sequences used most
of the CPU time; thus, we could not observe the effect.

Abstract
--------------
Sequencesync worker spent 20-25% of the working time scanning pg_subscription_rel
in the workload. It's not so large compared with the total CPU time; the worker
can work once per 2 seconds or longer. We may able to consider the optimization
if there are easy ways.

Source
-----------
ea47447 + v8 patch set + attached fix patch.
To simplify the analysis, I extracted the scan part into the function
scan_subscription_relations. No configure options are set at build.

Workload
---------------
Two workloads were tested.

A - profile with no sequence updates

0. Defined 100 sequences on both nodes
1. Built a pub-sub replication system.
2. Attached the sequencesync worker as early as creating the subscription.
3. Waited 10 minutes.

B - profile with 10% sequences updates

0. Defined 100 sequences on both nodes
1. Built a pub-sub replication system.
2. Waited till the initial sync was done. On my env 100s was enough
3. Attached the sequencesync worker
4. Updated 10 sequences per second.
5. Repeat step 4 for 10 minutes.

Result
----------
The attached profiles show the detailed results: noupdate.out corresponds to
workload A, while 10percent_update.out is for workload B.

In both cases, scan_subscription_relations spends more than 20% of their working
time. Notable points are to open sequence relations with the AccessShareLock,
committing the transaction, starting the catalog scan, etc.

workload A:
```
| --20.83%--scan_subscription_relations
| |
| |--10.83%--try_table_open
| | try_relation_open
```

workload B:
```
| --24.01%--scan_subscription_relations
| |
| |--12.52%--try_table_open
| | try_relation_open
```

Consideration
--------------
Based on that, we may be able to cache seqinfos to avoid starting the
transaction and opening the sequence. But we need to introduce a relcache
callback to invalidate the specific entry of the list, not sure it's beneficial
more than the complexity.

Configuration
----------------------
Each node had shared_buffer=1GB, and others had the default GUC values.

Environment
--------------------
CPU: Intel(R) Xeon(R) Platinum 8358P CPU @ 2.60GHz, 4 cores, 1 thread per core
Memory: 15GiB
OS: AlmaLinux 9.7

Best regards,
Hayato Kuroda
FUJITSU LIMITED