On Wed, Mar 5, 2025 at 2:35 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> I did a performance testing with HEAD and v2-0001, and confirmed that it could
> improve performance around 20% in the typical case.
>
> Workload
> ======
> I emulated a scenario that there are many tables to be published and only one
> table is stop and resume publishing. In HEAD, ALTER PUBLICATION DROP/ADD command
> invalidates all entries in the RelSyncCache, but v2 invalidates only a needed
> one - this means the decoding time would be reduced after patching.
>
> 1. Initialized an instance
> 2. Created a root table and 5000 leaf tables.
> 3. Created another table which did not have parent-child relationship.
> 4. Created a publication which included a root table and another table
> 5. Created a replication slot with pgoutput plugin.
> 6. Executed a transaction which would be decoded. In the transaction:
> a. Inserted tuples to all the leaf tables
> b. Altered publication to drop another table
> c. Altered publication again to add the dropped one
> d. Inserted tuples to all the leaf tables again.
> 7. measured decoding time via SQL interfaces, five times.
>
> Attached script automated above.
>
> Results
> =====
> Each cell is a median value of 5 runs. Compared with HEAD, V2-0001 can reduce
> the decoding time, relatively 20%.
>
Yeah, this is a good improvement and the patch looks safe to me, so I
pushed with minor changes in the comments.
..
>
> I'm planning to do further tests to prove the benefit for 0002, 0003 patches.
>
Ideally, we need the time of only step-6d with and without the patch.
Step 6-a is required to build the cache, but in actual workloads via
walsender, we won't need that, and also, at the end of SQL API
execution, we will forcibly invalidate all caches, so that is also not
required. See, if in the performance measurement of the next set of
patches, you can avoid that.
--
With Regards,
Amit Kapila.
