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

Hey @zzaakiirr!

Thank you for your contribution to GitLab. Please refer to the contribution documentation for an overview of the process.

When you're ready for a first review, post @gitlab-bot ready. If you know a relevant reviewer(s) (for example, someone that was involved in a related issue), you can also assign them directly with @gitlab-bot ready @user1 @user2.

At any time, if you need help, feel free to post @gitlab-bot help or initiate a mentor session on Discord. Read more on how to get help.

You can make AI-generated contributions to GitLab! If you use AI-generated content please check the box added to the top of your merge request description.

You can comment @gitlab-bot label <label1> <label2> to add labels to your MR. Please see the list of allowed labels in the label command documentation.

This message was generated automatically. You're welcome to improve it.

added Community contribution workflowin dev labels

assigned to @zzaakiirr

changed the description

@gitlab-bot ready @tigerwnz

@gitlab-bot label database

added database label

added workflowready for review label and removed workflowin dev label

requested review from @tigerwnz

@tigerwnz, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? If not, find one or more reviewers and assign to them.
• If you've reviewed it, add the workflowin dev label if these changes need more work before the next review.

This message was generated automatically. You're welcome to improve it.

mentioned in merge request !147204 (merged)

	2 Warnings
	Migration Timestamp Out of Date The following migrations have timestamps that are over three weeks old: db/post_migrate/20240507161859_add_trigram_index_on_public_email_for_users.rb db/post_migrate/20240507162033_add_trigram_index_on_email_for_users.rb db/post_migrate/20240507162310_add_trigram_index_on_email_for_emails.rb Please double check the timestamps and update them if possible. Why does this matter?
	You've made some app changes, but didn't add any tests. That's OK as long as you're refactoring existing code, but please consider adding any of the maintenancepipelines, maintenancerefactor, maintenanceworkflow, documentation, QA labels.

	1 Message
	This merge request adds or changes files that require a review from the Database team.

This merge request requires a database review. To make sure these changes are reviewed, take the following steps:

1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
2. Prepare your MR for database review according to the docs.
3. Assign and mention the database reviewer suggested by Reviewer Roulette.

The following files require a review from the Database team:

• db/post_migrate/20240507161859_add_trigram_index_on_public_email_for_users.rb
• db/post_migrate/20240507162033_add_trigram_index_on_email_for_users.rb
• db/post_migrate/20240507162310_add_trigram_index_on_email_for_emails.rb
• db/schema_migrations/20240507161859
• db/schema_migrations/20240507162033
• db/schema_migrations/20240507162310
• db/docs/emails.yml
• db/docs/users.yml
• db/structure.sql

Reviewer roulette

Category	Reviewer	Maintainer
database	@ck3g (UTC+2)	@tigerwnz (UTC+10)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

Thanks @zzaakiirr, these look correct to me. I didn't realise we'd need a Rubocop exception for this, it looks like we even had one of these indexes previously, but removed it in #429082 (closed).

@krasio what is the latest advice for new indexes on these tables? I understand we want to keep them to a minimum, but we can't really support this feature without a new index(es) (example query).

mentioned in issue gitlab-org/quality/triage-reports#17644 (closed)

changed milestone to %17.0

added Category:Organization Category:User Management admin dashboard backend devopsdata stores grouptenant scale linked-issue pipeline:mr-approved sectioncore platform typefeature labels

removed pipeline:mr-approved label

removed backend label

removed database label

removed admin dashboard label

removed linked-issue label

mentioned in issue #20381 (closed)

added groupauthentication label and removed grouptenant scale label

added pipelinetier-1 label

removed sectioncore platform label

removed devopsdata stores label

added devopsgovern sectionsec labels

added 1 commit

• 4883da72 - Add indexes only for self-managed instances

Compare with previous version

changed title from Add trigram indexes on email columns to Add trigram indexes on email columns for self-managed instances

changed the description

added 1 commit

• 2075a47c - Document missing indexes inconsistencies

Compare with previous version

Started database testing pipeline (limited access). This comment will be updated once the pipeline has finished running.

added database databasereview pending labels

	1 Warning
	You've made some app changes, but didn't add any tests. That's OK as long as you're refactoring existing code, but please consider adding any of the maintenancepipelines, maintenancerefactor, maintenanceworkflow, documentation, QA labels.

