[Core] Running large database queries without crashing

[boazpoolman]

Feature request

Summary

Imagine a Strapi application, which has Webtools enabled, and has more then 100k pages in it's database. If we want to change the URL structure of those web pages, we need to then regenerate all those 100k URLs to reflect that change. If we try to do that using our Webtools bulk generation it will just overload the database and cause Strapi to crash.

Why is it needed?

We need to be able to run large database queries, without compromising the stability of our API.

Suggested solution(s)

I think there are multiple parts to finding a good solution for this problem. I put my suggestions here, but I would love to get your input on this!

1. Execute the operation as a database transaction.
That way we can make sure that all the URLs are updated at the same time. This will prevent inconsistencies in the data whilst the queries are still running, and thus provide data stability.

2. Execute the operation in a child process
By running these queries in a child process we can offload the Node.js main thread and provide api stability for our application.

3. Progress tracking
Because we're running a task that may take 30 seconds or longer, we need a way to track it's progress. Some kind of progress bar on the admin panel that shows the percentage of the query that has been finished.

4. Queueing
This step is kind of optional, but important to consider. By implementing such a feature we're introducing a concept in Strapi of executing long running tasks. By doing that it would be beneficial to have a way to queue these tasks. So that they are executed in an order that makes sense.

Related issue(s)/PR(s)

#282, #280, #13

[mrdhira]

The best practice is to implement queueing and give them just abstract queue, so it will be abstract so everyone can implement any queue (we do not handle that, for testing purpose we can use redis pub/sub).

But for robust and the fastest way is to Execute the operation as a database transaction, because it will be can implement for anyone and any database. and there will be no changes for everyone that already implement it. The trade off is if the database not well config or have smaller specs in staging/development env, it may have vary config for each env. because database transaciton will be heavy burden the database.