Resolve "Fix some of the errors that prevents the project deletion process"

What does this MR do and why?

Premise

When a project is deleted, its related associations also have to be deleted. These deletions of related associations are done using 2 ways:

• Using the ON DELETE CASCADE clause at the database level so that when a project is deleted, the records associated with it via foreign keys are also removed. This is the recommended way
• Using dependent: :destroy on the association at the Rails level. While in vanilla Rails, such deletions would not be batched, we have specifically built a solution to make such deletes work in batches of 1000, thus improving performance. See here and the implementation of destroy_dependent_associations_in_batches.

Problem

The problem with the recommended method - ie, using the ON DELETE CASCADE clause at the database level could turn problematic in cases where the associations have a large number of records. For eg, a project can have quite a really large number of issues associated with it, so when the project is deleted, it tries to delete these associated issues via the ON DELETE CASCADE clause.

And this system does not support any kind of batching, since it is done at the database level. And this has been a leading cause of database query timeouts, thus leading to failure of the entire project destroy process.

Solution

If we move such regularly problematic associations to the dependent: :destroy clause, it automatically moves to batched deletes, and batched deletions can help prevent the problem of query timeouts.

How do we identify the associations that regularly cause problems during project destroy process?

We used Kibana for that, and looking at the errors arising in ProjectDestroyWorker, we identified the common associations that have timed out in the past and this MR moves 5 such associations to dependent: :destroy, so that it can benefit from batched deletes.

This is similar to the approach we recently took with User deletes in !101098 (merged)

Way forward

After this MR is deployed, we will observe the error rates on Kibana again and we expect it to go down from the current levels. Of course, this fix alone will not solve the problem 100%, but this is a good start. If we keep observing, we might be able to also notice other such associations that are problematic and apply the same solution to those associations as well. Eventually, by following this pattern, we should be able to reach a pretty good state for project deletes.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #379023 (closed)