Skip to content

Add trigram indexes on email columns for self-managed instances

Zakir Dzhamaliddinov requested to merge gitlab-community/gitlab:20381-add-trigram-indexes-on-email-column into master
  • Please check this box if this contribution uses AI-generated content (including content generated by GitLab Duo features) as outlined in the GitLab DCO & CLA

What does this MR do and why?

This MR adds trigram indexes on email columns for self-managed instances which are needed for partial email search feature, see - !147204 (comment 1895356618)

Migrations

bin/rails db:migrate

Result 
main: == [advisory_lock_connection] object_id: 124500, pg_backend_pid: 28926
main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: migrating =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0405s
main: -- index_exists?(:users, :public_email, {:name=>"index_users_on_public_email_trigram", :using=>:gin, :opclass=>{:public_email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0234s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:users, :public_email, {:name=>"index_users_on_public_email_trigram", :using=>:gin, :opclass=>{:public_email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0437s
main: -- execute("RESET statement_timeout")
main:    -> 0.0012s
main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: migrated (0.1911s) ====

main: == [advisory_lock_connection] object_id: 124500, pg_backend_pid: 28926
main: == [advisory_lock_connection] object_id: 124740, pg_backend_pid: 28928
main: == 20240507162033 AddTrigramIndexOnEmailForUsers: migrating ===================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0014s
main: -- index_exists?(:users, :email, {:name=>"index_users_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0399s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0007s
main: -- add_index(:users, :email, {:name=>"index_users_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0097s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20240507162033 AddTrigramIndexOnEmailForUsers: migrated (0.0767s) ==========


main: == [advisory_lock_connection] object_id: 124740, pg_backend_pid: 28928
main: == [advisory_lock_connection] object_id: 124840, pg_backend_pid: 28930
main: == 20240507162310 AddTrigramIndexOnEmailForEmails: migrating ==================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0012s
main: -- index_exists?(:emails, :email, {:name=>"index_emails_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0115s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0024s
main: -- add_index(:emails, :email, {:name=>"index_emails_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0071s
main: -- execute("RESET statement_timeout")
main:    -> 0.0006s
main: == 20240507162310 AddTrigramIndexOnEmailForEmails: migrated (0.0567s) =========


main: == [advisory_lock_connection] object_id: 124840, pg_backend_pid: 28930

bin/rails db:rollback:main

Result 
main: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 28467
main: == 20240507162310 AddTrigramIndexOnEmailForEmails: reverting ==================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0345s
main: -- indexes(:emails)
main:    -> 0.0080s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:emails, {:algorithm=>:concurrently, :name=>"index_emails_on_email_trigram"})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240507162310 AddTrigramIndexOnEmailForEmails: reverted (0.0730s) =========

main: == 20240507162033 AddTrigramIndexOnEmailForUsers: reverting ===================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- indexes(:users)
main:    -> 0.0141s
main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_email_trigram"})
main:    -> 0.0019s
main: == 20240507162033 AddTrigramIndexOnEmailForUsers: reverted (0.0272s) ==========


main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: reverting =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0007s
main: -- indexes(:users)
main:    -> 0.0112s
main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_public_email_trigram"})
main:    -> 0.0019s
main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: reverted (0.0234s) ====


main: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 28467
Edited by Zakir Dzhamaliddinov

Merge request reports