Add add_concurrent_index migration for index_namespaces_on_updated_at
What does this MR do and why?
Addresses the performance issues raised in #392020 (closed) by adding a post migration to create a new index_namespaces_on_updated_at
index with add_concurrent_index
. This was originally implemented as a scheduled asynchronous creation as per the adding database indexes guide, but after assessing the necessary time to create the index was just a few minutes, it was decided that the overhead of asynchronous creation was not worth it.
Screenshots or screen recordings
Migrate:
main: == 20230218145940 AddIndexNamespacesOnUpdatedAt: migrating ====================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1772s
main: -- index_exists?("namespaces", :updated_at, {:name=>"index_namespaces_on_updated_at", :algorithm=>:concurrently})
main: -> 0.0087s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- add_index("namespaces", :updated_at, {:name=>"index_namespaces_on_updated_at", :algorithm=>:concurrently})
main: -> 0.0045s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230218145940 AddIndexNamespacesOnUpdatedAt: migrated (0.2069s) ===========
ci: == 20230218145940 AddIndexNamespacesOnUpdatedAt: migrating ====================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?("namespaces", :updated_at, {:name=>"index_namespaces_on_updated_at", :algorithm=>:concurrently})
ci: -> 0.0139s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- add_index("namespaces", :updated_at, {:name=>"index_namespaces_on_updated_at", :algorithm=>:concurrently})
ci: -> 0.0033s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20230218145940 AddIndexNamespacesOnUpdatedAt: migrated (0.0332s) ===========
Creating index with Ask Joe bot:
Query performance improvements
Before adding the index:
Time: 4.163 min
- planning: 2.044 ms
- execution: 4.163 min
- I/O read: 12.013 min
- I/O write: 878.926 ms
Shared buffers:
- hits: 64 (~512.00 KiB) from the buffer pool
- reads: 973281 (~7.40 GiB) from the OS file cache, including disk I/O
- dirtied: 20068 (~156.80 MiB)
- writes: 19766 (~154.40 MiB)
Postgres.ai explain: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15635/commands/54084
After adding the index:
Time: 2.496 ms
- planning: 2.097 ms
- execution: 0.399 ms
- I/O read: 0.340 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Postgres.ai explain: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15635/commands/54088
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.