Remove index_deployments_on_project_id_and_ref async-ly
What does this MR do and why?
This is part of an epic to optimize the deployments table by removing unused indexes: &10185
This MR is an asynchronous removal migration for index_deployments_on_project_id_and_ref
. This index has a usage rate of 0
(see Thanos query).
The definition of this index is:
CREATE INDEX index_deployments_on_project_id_and_ref ON deployments USING btree (project_id, ref);
The sync index removal will be done in a follow-up MR, as part of #402511 (closed).
Screenshots or screen recordings
Migration output
main: == [advisory_lock_connection] object_id: 227240, pg_backend_pid: 55620
main: == 20231002023319 PrepareRemovalIndexDeploymentsOnProjectIdAndRef: migrating ==
main: -- index_exists?(:deployments, [:project_id, :ref], {:name=>"index_deployments_on_project_id_and_ref"})
main: -> 0.0735s
main: == 20231002023319 PrepareRemovalIndexDeploymentsOnProjectIdAndRef: migrated (0.0841s)
main: == [advisory_lock_connection] object_id: 227240, pg_backend_pid: 55620
Testing the async removal
Following this guide:
After running bundle exec rails db:migrate
:
gitlabhq_development=# select * from postgres_async_indexes where name='index_deployments_on_project_id_and_ref';
id | created_at | updated_at | name | definition | table_name | attempts | last_error
----+-------------------------------+-------------------------------+-----------------------------------------+-------------------------------------------------------------------+-------------+----------+------------
8 | 2023-10-02 02:44:14.630832+00 | 2023-10-02 02:44:14.630832+00 | index_deployments_on_project_id_and_ref | DROP INDEX CONCURRENTLY "index_deployments_on_project_id_and_ref" | deployments | 0 |
(1 row)
After running bundle exec rails gitlab:db:reindex
:
gitlabhq_development=# \d index_deployments_on_project_id_and_ref
Did not find any relation named "index_deployments_on_project_id_and_ref".
How to set up and validate locally
You can follow this guide to verify async removal of index.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #402511 (closed)
Edited by Pam Artiaga