perf(PG CDC): investigate the CDC backfill performance

[st1page]

When investigating an issue with a user, we found RW might need more than 10s to scan a chunk from the Upstream PG. It could be because of many potential reasons such as:

• the table is too large with 180 million rows
• their upstream PG is an AWS Aurora with different isolation implementation
• It is also related to the workload on the upstream PG.

We need to investigate this case and set some benchmarks for CDC backfill in different situations.

Maybe also we need to doc what our CDC backfill does:

• what SQL is
• which isolation level those SQL in
• in which upstream DB's workload, the backfill could be in bad performance

So that users can know more clearly about its performance.

[StrikeW]

In current stage, I would not attribute the performance degradation to specific vendor, e.g AWS. The scenario is a new case we didn't cover in self-test and the automation pipeline: during the progress of backfilling historical data, upstream DB also has continuous insertion/update workload. I think we should also consider adding this case as a performance scenario. cc @cyliu0 @lmatz
https://risingwave-labs.slack.com/archives/C064SBT0ASF/p1709632731824109

[StrikeW]

We have implemented an optimization for cdc backfill #16349 and add LIMIT to the select query #15684, I think we can close this issue and reopen it in future if needed.