Investigate batch processing for updating TI UUIDs to avoid long locks on large datasets

[kaxil]

This is part of the future work of #43243 that migrate TI table's primary key to be a UUID7 string

Investigate batch processing for updating UUIDs to avoid long locks on large datasets. Maybe run with batches of 10000 rows??? -- TBD. We can also run some benchmarks once we do that.

[rawwar]

I've done some reading and I now find this doable. @kaxil , correct me if I am wrong here.

All I need to do is, consider a reasonably large TI table. Also, consider a reasonable-sized DB instance type(probably with 2vcpu and 8 GB RAM)(probably going to run DB on k8s pod and limit the resources).

I think time taken should be the only metric I should be keeping in mind. So, I'll start without batching and time it, then with batching, look for other optimizations and try to figure out the best way to reduce the time taken.

EDIT: One thing I just realised is that I forgot to think about "avoiding long locks". I'll look into how this is detected and give an update here. Simply guessing, batching should take care of this.

EDIT2: I think, I also need to consider the query timeout here. Which I think, is typically 300 seconds.

[rawwar]

@kaxil , one question. Do we really need to worry about the long locks? Given that this is a major upgrade, users will assume a maintenance window with downtime.

Although, I'm more worried about the query timeouts.

[kaxil]

Correct, query_timeout and just time to run the migration are key to measure and optimize