Poor performance querying namespaces ordered by updated_at due to missing index
cc @gitlab-com/gl-security/security-operations/trust-and-safety
Background
There is poor performance when making queries on namespaces
ordered by updated_at
as there is currently no index. This is impacting the Trust and Safety's internal use case (confidential issue: https://gitlab.com/gitlab-com/gl-infra/reliability/-/issues/17423).
Proposal
Add a new index index_namespaces_on_updated_at
asynchronously following the "Create indexes asynchronously" guide in the Contribute to GitLab documentation.
Implementation guide
-
Create and merge an MR adding a post-deployment migration to prepare for the asynchronous index creation -
Verify the index was created and is valid -
Create and merge an MR adding a migration to create the index synchronously
Edited by Ruby Nealon