Skip to content

Add add_concurrent_index migration for index_users_on_updated_at

Ruby Nealon requested to merge ruby/392021-users-updated-at-index into master

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: Screenshot_2023-02-19_at_17.46.25

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.

Edited by Ruby Nealon

Merge request reports