Skip to content

Set sharding key on todos

Tomasz Skoruparequested to merge
tskorupa/set-sharding-key-columns-on-todos into master

What does this MR do and why?

Addresses #562437

ThisMR accomplishes the following:

  • add concurrent index on organization_id
  • add concurrent foreign key constraint (NOT VALID) on organization_id
  • backfill rows that contain num_nonnulls( project_id, group_id ) > 1
  • backfill rows that contain num_nonnulls( project_id, group_id ) < 1
  • modify the rails model to prefer project_id over group_id when both are set
  • modify the rails model to set organization_id when project and group are nil (i.e parentless?)
  • set NOT NULL (NOT VALID) on sharding key
Dependencies
  • verify the index on organization_id has been created on gitlab.com following !204713 (merged)
SQL queries
Batch background migration
SELECT "todos"."id" FROM "todos" WHERE "todos"."id" BETWEEN 73 AND 77 AND "todos"."id" >= 73 ORDER BY "todos"."id" ASC LIMIT 1 OFFSET 1

Explain: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/43850/commands/134391

UPDATE "todos" SET "group_id" = NULL WHERE "todos"."id" BETWEEN 1000000 AND 1100000 AND "todos"."id" >= 1000000 AND "todos"."id" < 1100000 AND "todos"."group_id" IS NOT NULL AND "todos"."project_id" IS NOT NULL

Explain: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/43850/commands/134495

UPDATE todos SET organization_id = users.organization_id
FROM users
WHERE todos.user_id = users.id
AND todos.group_id IS NULL
AND todos.project_id IS NULL
AND todos.id IN (SELECT "todos"."id" FROM "todos" WHERE "todos"."id" BETWEEN 1000000 AND 1100000 AND "todos"."id" >= 1000000 AND "todos"."id" < 1100000)

Explain: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/43850/commands/134496

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.

Edited by Tomasz Skorupa

Merge request reports