Reviewer roulette

Category	Reviewer	Maintainer
database	@ck3g (UTC+2)	@tigerwnz (UTC+10)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

Error: Database testing pipeline failure

Failed pipeline ID: 3310795

The database testing pipeline has failed for pipeline ID 3310795. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

approved this merge request

added pipeline:mr-approved label

changed milestone to %17.1

added databasereviewed label and removed databasereview pending label

requested review from @krasio and removed review request for @tigerwnz

@tigerwnz, thanks for approving this merge request.

This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break master, please start a new pipeline before merging.

Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.

added pipelinetier-3 label and removed pipelinetier-1 label

added 1 commit

• 38e4cc8d - Review fixes - add indexes for JiHu version

Compare with previous version

reset approvals from @tigerwnz by pushing to the branch

Error: Database testing pipeline failure

Failed pipeline ID: 3324825

The database testing pipeline has failed for pipeline ID 3324825. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

Generated by gitlab_quality-test_tooling.

---

Slow tests detected in this merge request. These slow tests might be related to this merge request's changes.

Click to expand

Job	File	Name	Duration	Expected duration
#6975007706	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	64.82 s	< 50.13 s
#6975007889	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	64.96 s	< 50.13 s
#6975007802	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	67.9 s	< 50.13 s
#6975942591	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	65.2 s	< 50.13 s
#6975942515	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	66.59 s	< 50.13 s
#6975942554	spec/features/admin/users/users_spec.rb#L175	Admin::Users GET /admin/users when blocking/unblocking a user shows confirmation and allows blocking and unblocking	67.26 s	< 50.13 s

added rspec:slow test detected label

removed pipeline:mr-approved label

added pipelinetier-1 label and removed pipelinetier-3 label

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 38e4cc8d

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 54     | 0      | 2       | 0     | 56    | ✅     |
| Create      | 125    | 0      | 10      | 0     | 135   | ✅     |
| Govern      | 64     | 0      | 1       | 0     | 65    | ✅     |
| Package     | 19     | 0      | 12      | 0     | 31    | ✅     |
| Verify      | 31     | 0      | 1       | 0     | 32    | ✅     |
| Data Stores | 31     | 0      | 0       | 0     | 31    | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
| Release     | 5      | 0      | 0       | 0     | 5     | ✅     |
| Manage      | 0      | 0      | 1       | 0     | 1     | ➖     |
| Analytics   | 1      | 0      | 1       | 0     | 2     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 338    | 0      | 28      | 0     | 366   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for 38e4cc8d

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Govern      | 129    | 0      | 10      | 0     | 139   | ✅     |
| Create      | 318    | 0      | 36      | 0     | 354   | ✅     |
| Data Stores | 22     | 0      | 0       | 0     | 22    | ✅     |
| Verify      | 10     | 0      | 0       | 0     | 10    | ✅     |
| Plan        | 44     | 0      | 4       | 0     | 48    | ✅     |
| Package     | 6      | 0      | 8       | 0     | 14    | ✅     |
| Release     | 2      | 0      | 0       | 0     | 2     | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 539    | 0      | 58      | 0     | 597   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

approved this merge request

added pipeline:mr-approved label

added pipelinetier-3 label and removed pipelinetier-1 label

approved this merge request

added databaseapproved label and removed databasereviewed label

resolved all threads

enabled an automatic merge when all merge checks for 38e4cc8d pass

merged

@zzaakiirr, how was your code review experience with this merge request? Please tell us how we can continue to iterate and improve:

1. React with a or a on this comment to describe your experience.
2. Create a new comment starting with @gitlab-bot feedback below, and leave any additional feedback you have for us in the comment.

Subscribe to the GitLab Community Newsletter for contributor-focused content and opportunities to level up.

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

Hello @zzaakiirr

The database team is looking for ways to improve the database review process and we would love your help!

If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

@gitlab-org/database-team

And someone will be by shortly!

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

mentioned in commit ebefa8d1

mentioned in incident gitlab-org/quality/engineering-productivity/master-broken-incidents#6654 (closed)

added workflowstaging-canary label and removed workflowready for review label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added releasedcandidate label

added releasedpublished label and removed releasedcandidate label

mentioned in merge request kubitus-project/kubitus-installer!3098 (merged)