Add NOT NULL for sharding key on operations_feature_flags_issues

What does this MR do and why?

Add NOT NULL constraint to operations_feature_flags_issues.project_id column.

• We do not add a validation on the model because we're populating the column using a "BEFORE INSERT OR UPDATE" trigger.
• We have finalized the backfill migration for this column.

The sharding key will be used to determine what Organization the row belongs to.

References

• Organization Isolation and Sharding Keys: https://handbook.gitlab.com/handbook/engineering/architecture/design-documents/organization/isolation/
• Sharding Key (Cells) Development Guide: https://docs.gitlab.com/ee/development/cells/#defining-a-sharding-key-for-all-cell-local-tables
• Related to #493768

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

N/A

How to set up and validate locally

N/A

changed milestone to %17.9

added Category:Organization backend devopstenant scale grouporganizations missed:17.5 missed:17.6 missed:17.7 sectioninfrastructure platforms typefeature workflowready for development labels

assigned to @smaglangit

added pipelinetier-1 label

added database databasereview pending labels

	1 Warning
	You've made some app changes, but didn't add any tests. That's OK as long as you're refactoring existing code, but please consider adding any of the maintenancepipelines, maintenancerefactor, maintenanceworkflow, maintenanceperformance, documentation, QA labels.

Reviewer roulette

Category	Reviewer	Maintainer
database	@bmarjanovic (UTC+1, 7 hours behind author)	@a_akgun (UTC+3, 5 hours behind author)

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

added Data WarehouseImpact Check label

mentioned in issue #493768

changed the description

