Poor performance querying users 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 users
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), as well as the users API for administrators.
Example:
EXPLAIN ANALYZE SELECT "users"."id" FROM "users" ORDER BY "users"."updated_at" DESC LIMIT 1;
...
Planning Time: 0.456 ms
Execution Time: 149464.796 ms
Proposal
Add a new index index_users_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