Exclude null records from index on `events(group_id)`
What does this MR do?
Replace events index with partial one
This improves the index size by removing all records with group_id
from the index. On GitLab.com this means reducing the size from 9 GB to
8kb (as long as the column stays mostly null).
Relates to https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/32826#note_214690051
Screenshots
abrandl-gl:gitlab/ (ab/partial-events-index✗) $ spring rake db:migrate [12:43:37]
Running via Spring preloader in process 3426
== 20190910103144 ReplaceEventsIndexOnGroupIdWithPartialIndex: migrating ======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:events, :group_id, {:where=>"group_id IS NOT NULL", :name=>"index_events_on_group_id_partial", :algorithm=>:concurrently})
-> 0.0041s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- add_index(:events, :group_id, {:where=>"group_id IS NOT NULL", :name=>"index_events_on_group_id_partial", :algorithm=>:concurrently})
-> 0.0156s
-- execute("RESET ALL")
-> 0.0005s
-- transaction_open?()
-> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
-> 0.0005s
-- indexes(:events)
-> 0.0034s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_on_group_id"})
-> 0.0019s
-- execute("RESET ALL")
-> 0.0003s
== 20190910103144 ReplaceEventsIndexOnGroupIdWithPartialIndex: migrated (0.0273s)
abrandl-gl:gitlab/ (ab/partial-events-index) $ spring rake db:migrate:down VERSION=20190910103144 [12:43:46]
Running via Spring preloader in process 3512
== 20190910103144 ReplaceEventsIndexOnGroupIdWithPartialIndex: reverting ======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:events, :group_id, {:name=>"index_events_on_group_id", :algorithm=>:concurrently})
-> 0.0042s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- add_index(:events, :group_id, {:name=>"index_events_on_group_id", :algorithm=>:concurrently})
-> 0.0105s
-- execute("RESET ALL")
-> 0.0003s
-- transaction_open?()
-> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
-> 0.0005s
-- indexes(:events)
-> 0.0034s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_on_group_id_partial"})
-> 0.0021s
-- execute("RESET ALL")
-> 0.0003s
== 20190910103144 ReplaceEventsIndexOnGroupIdWithPartialIndex: reverted (0.0223s)
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry for user-facing changes, or community contribution. Check the link for other scenarios.
Edited by Andreas Brandl