Clean up orphaned redirect routes from deleted namespaces

Tomasz Skoruparequested to merge
tskorupa/delete-orphan-redirect-routes-namespace-rows into master

What does this MR do and why?

This MR removes redirect routes that are no longer valid because their associated namespaces have been deleted from the database, and then validates the sharding key constraint.

When a namespace is deleted, any redirect routes pointing to it become orphaned - they reference a namespace that no longer exists. These orphaned routes take up space in the database and can cause issues during data migration operations.

This cleanup ensures the database stays clean and consistent, which is important for the Cells 1.0 migration work where we need to move organization data between database instances. After removing the orphaned routes, we validate the sharding key constraint to ensure all remaining redirect routes have a valid namespace_id.

How does it work?

The migration consists of two steps:

  1. Delete orphaned routes: Identifies and removes redirect routes that:
    • Point to a deleted namespace (source_type = 'Namespace')
    • Have no namespace_id value (namespace_id IS NULL)
    • The cleanup is done in batches to avoid locking the entire table
  2. Validate sharding key constraint: Once orphaned routes are removed, validates the NOT NULL constraint on the namespace_id column to ensure all remaining redirect routes have a valid sharding key value.

SQL query 

DELETE FROM "redirect_routes" WHERE ("redirect_routes"."id") IN (SELECT "redirect_routes"."id" FROM "redirect_routes" LEFT OUTER JOIN namespaces ON redirect_routes.source_id = namespaces.id WHERE "redirect_routes"."source_type" = 'Namespace' AND "redirect_routes"."namespace_id" IS NULL AND "redirect_routes"."id" >= 9 AND "namespaces"."id" IS NULL)

EXPLAIN plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/46316/commands/141438

Related issue

Addresses #561339

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tomasz Skorupa

Merge request reports