Skip to content

Index issues on sent_notifications

Felipe Cardozo requested to merge issue_11391_2 into master

What does this MR do?

This is a follow up of !25640 (merged) which updates sent notifications when moving service desk issues.

That merge request is not working properly in production because it is timing with the following query:

UPDATE "sent_notifications"
SET    "project_id" = $1,
       "noteable_id" = $2
WHERE  "sent_notifications"."noteable_id" = $3
       AND "sent_notifications"."noteable_type" = $1946728 

More information: https://sentry.gitlab.net/gitlab/gitlabcom/issues/1376829/events/25792837/

Here we add an index to sent_notifications to prevent the timeout.

Migration output

== 20200311192012 AddIssueIndexToSentNotifications: migrating =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:sent_notifications, [:noteable_type, :noteable_id], {:name=>"index_sent_notifications_on_noteable_type_noteable_id", :where=>"noteable_type = 'Issue'", :algorithm=>:concurrently})
   -> 0.0021s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:sent_notifications, [:noteable_type, :noteable_id], {:name=>"index_sent_notifications_on_noteable_type_noteable_id", :where=>"noteable_type = 'Issue'", :algorithm=>:concurrently})
   -> 0.0080s
-- execute("RESET ALL")
   -> 0.0006s
== 20200311192012 AddIssueIndexToSentNotifications: migrated (0.0118s) ========

Warning

Sent notifications is a huge table with 318067380 records. Is it safe to add this index to production?

Additional information:

Edited by Justin Farris

Merge request reports

Loading