Add add_concurrent_index migration for index_users_on_updated_at
What does this MR do and why?
Addresses the performance issues raised in #392019 (closed) by adding a post migration to create a new index_users_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: == 20230218145930 AddIndexUsersOnUpdatedAt: migrating =========================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1349s
main: -- index_exists?("users", :updated_at, {:name=>"index_users_on_updated_at", :algorithm=>:concurrently})
main: -> 0.0311s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index("users", :updated_at, {:name=>"index_users_on_updated_at", :algorithm=>:concurrently})
main: -> 0.0093s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
main: == 20230218145930 AddIndexUsersOnUpdatedAt: migrated (0.1970s) ================
ci: == 20230218145930 AddIndexUsersOnUpdatedAt: migrating =========================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0008s
ci: -- index_exists?("users", :updated_at, {:name=>"index_users_on_updated_at", :algorithm=>:concurrently})
ci: -> 0.0124s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- add_index("users", :updated_at, {:name=>"index_users_on_updated_at", :algorithm=>:concurrently})
ci: -> 0.0045s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0002s
ci: == 20230218145930 AddIndexUsersOnUpdatedAt: migrated (0.0326s) ================
Creating index with Ask Joe bot:
Query performance improvements
Before adding the index:
Time: 3.252 min
- planning: 2.717 ms
- execution: 3.252 min
- I/O read: 9.500 min
- I/O write: 1.057 s
Shared buffers:
- hits: 64 (~512.00 KiB) from the buffer pool
- reads: 798284 (~6.10 GiB) from the OS file cache, including disk I/O
- dirtied: 54979 (~429.50 MiB)
- writes: 25031 (~195.60 MiB)
Postgres.ai explain: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15635/commands/54083
After adding the index:
Time: 4.145 ms
- planning: 3.693 ms
- execution: 0.452 ms
- I/O read: 0.333 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 0 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/54087
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.