# Cleanup rows with both namespace_id and project_id populated in notes table ## Summary Following the backfill operation to ensure all rows have at least one sharding key populated, we need to clean up rows where both `namespace_id` and `project_id` are NOT NULL. This will allow us to enforce a stricter constraint where exactly one sharding key should be present per row. ## Background As part of our database sharding strategy, the `notes` table uses triple sharding keys: - `namespace_id`: for group-level objects (epics, group-level issues) - `project_id`: for project-level objects - `organization_id`: for personal snippet notes Currently, we have a constraint ensuring at least one sharding key is present: ```sql num_nonnulls(namespace_id, project_id, organization_id) >= 1 ``` However, some rows have more than one column populated, which is unnecessary and inconsistent with our sharding strategy. ## Problem Statement - Some rows in the `notes` table (for child tables we can make sure they don't have this problem from the start like in `system_note_metadata`) have both `namespace_id` and `project_id` populated - This prevents us from enforcing a cleaner constraint: `num_nonnulls(namespace_id, project_id, organization_id) = 1` - Having both keys populated adds unnecessary data redundancy and because of a first try on the backfill, both values might not match between each other (project.namespace_id vs project.project_namespace_id) ## Proposed Solution 1. **Identify affected rows**: Query all rows where both `namespace_id` and `project_id` are NOT NULL 2. **Determine cleanup strategy**: For rows with both keys populated, decide which key to keep: - Keep `project_id` and set `namespace_id = NULL` for project-level notes - This aligns with the original sharding strategy where project-level objects use `project_id` 3. **Implement batched cleanup**: Use batched background migration to avoid database locks 4. **Update constraint**: After cleanup, change the constraint from `>= 1` to `= 1` ## Implementation Plan ### Phase 1: Analysis - [x] Count rows where both `namespace_id` and `project_id` are NOT NULL - [x] Analyze the distribution and patterns of dual-populated rows - [x] Estimate migration duration based on row count _See the [Phase 1 analysis comment](https://gitlab.com/gitlab-org/gitlab/-/work_items/569520#note_3387689612) for postgres.ai EXPLAIN output and numbers (~2.29B rows to clean up; only ~4 mismatched per 4M sampled, so a straight `SET namespace_id = NULL` is correct)._ ### Phase 2: Cleanup Implementation - [ ] Create batched background migration for `notes` table - [ ] Handle cascading tables (`system_note_metadata`, `award_emoji`) - [ ] Add monitoring and progress tracking ### Phase 3: Constraint Update - [ ] Update model validations to prevent future dual population ([#601435](https://gitlab.com/gitlab-org/gitlab/-/work_items/601435) — must land first) - [ ] Add the `num_nonnulls(namespace_id, project_id, organization_id) = 1` constraint as NOT VALID **before** the backfill. A NOT VALID constraint does not check existing rows, so it is safe to add early; it guards against any new dual-writes slipping in during the (weeks-to-months) backfill window. (Per [!238033#note_3396108631](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/238033#note_3396108631).) - [ ] Run the cleanup BBM ([#601435](https://gitlab.com/gitlab-org/gitlab/-/work_items/601435) deployed first, then [!238033](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/238033)) - [ ] Verify all rows have exactly one sharding key - [ ] VALIDATE the constraint ## Acceptance Criteria - [ ] All rows in `notes` table have exactly one sharding key populated (either `namespace_id` OR `project_id`, not both) - [ ] Cascading tables are properly cleaned up - [ ] Database constraint is updated to enforce single sharding key - [ ] No data loss or corruption during migration - [ ] Migration is reversible if issues arise ## SQL Queries ### Count affected rows ```sql SELECT COUNT(*) FROM notes WHERE namespace_id IS NOT NULL AND project_id IS NOT NULL; ``` ### Sample cleanup query (to be batched) ```sql UPDATE notes SET namespace_id = NULL WHERE namespace_id IS NOT NULL AND project_id IS NOT NULL AND EXISTS ( SELECT 1 FROM projects WHERE projects.id = notes.project_id ); ``` ## Related Issues/MRs - Original discussion: #444222 - Backfill MR: [Link to backfill MR] - Sharding documentation: https://docs.gitlab.com/development/database/not_null_constraints/#not-null-constraints-for-multiple-columns - [#11670](https://gitlab.com/groups/gitlab-org/-/epics/11670) ## Risks and Considerations - **Performance impact**: Need to use batched migrations to minimize database load - **Data consistency**: Ensure we're not breaking any existing queries or application logic - **Rollback plan**: Have a clear rollback strategy if issues are discovered ## Definition of Done - [ ] Background migration completed successfully - [ ] All affected rows cleaned up - [ ] Constraint updated to `num_nonnulls(namespace_id, project_id) = 1` - [ ] Documentation updated - [ ] Monitoring confirms no adverse effects - [ ] Drop temporary index `tmp_index_notes_on_id_with_namespace_and_project` once the `CleanupDualShardingKeysInNotes` BBM is finalized (added by MR https://gitlab.com/gitlab-org/gitlab/-/merge_requests/242613)