Database migrations (on the main database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20250109065629 - AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint	Post deploy	7.1 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	0
0.1 seconds - 1 second	5
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20250109065629 - AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint

* Type: Post deploy
* Duration: 7.1 s
* Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	2.6 ms	2.6 ms	2.6 ms	0	ALTER TABLE operations_feature_flags_issues VALIDATE CONSTRAINT check_0e57762955
1	1.7 ms	1.7 ms	1.7 ms	0	ALTER TABLE operations_feature_flags_issues ADD CONSTRAINT check_0e57762955 CHECK ( project_id IS NOT NULL ) NOT VALID
1	1.2 ms	1.2 ms	1.2 ms	1	SELECT c.is_nullable FROM information_schema.columns c WHERE c.table_schema = $1 AND c.table_name = $2 AND c.column_name = $3
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	0
0.1 seconds - 1 second	5
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Background Migration: SkipCompanyOnboardingStep

Sampled 108 batches. Estimated Time to complete: 1 week, 1 day, 12 hours, and 42 minutes

• Interval: 120s
• Max batch size: 10000
• Estimated seconds to complete: 736920s
• Average batch time: 6.0s
• Batch size: 3000
• N. of batches sampled: 108
• N. of failed batches: 0

Time estimation is conservative and based on sampling production data in a test environment. It represents the max time that migration could take. The actual time may differ from this estimation.

Consider changing max_batch_size and interval if this estimate is unacceptable.

Calls	Total Time	Max Time	Mean Time	Rows	Query
1177	5303.4 ms	105.1 ms	4.5 ms	745	UPDATE user_details SET onboarding_status = onboarding_status || jsonb_build_object($1, $2) WHERE user_details.user_id IN ( SELECT users.id FROM users WHERE users.id BETWEEN $3 AND $4 AND users.id >= $5 AND users.id < $6 AND users.onboarding_in_progress = $7 ) AND NOT jsonb_exists(user_details.onboarding_status, $8) AND jsonb_path_exists(user_details.onboarding_status, $9)
108	433.3 ms	23.0 ms	4.0 ms	68	UPDATE user_details SET onboarding_status = onboarding_status || jsonb_build_object($1, $2) WHERE user_details.user_id IN ( SELECT users.id FROM users WHERE users.id BETWEEN $3 AND $4 AND users.id >= $5 AND users.onboarding_in_progress = $6 ) AND NOT jsonb_exists(user_details.onboarding_status, $7) AND jsonb_path_exists(user_details.onboarding_status, $8)
108	15.3 ms	1.7 ms	0.1 ms	108	UPDATE batched_background_migration_jobs SET updated_at = $1, started_at = $2, status = $3, attempts = $4 WHERE batched_background_migration_jobs.id = $5
216	29.0 ms	1.5 ms	0.1 ms	216	INSERT INTO batched_background_migration_job_transition_logs (batched_background_migration_job_id, created_at, updated_at, previous_status, next_status) VALUES ($1, $2, $3, $4, $5) RETURNING id
108	14.6 ms	0.7 ms	0.1 ms	108	UPDATE batched_background_migration_jobs SET updated_at = $1, finished_at = $2, status = $3, metrics = $4 WHERE batched_background_migration_jobs.id = $5
1285	191.6 ms	0.3 ms	0.1 ms	1177	SELECT users.id FROM users WHERE users.id BETWEEN $1 AND $2 AND users.id >= $3 ORDER BY users.id ASC LIMIT $4 OFFSET $5
108	7.0 ms	0.1 ms	0.1 ms	108	SELECT sum(batched_background_migration_jobs.batch_size) FROM batched_background_migration_jobs WHERE batched_background_migration_jobs.batched_background_migration_id = $1 AND batched_background_migration_jobs.status IN ($2)
216	8.4 ms	0.1 ms	0.0 ms	216	SELECT batched_background_migration_jobs.* FROM batched_background_migration_jobs WHERE batched_background_migration_jobs.id = $1 LIMIT $2
108	3.6 ms	0.1 ms	0.0 ms	108	SELECT users.id FROM users WHERE users.id BETWEEN $1 AND $2 ORDER BY users.id ASC LIMIT $3

Histogram of batch runtimes for SkipCompanyOnboardingStep

Batch Runtime	Count
0 seconds - 10 seconds	108
10 seconds - 1 minute	0
1 minute - 2 minutes	0
2 minutes - 3 minutes	0
3 minutes - 5 minutes	0
5 minutes +	0

Histogram across all sampled batches of SkipCompanyOnboardingStep

Query Runtime	Count
0 seconds - 0.1 seconds	0
0.1 seconds - 0.5 seconds	3434
0.5 seconds - 1 second	0
1 second - 2 seconds	0
2 seconds - 5 seconds	0
5 seconds +	0

#### Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20241219144523 - QueueSkipCompanyOnboardingStep	Post deploy	7.4 s		+0.00 B
20241226131806 - FinalizeMigrateOsSbomOccurrencesToComponentsWithoutPrefix	Post deploy	6.1 s		+0.00 B
20241231055516 - CleanupProjectSettingPagesDefaultDomainRedirectRename	Post deploy	6.1 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-4072397-16739560-main	2025-01-10T06:51:33Z	2025-01-09T00:24:24Z	2025-01-10 19:17:34 +0000
database-testing-4072397-16739560-ci	2025-01-10T06:51:33Z	2025-01-09T23:18:58Z	2025-01-10 19:17:34 +0000

Job artifacts

Database migrations (on the ci database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20250109065629 - AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint	Post deploy	10.0 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	0
0.1 seconds - 1 second	5
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20250109065629 - AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint

* Type: Post deploy
* Duration: 10.0 s
* Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	1.5 ms	1.5 ms	1.5 ms	1	SELECT c.is_nullable FROM information_schema.columns c WHERE c.table_schema = $1 AND c.table_name = $2 AND c.column_name = $3
1	1.1 ms	1.1 ms	1.1 ms	0	ALTER TABLE operations_feature_flags_issues ADD CONSTRAINT check_0e57762955 CHECK ( project_id IS NOT NULL ) NOT VALID
1	0.2 ms	0.2 ms	0.2 ms	0	ALTER TABLE operations_feature_flags_issues VALIDATE CONSTRAINT check_0e57762955
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddOperationsFeatureFlagsIssuesProjectIdNotNullConstraint

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	0
0.1 seconds - 1 second	5
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

No other migrations pending on GitLab.com

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-4072397-16739560-main	2025-01-10T06:51:33Z	2025-01-09T00:24:24Z	2025-01-10 19:17:34 +0000
database-testing-4072397-16739560-ci	2025-01-10T06:51:33Z	2025-01-09T23:18:58Z	2025-01-10 19:17:34 +0000

Job artifacts

---

Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

added database-testing-automation label

Hi @shubhamkrai, could you please do the initial review for this? Thank you

marked this merge request as ready

requested review from @shubhamkrai

changed title from Add NOT NULL constraint on operations_feature_flags_issues.project_id to Add NOT NULL for sharding key on operations_feature_flags_issues

approved this merge request

added pipeline:mr-approved label

added pipelinetier-2 label and removed pipelinetier-1 label

Before you set this MR to auto-merge

This merge request will progress on pipeline tiers until it reaches the last tier: pipelinetier-3. We will trigger a new pipeline for each transition to a higher tier.

Before you set this MR to auto-merge, please check the following:

• You are the last maintainer of this merge request
• The latest pipeline for this merge request is pipelinetier-3 (You can find which tier it is in the pipeline name)
• This pipeline is recent enough (created in the last 8 hours)

If all the criteria above apply, please set auto-merge for this merge request.

See pipeline tiers and merging a merge request for more details.

Hello @OmarQunsulGitlab Could you do the final review for this?

requested review from @OmarQunsulGitlab

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 01ff8297

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Create      | 138    | 0      | 20      | 0     | 158   | ✅     |
| Monitor     | 8      | 0      | 12      | 0     | 20    | ✅     |
| Verify      | 50     | 0      | 20      | 0     | 70    | ✅     |
| Plan        | 82     | 0      | 8       | 0     | 90    | ✅     |
| Fulfillment | 2      | 0      | 7       | 0     | 9     | ✅     |
| Govern      | 80     | 0      | 12      | 0     | 92    | ✅     |
| Release     | 5      | 0      | 1       | 0     | 6     | ✅     |
| Data Stores | 33     | 0      | 10      | 0     | 43    | ✅     |
| Package     | 25     | 0      | 13      | 0     | 38    | ✅     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
| Configure   | 0      | 0      | 3       | 0     | 3     | ➖     |
| Manage      | 0      | 0      | 10      | 0     | 10    | ➖     |
| Ai-powered  | 0      | 0      | 2       | 0     | 2     | ➖     |
| Secure      | 4      | 0      | 3       | 0     | 7     | ✅     |
| Growth      | 0      | 0      | 2       | 0     | 2     | ➖     |
| ModelOps    | 0      | 0      | 1       | 0     | 1     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 429    | 0      | 124     | 0     | 553   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-test-on-cng: test report for 01ff8297

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 51     | 0      | 19      | 0     | 70    | ✅     |
| Create      | 143    | 0      | 19      | 0     | 162   | ✅     |
| Package     | 30     | 0      | 14      | 0     | 44    | ✅     |
| Growth      | 0      | 0      | 2       | 0     | 2     | ➖     |
| Monitor     | 8      | 0      | 12      | 0     | 20    | ✅     |
| Govern      | 84     | 0      | 10      | 0     | 94    | ✅     |
| Fulfillment | 2      | 0      | 7       | 0     | 9     | ✅     |
| Data Stores | 33     | 0      | 10      | 0     | 43    | ✅     |
| Plan        | 86     | 0      | 8       | 0     | 94    | ✅     |
| Ai-powered  | 0      | 0      | 2       | 0     | 2     | ➖     |
| Configure   | 0      | 0      | 3       | 0     | 3     | ➖     |
| Secure      | 2      | 0      | 5       | 0     | 7     | ✅     |
| Release     | 5      | 0      | 1       | 0     | 6     | ✅     |
| Manage      | 0      | 0      | 10      | 0     | 10    | ➖     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
| ModelOps    | 0      | 0      | 1       | 0     | 1     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 446    | 0      | 123     | 0     | 569   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

changed the description

approved this merge request

added pipelinetier-3 pipeline:run-e2e-omnibus-once labels and removed pipelinetier-2 label

added databaseapproved label and removed databasereview pending label

enabled automatic add to merge train when checks pass

started a merge train

merged

Hello @smaglangit

The database team is looking for ways to improve the database review process and we would love your help!

If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

@gitlab-org/database-team

And someone will be by shortly!

Thanks for your help!

This message was generated automatically. Improve it or delete it.

mentioned in commit 52e75593

added workflowstaging-canary label and removed workflowready for development label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added workflowpost-deploy-db-staging label and removed workflowproduction label

added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label