Add index for events to efficiently query user contributions [SYNC]

What does this MR do and why?

The index index_events_author_id_group_id_action_target_type_created_at was already created on Gitlab.com async as part of !139928 (merged). This migration creates it sync for self-hosted

Migration output

UP

bin/rails db:migrate:down:main db:migrate:down:ci VERSION=20240112143548
main: == [advisory_lock_connection] object_id: 182600, pg_backend_pid: 8312
main: == [advisory_lock_connection] object_id: 182600, pg_backend_pid: 8312
ci: == [advisory_lock_connection] object_id: 183180, pg_backend_pid: 8570
ci: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: migrating ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0621s
ci: -- index_exists?(:events, [:author_id, :group_id, :action, :target_type, :created_at], {:name=>"index_events_author_id_group_id_action_target_type_created_at", :algorithm=>:concurrently})
ci:    -> 0.0305s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0011s
ci: -- add_index(:events, [:author_id, :group_id, :action, :target_type, :created_at], {:name=>"index_events_author_id_group_id_action_target_type_created_at", :algorithm=>:concurrently})
ci:    -> 0.0067s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0005s
ci: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: migrated (0.2082s)
ci: == [advisory_lock_connection] object_id: 183180, pg_backend_pid: 8570

DOWN

bin/rails db:migrate:down:main db:migrate:down:ci VERSION=20240112143548
main: == [advisory_lock_connection] object_id: 182660, pg_backend_pid: 7573
main: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: reverting ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0540s
main: -- indexes(:events)
main:    -> 0.0131s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_author_id_group_id_action_target_type_created_at"})
main:    -> 0.0051s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: reverted (0.0906s)
main: == [advisory_lock_connection] object_id: 182660, pg_backend_pid: 7573
ci: == [advisory_lock_connection] object_id: 201240, pg_backend_pid: 7832
ci: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: reverting ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0048s
ci: -- indexes(:events)
ci:    -> 0.0346s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0097s
ci: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_author_id_group_id_action_target_type_created_at"})
ci:    -> 0.0318s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0061s
ci: == 20240112143548 AddIndexToEventsAuthorGroupActionTargetType: reverted (0.1859s)
ci: == [advisory_lock_connection] object_id: 201240, pg_backend_pid: 7832

Related to #435524 (closed)

added typefeature label

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 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

@gitlab-bot ready @terrichu

added workflowready for review label and removed workflowin dev label

requested review from @terrichu

@terrichu, 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.

added linked-issue label

	2 Warnings
	featureaddition and featureenhancement merge requests normally have a documentation change. Consider adding a documentation update or confirming the documentation plan with the Technical Writer counterpart. For more information, see: The Handbook page on merge request types. The definition of done documentation.
	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/20240112143548_add_index_to_events_author_group_action_target_type.rb
• db/schema_migrations/20240112143548
• db/post_migrate/20231215130625_schedule_index_to_events_author_group_action_target_type_created_at.rb
• db/structure.sql

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
database	@mwoolf (UTC+0)	@a_akgun (UTC+3)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

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

Generated by Danger

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

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

added featureenhancement groupdatabase labels

added devopsdata stores sectioncore platform labels

changed milestone to %16.9

added database databasereview pending labels

	2 Warnings
	featureaddition and featureenhancement merge requests normally have a documentation change. Consider adding a documentation update or confirming the documentation plan with the Technical Writer counterpart. For more information, see: The Handbook page on merge request types. The definition of done documentation.
	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/20240112143548_add_index_to_events_author_group_action_target_type.rb
• db/schema_migrations/20240112143548
• db/post_migrate/20231215130625_schedule_index_to_events_author_group_action_target_type_created_at.rb
• db/structure.sql

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
database	@mwoolf (UTC+0)	@a_akgun (UTC+3)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

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

Generated by Danger

Database migrations (on the main database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20240112143548 - AddIndexToEventsAuthorGroupActionTargetType	Post deploy	2.8 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20240112143548 - AddIndexToEventsAuthorGroupActionTargetType

• Type: Post deploy
• Duration: 2.8 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	2.7 ms	2.7 ms	2.7 ms	1	SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddIndexToEventsAuthorGroupActionTargetType

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	3
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

No other migrations pending on GitLab.com

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2752967-12516775-main	2024-01-16T18:34:12Z	2024-01-16T10:54:42Z	2024-01-17 06:38:26 +0000
database-testing-2752967-12516775-ci	2024-01-16T18:34:12Z	2024-01-16T16:47:11Z	2024-01-17 06:38:26 +0000

Job artifacts

Database migrations (on the ci database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20240112143548 - AddIndexToEventsAuthorGroupActionTargetType	Post deploy	3.7 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20240112143548 - AddIndexToEventsAuthorGroupActionTargetType

• Type: Post deploy
• Duration: 3.7 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddIndexToEventsAuthorGroupActionTargetType

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

No other migrations pending on GitLab.com

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2752967-12516775-main	2024-01-16T18:34:12Z	2024-01-16T10:54:42Z	2024-01-17 06:38:26 +0000
database-testing-2752967-12516775-ci	2024-01-16T18:34:12Z	2024-01-16T16:47:11Z	2024-01-17 06:38:26 +0000

Job artifacts

---

Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

added database-testing-automation label