Skipped pending deleted projects for root_storage_statistics
What does this MR do and why?
This MR ensures we no longer count projects that have been marked for deletion in the usage quota calculation
- Remove soft deleted projects from the list of projects we pick for storage calculation
ref: https://gitlab.com/gitlab-org/gitlab/-/issues/411907
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Database
The new query is similar to the first one.
An index index_projects_aimed_for_deletion
is already present for the new filter condition, but I think because the Selectivity was very low, Postgres decided not to use it to filter the result.
Updated Query
SELECT "projects".* FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{4249178}'))) AND "projects"."marked_for_deletion_at" IS NULL AND "projects"."pending_delete" = FALSE
QueryPlan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/18893/commands/62678
Original Query
SELECT "projects".* FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{4249178}')))
QueryPlan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/18893/commands/62683
Difference in the number of project beacuse of the updated query
[ gprd ] production> g.all_projects.count
=> 52701
[ gprd ] production> g.all_projects.not_aimed_for_deletion.count
=> 52419
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.