Bulk delete is too slow

[brian-mulier-p]

Explain the bug

Currently as a quick win what was done is that both for by-ids & by-query deletions, we retrieve executions first:

• for by-ids we do 1 fetch request / id included in the request (as we allow a 100 executions pagination, it can take a lot of queries) :red_circle:
• for by-query we do 1 fetch request with given filters :heavy_check_mark:

Then in both case for each retrieved execution we do a repository delete (which doesn't do bulk at all) which can be time & resource consuming :red_circle:

What I suggest is in both case do a bulk delete query:

• by-ids might split into multiple batch delete queries to prevent having a huge where (testing should be done, might be unnecessary)
• by-query should do a single delete query with the given filters

This will greatly reduce deletion times.

Another solution would be to make this process asynchronous but we may need to provide a view to see the deletion progress which might be hard :thinking:

In any way I think the first solution is the go-to for now as it will heavily reduce database load.

Environment Information

• Kestra Version:
• Operating System and Java Version (if not using Kestra Docker image):

[loicmathieu]

By careful that some databases limit the number of elements in a IN clause so we may limit the bulk to for ex 100. This needs to be evaluated