Skip to content

Add add_concurrent_index migration for index_namespaces_on_updated_at

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

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

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.

Edited by Ruby Nealon

Merge request reports