Reschedule sent_notifications table backfill

What does this MR do and why?

We repartitioned the sent_notifications table using the sliding_list partition strategy. We already partitioned the table in the past, and this backfill BBM completed successfully in .com

Rescheduling here to backfill the new partitioned table.

References

• MR for the first time we partitioned the table Add partitioned copy for sent_notifications table (!192267 - merged)
• MR for the first time we backfilled the table Backfill partitioned sent_notifications table (!194482 - merged)
• Small fix for initial scheduling of the backfill Remove upper bound on sent_notifications backfill (!196626 - merged)

Query plans - Copied from !194482 (merged)

We will be executing the same queries as we did before, simply inserting in a different table.

Insert issue related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126329

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2073270900 
    AND 2073300900 
    AND "sent_notifications"."id" >= 2073271100 
    AND "sent_notifications"."id" < 2073271600 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'Issue' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  issues.namespace_id 
FROM 
  filtered_relation 
  INNER JOIN issues ON filtered_relation.noteable_id = issues.id ON CONFLICT DO NOTHING

Insert merge request related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126332

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2073270900 
    AND 2073300900 
    AND "sent_notifications"."id" >= 2073271100 
    AND "sent_notifications"."id" < 2073271600 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'MergeRequest' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  projects.project_namespace_id 
FROM 
  filtered_relation 
  INNER JOIN merge_requests ON filtered_relation.noteable_id = merge_requests.id 
  INNER JOIN projects ON projects.id = merge_requests.target_project_id ON CONFLICT DO NOTHING

Insert Epic related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126333

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2073270900 
    AND 2073300900 
    AND "sent_notifications"."id" >= 2073277494 
    AND "sent_notifications"."id" < 2073277993 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'Epic' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  epics.group_id 
FROM 
  filtered_relation 
  INNER JOIN epics ON filtered_relation.noteable_id = epics.id ON CONFLICT DO NOTHING

Insert commit related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126334

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2073270900 
    AND 2073300900 
    AND "sent_notifications"."id" >= 2073277494 
    AND "sent_notifications"."id" < 2073277993 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'Commit' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  projects.project_namespace_id 
FROM 
  filtered_relation 
  INNER JOIN projects ON projects.id = filtered_relation.project_id ON CONFLICT DO NOTHING

Insert project snippet related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126335

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2949320000 
    AND 2949350000 
    AND "sent_notifications"."id" >= 2949321300 
    AND "sent_notifications"."id" < 2949321800 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'ProjectSnippet' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  projects.project_namespace_id 
FROM 
  filtered_relation 
  INNER JOIN snippets ON filtered_relation.noteable_id = snippets.id 
  INNER JOIN projects ON projects.id = snippets.project_id ON CONFLICT DO NOTHING

Insert design management design records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126336

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2949600000 
    AND 2949630000 
    AND "sent_notifications"."id" >= 2949602000 
    AND "sent_notifications"."id" < 2949602500 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'DesignManagement::Design' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  design_management_designs.namespace_id 
FROM 
  filtered_relation 
  INNER JOIN design_management_designs ON filtered_relation.noteable_id = design_management_designs.id ON CONFLICT DO NOTHING

Insert wiki page meta records (could not find a batch with such record, feature recently merged)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41092/commands/126337

WITH relation AS (
  SELECT 
    "sent_notifications".* 
  FROM 
    "sent_notifications" 
  WHERE 
    "sent_notifications"."id" BETWEEN 2949600000 
    AND 2949630000 
    AND "sent_notifications"."id" >= 2949602000 
    AND "sent_notifications"."id" < 2949602500 
  LIMIT 
    500
), filtered_relation AS (
  SELECT 
    * 
  FROM 
    relation 
  WHERE 
    noteable_type = 'WikiPage::Meta' 
  LIMIT 
    500
) -- Insert batch, including the sharding key value
INSERT INTO sent_notifications_7abbf02cb6 (
  id, project_id, noteable_type, noteable_id, 
  recipient_id, commit_id, reply_key, 
  in_reply_to_discussion_id, issue_email_participant_id, 
  created_at, namespace_id
) 
SELECT 
  filtered_relation.id, 
  filtered_relation.project_id, 
  filtered_relation.noteable_type, 
  filtered_relation.noteable_id, 
  filtered_relation.recipient_id, 
  filtered_relation.commit_id, 
  filtered_relation.reply_key, 
  filtered_relation.in_reply_to_discussion_id, 
  filtered_relation.issue_email_participant_id, 
  filtered_relation.created_at, 
  coalesce(
    wiki_page_meta.namespace_id, projects.project_namespace_id
  ) 
FROM 
  filtered_relation 
  INNER JOIN wiki_page_meta ON filtered_relation.noteable_id = wiki_page_meta.id 
  LEFT JOIN projects ON projects.id = wiki_page_meta.project_id ON CONFLICT DO NOTHING

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #514591 (closed)