Skip to content

Add sync database indexes to events table

Abdul Wadood requested to merge 355831-add-sync-indexes into master

What does this MR do and why?

Create sync indexes for the async counterparts created in !85810 (merged) & !84856 (merged)

Database

Up 
bin/rails db:migrate
WARNING: This version of GitLab depends on gitlab-shell 14.15.0, but you're running 14.14.0. Please update gitlab-shell.
main: == 20230116090630 AddIndexForGroupActivityEvents: migrating ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1169s
main: -- index_exists?(:events, [:group_id, :target_type, :action, :id], {:name=>"index_events_for_group_activity", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0143s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0029s
main: -- add_index(:events, [:group_id, :target_type, :action, :id], {:name=>"index_events_for_group_activity", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0121s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230116090630 AddIndexForGroupActivityEvents: migrated (0.1818s) ==========

main: == 20230116090852 AddIndexForProjectActivityEvents: migrating =================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0020s
main: -- index_exists?(:events, [:project_id, :target_type, :action, :id], {:name=>"index_events_for_project_activity", :algorithm=>:concurrently})
main:    -> 0.0163s
main: -- add_index(:events, [:project_id, :target_type, :action, :id], {:name=>"index_events_for_project_activity", :algorithm=>:concurrently})
main:    -> 0.0055s
main: == 20230116090852 AddIndexForProjectActivityEvents: migrated (0.0364s) ========

main: == 20230116090920 AddIndexToEventsOnGroupIdAndId: migrating ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:events, [:group_id, :id], {:name=>"index_events_on_group_id_and_id", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0387s
main: -- add_index(:events, [:group_id, :id], {:name=>"index_events_on_group_id_and_id", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
main:    -> 0.0037s
main: == 20230116090920 AddIndexToEventsOnGroupIdAndId: migrated (0.0531s) ==========

main: == 20230116091626 AddIndexForEventsFollowedUsers: migrating ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0016s
main: -- index_exists?(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
main:    -> 0.0155s
main: -- add_index(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
main:    -> 0.0031s
main: == 20230116091626 AddIndexForEventsFollowedUsers: migrated (0.0280s) ==========

ci: == 20230116090630 AddIndexForGroupActivityEvents: migrating ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0015s
ci: -- index_exists?(:events, [:group_id, :target_type, :action, :id], {:name=>"index_events_for_group_activity", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0105s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- add_index(:events, [:group_id, :target_type, :action, :id], {:name=>"index_events_for_group_activity", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0086s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20230116090630 AddIndexForGroupActivityEvents: migrated (0.0471s) ==========

ci: == 20230116090852 AddIndexForProjectActivityEvents: migrating =================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0008s
ci: -- index_exists?(:events, [:project_id, :target_type, :action, :id], {:name=>"index_events_for_project_activity", :algorithm=>:concurrently})
ci:    -> 0.0087s
ci: -- add_index(:events, [:project_id, :target_type, :action, :id], {:name=>"index_events_for_project_activity", :algorithm=>:concurrently})
ci:    -> 0.0037s
ci: == 20230116090852 AddIndexForProjectActivityEvents: migrated (0.0344s) ========

ci: == 20230116090920 AddIndexToEventsOnGroupIdAndId: migrating ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
ci: -- index_exists?(:events, [:group_id, :id], {:name=>"index_events_on_group_id_and_id", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0077s
ci: -- add_index(:events, [:group_id, :id], {:name=>"index_events_on_group_id_and_id", :where=>"group_id IS NOT NULL", :algorithm=>:concurrently})
ci:    -> 0.0026s
ci: == 20230116090920 AddIndexToEventsOnGroupIdAndId: migrated (0.0263s) ==========

ci: == 20230116091626 AddIndexForEventsFollowedUsers: migrating ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- index_exists?(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
ci:    -> 0.0065s
ci: -- add_index(:events, [:author_id, :target_type, :action, :id], {:name=>"index_events_for_followed_users", :algorithm=>:concurrently})
ci:    -> 0.0036s
ci: == 20230116091626 AddIndexForEventsFollowedUsers: migrated (0.0232s) ==========
Down 
bin/rails db:rollback:main STEP=4
WARNING: This version of GitLab depends on gitlab-shell 14.15.0, but you're running 14.14.0. Please update gitlab-shell.
main: == 20230116091626 AddIndexForEventsFollowedUsers: reverting ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1452s
main: -- indexes(:events)
main:    -> 0.0207s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0008s
main: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_followed_users"})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230116091626 AddIndexForEventsFollowedUsers: reverted (0.1832s) ==========

main: == 20230116090920 AddIndexToEventsOnGroupIdAndId: reverting ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0012s
main: -- indexes(:events)
main:    -> 0.0094s
main: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_on_group_id_and_id"})
main:    -> 0.0017s
main: == 20230116090920 AddIndexToEventsOnGroupIdAndId: reverted (0.0187s) ==========

main: == 20230116090852 AddIndexForProjectActivityEvents: reverting =================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- indexes(:events)
main:    -> 0.0125s
main: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_project_activity"})
main:    -> 0.0030s
main: == 20230116090852 AddIndexForProjectActivityEvents: reverted (0.0222s) ========

main: == 20230116090630 AddIndexForGroupActivityEvents: reverting ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0007s
main: -- indexes(:events)
main:    -> 0.0115s
main: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_group_activity"})
main:    -> 0.0016s
main: == 20230116090630 AddIndexForGroupActivityEvents: reverted (0.0195s) ==========

bin/rails db:rollback:ci STEP=4
WARNING: This version of GitLab depends on gitlab-shell 14.15.0, but you're running 14.14.0. Please update gitlab-shell.
ci: == 20230116091626 AddIndexForEventsFollowedUsers: reverting ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.1449s
ci: -- indexes(:events)
ci:    -> 0.0149s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0008s
ci: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_followed_users"})
ci:    -> 0.0023s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20230116091626 AddIndexForEventsFollowedUsers: reverted (0.1876s) ==========

ci: == 20230116090920 AddIndexToEventsOnGroupIdAndId: reverting ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0013s
ci: -- indexes(:events)
ci:    -> 0.0160s
ci: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_on_group_id_and_id"})
ci:    -> 0.0020s
ci: == 20230116090920 AddIndexToEventsOnGroupIdAndId: reverted (0.0369s) ==========

ci: == 20230116090852 AddIndexForProjectActivityEvents: reverting =================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0009s
ci: -- indexes(:events)
ci:    -> 0.0127s
ci: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_project_activity"})
ci:    -> 0.0021s
ci: == 20230116090852 AddIndexForProjectActivityEvents: reverted (0.0305s) ========

ci: == 20230116090630 AddIndexForGroupActivityEvents: reverting ===================
ci: -- transaction_open?()
ci:    -> 0.0002s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0010s
ci: -- indexes(:events)
ci:    -> 0.0110s
ci: -- remove_index(:events, {:algorithm=>:concurrently, :name=>"index_events_for_group_activity"})
ci:    -> 0.0018s
ci: == 20230116090630 AddIndexForGroupActivityEvents: reverted (0.0309s) ==========

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #355831 (closed)

Edited by Abdul Wadood

Merge request reports