Skip to content

Add NOT NULL constraint to notes sharding key

As part of &12795 we need to add sharding keys to all our tables. notes already has project_id from way-back, however sharding keys are required to be NOT NULL so we will need to embark on the multi-release process of migrating that column, as outlined here: https://docs.gitlab.com/ee/development/database/not_null_constraints.html#add-a-not-null-constraint-to-an-existing-column

This will delay completion of adding sharding keys to tables that want to backfill their own sharding keys from notes.

Order of Work

  • Delete abuse_notes records

  • Add (num_nonnulls(namespace_id, organization_id, project_id) >= 1) NOT VALID constraint

  • Backfill sharding key

  • Validate (num_nonnulls(namespace_id, organization_id, project_id) >= 1) constraint

  • Set (namespace_id, organization_id, project_id) as the sharding key for notes

  • 1. Check which of the noteable types have project id null SELECT DISTINCT "notes"."noteable_type" FROM "notes" WHERE "notes"."project_id" IS NULL - if someone has access, it would be nice to check for me, otherwise I will create an access request and connect through postgres (https://docs.gitlab.com/development/database/database_lab/#access-the-console-with-psql)

  • 2. Create a backfill migration that handles these noteable types similar to (!163687 (merged))

Edited by Mario Celi