Backfill award_emoji sharding key

Mario Celirequested to merge
514604-backfill-award-emoji-table into master

What does this MR do and why?

Backfill award_emoji sharding key.

Setting namespace_id or organization_id depending on record type. Organization_id is only set for records related to personal snippets (might be related through the notes table)

We never update records on this table, so it's safe to add the num_nonnulls constraint. Evidence of no updates in PG stats (internal only)

awardable_type list

awardable types in .com 
select distinct(awardable_type) from award_emoji;
 awardable_type
----------------
 Epic
 Issue
 MergeRequest
 Note
 Snippet
(5 rows)

Irreversible migrations (DELETE) details

Recover data

The migration will insert every deleted records into the award_emoji_archived table. If needed, those records can be moved back into the award_emoji table.

Impact on user experience if records go missing

award_emoji holds every emoji that has been awarded by any user to an Issue, MR, Note, Epic or Snippet. If records go missing, award emoji won't be displayed in the associated records

Query plans

Update Issue related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139189

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 1 
    AND 10487 
    AND "award_emoji"."id" >= 1 
    AND "award_emoji"."id" < 113 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'Issue' 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "issues"."namespace_id" 
FROM 
  filtered_relation 
  INNER JOIN "issues" ON "issues"."id" = filtered_relation."awardable_id" 
WHERE 
  "award_emoji"."id" = filtered_relation."id"
MergeRequest related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139191

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 1 
    AND 10487 
    AND "award_emoji"."id" >= 1 
    AND "award_emoji"."id" < 113 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'MergeRequest' 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "projects"."project_namespace_id" 
FROM 
  filtered_relation 
  INNER JOIN "merge_requests" ON "merge_requests"."id" = filtered_relation."awardable_id" 
  INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" 
WHERE 
  "award_emoji"."id" = filtered_relation."id"
Epic related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139192

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 991887 
    AND 1003180 
    AND "award_emoji"."id" >= 991887 
    AND "award_emoji"."id" < 991997 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'Epic' 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "epics"."group_id" 
FROM 
  filtered_relation 
  INNER JOIN "epics" ON "epics"."id" = filtered_relation."awardable_id" 
WHERE 
  "award_emoji"."id" = filtered_relation."id"
Snippet related records

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139193

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 5044965 
    AND 5062565 
    AND "award_emoji"."id" >= 5044965 
    AND "award_emoji"."id" < 5045069 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'Snippet' 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "projects"."project_namespace_id", 
  "organization_id" = "snippets"."organization_id" 
FROM 
  filtered_relation 
  INNER JOIN "snippets" ON "snippets"."id" = filtered_relation."awardable_id" 
  LEFT JOIN "projects" ON "projects"."id" = "snippets"."project_id" 
WHERE 
  "award_emoji"."id" = filtered_relation."id"
~~Note related records~~ (legacy query without case)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139194

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 5044965 
    AND 5062565 
    AND "award_emoji"."id" >= 5044965 
    AND "award_emoji"."id" < 5045069 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'Note' 
  LIMIT 
    100
), relation_with_sk AS MATERIALIZED (
  SELECT 
    "filtered_relation"."id", 
    "notes"."organization_id", 
    COALESCE(
      "projects"."project_namespace_id", 
      "notes"."namespace_id"
    ) AS namespace_id 
  FROM 
    "filtered_relation" 
    INNER JOIN "notes" ON "notes"."id" = "filtered_relation"."awardable_id" 
    LEFT JOIN "projects" ON "projects"."id" = "notes"."project_id" 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "relation_with_sk"."namespace_id", 
  "organization_id" = "relation_with_sk"."organization_id" 
FROM 
  "relation_with_sk" 
WHERE 
  "award_emoji"."id" = "relation_with_sk"."id"
Update notes related records with case statement

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45612/commands/139792

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 5044965 
    AND 5062565 
    AND "award_emoji"."id" >= 5044965 
    AND "award_emoji"."id" < 5045069 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "awardable_type" = 'Note' 
  LIMIT 
    100
), relation_with_sk AS MATERIALIZED (
  SELECT 
    "filtered_relation"."id", 
    COALESCE(
      "projects"."project_namespace_id", 
      "notes"."namespace_id"
    ) AS namespace_id, 
    CASE WHEN num_nonnulls(
      "notes"."project_id", "notes"."namespace_id"
    ) >= 1 THEN NULL ELSE "notes"."organization_id" END AS organization_id 
  FROM 
    "filtered_relation" 
    INNER JOIN "notes" ON "notes"."id" = "filtered_relation"."awardable_id" 
    LEFT JOIN "projects" ON "projects"."id" = "notes"."project_id" 
  LIMIT 
    100
) 
UPDATE 
  "award_emoji" 
SET 
  "namespace_id" = "relation_with_sk"."namespace_id", 
  "organization_id" = "relation_with_sk"."organization_id" 
FROM 
  "relation_with_sk" 
WHERE 
  "award_emoji"."id" = "relation_with_sk"."id"
DELETE and archive award_emoji without sharding key

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45408/commands/139199

In the plans above I backfilled 40 note related records in the same batch, that's why in here we delete and archive 60 records

WITH relation AS MATERIALIZED (
  SELECT 
    "award_emoji".* 
  FROM 
    "award_emoji" 
  WHERE 
    "award_emoji"."id" BETWEEN 5044965 
    AND 5062565 
    AND "award_emoji"."id" >= 5044965 
    AND "award_emoji"."id" < 5045069 
  LIMIT 
    100
), filtered_relation AS MATERIALIZED (
  SELECT 
    * 
  from 
    relation 
  WHERE 
    "namespace_id" IS NULL 
    AND "organization_id" IS NULL 
  LIMIT 
    100
), deleted_emoji AS MATERIALIZED (
  DELETE FROM 
    "award_emoji" 
  WHERE 
    "id" IN (
      SELECT 
        "id" 
      FROM 
        filtered_relation
    ) RETURNING id, 
    name, 
    user_id, 
    awardable_id, 
    awardable_type, 
    created_at, 
    updated_at, 
    namespace_id, 
    organization_id
) INSERT INTO award_emoji_archived (
  id, name, user_id, awardable_id, awardable_type, 
  created_at, updated_at, namespace_id, 
  organization_id
) 
SELECT 
  id, 
  name, 
  user_id, 
  awardable_id, 
  awardable_type, 
  created_at, 
  updated_at, 
  namespace_id, 
  organization_id 
FROM 
  deleted_emoji

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 #514604 (closed)

Edited by Mario Celi

Merge request reports