Removing projects should remove CI builds efficiently

Currently removing a project will result in associated CI builds data being removed by Rails, using 1 DELETE query per associated row. This should be changed so that the database data is removed using a foreign key's ON DELETE CASCADE constraint. Associated file system data should preferably be removed using as few rm calls as possible.

mentioned in issue gitlab-foss#27998 (closed)

Labelling this AP1 in GitLab FOSS since this has a large impact on project removals, especially if a lot of CI builds exist for a to-be-removed project. This depends on some other CI migrations being done first (I can't remember the issue link at this time).

added AP1 in GitLab FOSS label

Slightly related, but I wonder if we should trigger the jobs to delete review apps when a project is removed.

@grzesiek How much effort do you think this is? Can we slip it in soon?

@markpundsack I think it is a good idea to resolve this soon.

I suppose that we need:

• add foreign key constraints to ci_builds and ci_pipelines
• make it possible to schedule removal of build-related files asynchronously

Does it make sense @yorickpeterse?

@grzesiek For removing the FS data the idea was to move all files in a common directory for the project, e.g. files/project-id/artifacts. This way we can just remove all CI data using rm -rf files/common-directory.

Doing that async is an option, but it requires us to schedule the data in such a way that it can deal with the DB data already being removed. If we do this by iterating over the builds and scheduling a job per build that could work, but I prefer a solution where we never have to iterate over the builds.

changed milestone to %Next 2-3 months in gitlab-foss

Making this AP2 for now since so far we seem to be managing fine availability wise, so this is not a "everything is on fire" kind of issue.

moved from gitlab-foss#35240 (moved)