Add trigram indexes on email columns for self-managed instances

• 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

db/docs/emails.yml

@@ -8,3 +8,7 @@ description: Stores users email records
 introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/commit/29cfd33d949d21d67f3892473c24d4f0a127dfe6
 milestone: '6.6'
 gitlab_schema: gitlab_main_clusterwide
+schema_inconsistencies:
+- type: missing_indexes
+  object_name: index_emails_on_email_trigram
+  introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/152249