Skip to content

Exclude null records from index on `events(group_id)`

Andreas Brandl requested to merge ab/partial-events-index into master

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

Merge request reports