Skip to content

Skipped pending deleted projects for root_storage_statistics

Suraj Tripathi requested to merge issue_411907_remove_softdeleted_projects_from_storage_calc into master

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.

Edited by Suraj Tripathi

Merge request reports