Backfill for Sharding Key on CI::BuildTraceMetadata
What does this MR do and why?
Add desired sharding key to Ci::BuildTraceMetadata (table p_ci_build_trace_metadata)
- already had association that could reference sharding key:
build_id - this table is partitioned and the new column is indexed accordingly
- Check of production db shows no records with null
build_id
https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/34208/commands/105254
This table has a desired sharding key configured (view configuration).
This merge request is the first step towards transforming the desired sharding key into a sharding key.
This involves 4 changes:
- Adding a new column that will serve as the sharding key (along with the relevant index and foreign key). ==>
:project_id - Populating the sharding key when new records are created by adding a database function and trigger. (using a before_validation on: :create on the actual model)
- indexing project_id on
p_ci_build_trace_metadata - Scheduling a batched background migration to set the sharding key for existing records.
Once the background migration has completed, a second merge request will be created to finalize the background migration and validate the not null constraint.
References
Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
| Before | After |
|---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Related to #458479 (closed)