Batch updates and inserts when renaming name and path for descendants in Route model

Background

When there is a group with large numbers of sub-groups and projects (around 50,000), changing the path of the root group times out

Proposal

In the Route model we iterate over each descendants (subgroup) and invoke a single-record UPDATE and a single-record INSERT queries. This can keep the transaction open for quite some time and it might also run into web timeout for very large groups.

We can reduce the network round trips for single-record statements if we batch these queries, let's say invoke an UPDATE query and INSERT query for every 100 records.

Generally, batching in a transaction is discouraged however, in this case we'll see improved runtime and it's less risky than invoking single-record statements.

Benchmark the resultant performance for changing group path at different numbers of subgroups (100, 1000, 50,000, 100,000),

Edited Nov 19, 2023 by Thong Kuah