Batch counting performance issues for concurrent and large tables

Batch counting performance of the last portion of the loops

Discussion

Discussed in !42556 (comment 414863796)

1. Without batching we have we shall have without batching and risk will be "small". So we shall get results from >>90% self-managed instances. 2.with batching the risk is large.

• With 178 million rows and 1250 expected batch size we shall loop 143K times.
• Now what's worse is that, with there's higher concurrency and more "hits" in the most recent ci_pipelines records.
• So we risk doing "Takes a large number of batches, causing stress on the database" and not completing succesfully, because we shall fail at the end. This will be like failing at the last meters of a 40KM marathon.

Solution Ideas

• Count in reverse to fail fast
• Omit the last 1% of the range
• Skip locked construct for batch counting