Add index on namespace_details.deletion_scheduled_at

Aakriti Guptarequested to merge
add-namespace-details-deletion-scheduled-index into master

What does this MR do and why?

Adds a partial index on namespace_details (deletion_scheduled_at) WHERE deletion_scheduled_at IS NOT NULL.

This is a prerequisite for the pending-delete backlog metric in !252036 (merged). That metric runs MIN(deletion_scheduled_at) and a count-over-threshold over the deletion-scheduled backlog. The only existing index is ordered (namespace_id, deletion_scheduled_at), so those aggregates can't be served efficiently — Database Lab measured the MIN at ~3.3s (cold) scanning all ~180k scheduled rows.

Per the adding database indexes guidance, the index is split into its own MR so it can be merged and confirmed live on GitLab.com (post-deploy migrations can lag several days) before the dependent metric code merges.

Query plan

With this index, MIN(deletion_scheduled_at) becomes an index-only scan reading a single row — 0.128 ms on a production-scale Database Lab clone:

Result  (cost=0.46..0.47 rows=1 width=8) (actual time=0.127..0.128 rows=1 loops=1)
  Buffers: shared hit=1 read=3
  InitPlan 1
    ->  Limit  (cost=0.42..0.46 rows=1 width=8) (actual time=0.124..0.125 rows=1 loops=1)
          ->  Index Only Scan using index_namespace_details_on_deletion_scheduled_at on public.namespace_details  (cost=0.42..7202.13 rows=168788 width=8) (actual time=0.123..0.123 rows=1 loops=1)
                Heap Fetches: 0

postgres.ai plan

Migration

  • Post-deploy migration, add_concurrent_index with disable_ddl_transaction!, reversible.
$ bundle exec rails db:migrate:main
== AddIndexToNamespaceDetailsOnDeletionScheduledAt: migrated

MR acceptance checklist

  • Reversible migration.
  • Database Lab plan documented above.
  • Index confirmed present on GitLab.com before merging !252036 (merged).

Related to #621640

Merge request reports