Update trial and opensource pipeline_triggers plan limits to 25k

What does this MR do and why?

As seen in #350577 (closed), the limits for pipeline_triggers are not consistent across all plans for GitLab.com. This is because either new plans were introduced or the limits were not migrated when the plan names were updated. This MR adds a migration to update the non-paid plans (opensource and trials) to 25k.

We confirmed in #350577 (comment 1791881087) that customers under the relevant plans are all well under 25k. We also have a confirmation to proceed with this change here.

Partially resolves #350577 (closed).

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.

Query plan

Up

main: == [advisory_lock_connection] object_id: 119340, pg_backend_pid: 19443
main: == 20240226191235 UpdatePipelineTriggersPlanLimits: migrating =================
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("premium_trial")
main:    -> 0.0000s
main: -- quote(25000)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '25000' FROM plans WHERE name = 'premium_trial' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0033s
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("ultimate_trial")
main:    -> 0.0000s
main: -- quote(25000)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '25000' FROM plans WHERE name = 'ultimate_trial' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0004s
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("opensource")
main:    -> 0.0000s
main: -- quote(25000)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '25000' FROM plans WHERE name = 'opensource' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0005s
main: == 20240226191235 UpdatePipelineTriggersPlanLimits: migrated (0.0075s) ========

main: == [advisory_lock_connection] object_id: 119340, pg_backend_pid: 19443
ci: == [advisory_lock_connection] object_id: 119540, pg_backend_pid: 19445
ci: == 20240226191235 UpdatePipelineTriggersPlanLimits: migrating =================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20240226191235 UpdatePipelineTriggersPlanLimits: migrated (0.0078s) ========

ci: == [advisory_lock_connection] object_id: 119540, pg_backend_pid: 19445

Down

main: == [advisory_lock_connection] object_id: 119040, pg_backend_pid: 21586
main: == 20240226191235 UpdatePipelineTriggersPlanLimits: reverting =================
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("premium_trial")
main:    -> 0.0000s
main: -- quote(0)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '0' FROM plans WHERE name = 'premium_trial' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0012s
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("ultimate_trial")
main:    -> 0.0000s
main: -- quote(0)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '0' FROM plans WHERE name = 'ultimate_trial' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0004s
main: -- quote_column_name("pipeline_triggers")
main:    -> 0.0000s
main: -- quote("opensource")
main:    -> 0.0000s
main: -- quote(0)
main:    -> 0.0000s
main: -- execute("INSERT INTO plan_limits (plan_id, \"pipeline_triggers\")\nSELECT id, '0' FROM plans WHERE name = 'opensource' LIMIT 1\nON CONFLICT (plan_id) DO UPDATE SET \"pipeline_triggers\" = EXCLUDED.\"pipeline_triggers\";\n")
main:    -> 0.0003s
main: == 20240226191235 UpdatePipelineTriggersPlanLimits: reverted (0.0056s) ========

main: == [advisory_lock_connection] object_id: 119040, pg_backend_pid: 21586
ci: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 22007
ci: == 20240226191235 UpdatePipelineTriggersPlanLimits: reverting =================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20240226191235 UpdatePipelineTriggersPlanLimits: reverted (0.0093s) ========

ci: == [advisory_lock_connection] object_id: 118980, pg_backend_pid: 22007

Related to #350577 (closed)

changed milestone to %16.10

added Category:Pipeline Composition Hacktoberfest SLOMissed auto closed backend devopsverify feature flagskipped grouppipeline authoring priority3 sectionci severity3 typebug vintage workflowin review labels

assigned to @lma-git

added 1 commit

• ecbe304a - Update pipeline_triggers plan limits to 25k

Compare with previous version

marked this merge request as draft

changed the description

added database databasereview pending documentation labels

	1 Warning
	New migrations added but db/structure.sql wasn't updated Usually, when adding new migrations, db/structure.sql should be updated too (unless the migration isn't changing the DB schema and isn't the most recent one).

	1 Message
	This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge.

Documentation review

The following files require a review from a technical writer:

• doc/user/gitlab_com/index.md (Link to current live version)

The review does not need to block merging this merge request. See the:

• Metadata for the *.md files that you've changed. The first few lines of each *.md file identify the stage and group most closely associated with your docs change.
• The Technical Writer assigned for that stage and group.
• Documentation workflows for information on when to assign a merge request for review.

Reviewer roulette

Category	Reviewer	Maintainer
database	@huzaifaiftikhar1 (UTC+5.5, 13.5 hours ahead of author)	@tigerwnz (UTC+11, 19 hours ahead of author)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

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

requested review from @fabiopitino

mentioned in issue #350577 (closed)

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
20240226191235 - UpdatePipelineTriggersPlanLimits	Regular	1.5 s		+0.00 B

Runtime Histogram for all migrations

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

Migration: 20240226191235 - UpdatePipelineTriggersPlanLimits

• Type: Regular
• Duration: 1.5 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
3	9.2 ms	7.4 ms	3.1 ms	3	INSERT INTO plan_limits (plan_id, "pipeline_triggers") SELECT id, $1 FROM plans WHERE name = $3 LIMIT $4 ON CONFLICT (plan_id) DO UPDATE SET "pipeline_triggers" = EXCLUDED."pipeline_triggers"
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for UpdatePipelineTriggersPlanLimits

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

Background Migration: BackfillOnboardingStatusStepUrl

Sampled 119 batches. Estimated Time to complete: 1 week, 6 hours, and 10 minutes

• Interval: 120s
• Total tuple count: 15677464
• Max batch size: 10000
• Estimated seconds to complete: 627000s
• Estimated number of batches: 5225
• Average batch time: 4.53s
• Batch size: 3000
• N. of batches sampled: 119
• 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
1308	183351.0 ms	320.4 ms	140.2 ms	5252	UPDATE user_details SET onboarding_status = jsonb_build_object($1, onboarding_step_url) WHERE user_details.user_id IN ( SELECT users.id FROM users WHERE users.id BETWEEN $2 AND $3 AND users.id >= $4 AND users.id < $5 AND users.onboarding_in_progress = $6 ) AND onboarding_status -> $7 IS NULL
119	16140.3 ms	219.1 ms	135.6 ms	420	UPDATE user_details SET onboarding_status = jsonb_build_object($1, onboarding_step_url) WHERE user_details.user_id IN ( SELECT users.id FROM users WHERE users.id BETWEEN $2 AND $3 AND users.id >= $4 AND users.onboarding_in_progress = $5 ) AND onboarding_status -> $6 IS NULL
238	52.3 ms	14.1 ms	0.2 ms	238	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
119	24.5 ms	6.8 ms	0.2 ms	119	UPDATE batched_background_migration_jobs SET updated_at = $1, started_at = $2, status = $3, attempts = $4 WHERE batched_background_migration_jobs.id = $5
119	31.7 ms	3.5 ms	0.3 ms	119	UPDATE batched_background_migration_jobs SET updated_at = $1, finished_at = $2, status = $3, metrics = $4 WHERE batched_background_migration_jobs.id = $5
1427	239.3 ms	0.5 ms	0.2 ms	1308	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
238	11.9 ms	0.3 ms	0.1 ms	238	SELECT batched_background_migration_jobs.* FROM batched_background_migration_jobs WHERE batched_background_migration_jobs.id = $1 LIMIT $2
119	11.3 ms	0.2 ms	0.1 ms	119	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)
119	4.7 ms	0.1 ms	0.0 ms	119	SELECT users.id FROM users WHERE users.id BETWEEN $1 AND $2 ORDER BY users.id ASC LIMIT $3
119	4.3 ms	0.1 ms	0.0 ms	119	SELECT batched_background_migrations.* FROM batched_background_migrations WHERE batched_background_migrations.id = $1 LIMIT $2

Histogram of batch runtimes for BackfillOnboardingStatusStepUrl

Batch Runtime	Count
0 seconds - 10 seconds	119
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 BackfillOnboardingStatusStepUrl

Query Runtime	Count
0 seconds - 0.1 seconds	2555
0.1 seconds - 0.5 seconds	1370
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
20240214035028 - AddFirstAndLatestPipelineIdToVulnerabilityOccurrences	Regular	2.0 s		+0.00 B
20240219043439 - CreateRdNamespaceClusterAgentMappingsTable	Regular	2.7 s		+40.00 KiB
20240219143045 - AddRemoveGroupToMemberRoles	Regular	1.5 s		+0.00 B
20240221200754 - ChangeDeploymentApprovalsIndex	Regular	3.3 s		+888.00 KiB
20240226043959 - AddNamespaceFkToNamespaceClusterAgentMappingsTable	Regular	2.0 s		+0.00 B
20240226044028 - AddClusterAgentFkToNamespaceClusterAgentMappingsTable	Regular	1.8 s		+16.00 KiB
20240226044054 - AddCreatorIdFkToNamespaceClusterAgentMappingsTable	Regular	1.9 s		+0.00 B
20240226072310 - PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences	Post deploy	2.4 s		+8.00 KiB [note]
20240226134858 - EnforceVsCodeSettingsUuidPresence	Post deploy	2.0 s		+0.00 B
20240226141402 - ReplaceCiBuildsCiStagesForeignKey	Post deploy	3.5 s		+8.00 KiB [note]
20240226174509 - QueueBackfillOnboardingStatusStepUrl	Post deploy	1.9 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2925173-13010072-main	2024-02-29T18:25:14Z	2024-02-28T13:38:08Z	2024-03-01 06:40:14 +0000
database-testing-2925173-13010072-ci	2024-02-29T18:25:15Z	2024-02-29T05:50:30Z	2024-03-01 06:40:14 +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
20240226191235 - UpdatePipelineTriggersPlanLimits	Regular	2.2 s		+0.00 B

Runtime Histogram for all migrations

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

Migration: 20240226191235 - UpdatePipelineTriggersPlanLimits

• Type: Regular
• Duration: 2.2 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for UpdatePipelineTriggersPlanLimits

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

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20240214035028 - AddFirstAndLatestPipelineIdToVulnerabilityOccurrences	Regular	2.8 s		+0.00 B
20240219143045 - AddRemoveGroupToMemberRoles	Regular	2.4 s		+0.00 B
20240226072310 - PrepareIndexForInitialAndLatestPipelineIdToVulnerabilityOccurrences	Post deploy	3.5 s		+8.00 KiB [note]
20240226134858 - EnforceVsCodeSettingsUuidPresence	Post deploy	2.9 s		+0.00 B
20240226141402 - ReplaceCiBuildsCiStagesForeignKey	Post deploy	4.5 s		+8.00 KiB [note]
20240226174509 - QueueBackfillOnboardingStatusStepUrl	Post deploy	2.3 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2925173-13010072-main	2024-02-29T18:25:14Z	2024-02-28T13:38:08Z	2024-03-01 06:40:14 +0000
database-testing-2925173-13010072-ci	2024-02-29T18:25:15Z	2024-02-29T05:50:30Z	2024-03-01 06:40:14 +0000

Job artifacts

---

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

Hi @fabiopitino could you please confirm if we are okay to proceed with this MR per #350577 (comment 1790082653)? Thanks!

added database-testing-automation label

removed review request for @fabiopitino

added 486 commits

• ecbe304a...f976bd92 - 485 commits from branch master
• ca534cd1 - Update pipeline_triggers plan limits to 25k for trials and opensource

Compare with previous version

marked this merge request as ready

changed title from Draft: Update all pipeline_triggers plan limits to 25k to Update trial and opensource pipeline_triggers plan limits to 25k

changed the description

added 1 commit

• 3d923014 - Update pipeline_triggers plan limits to 25k for trials and opensource

Compare with previous version

Hi @furkanayhan! Could you please do the first database review here? Thank you

requested review from @furkanayhan

approved this merge request

added databasereviewed label and removed databasereview pending label

removed review request for @furkanayhan

added pipeline:mr-approved label

@furkanayhan, thanks for approving this merge request.

This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break master, a new pipeline will be started shortly.

Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 0be6d110

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 35     | 0      | 1       | 0     | 36    | ✅     |
| Create      | 8      | 0      | 3       | 0     | 11    | ✅     |
| Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
| Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
| Plan        | 4      | 0      | 0       | 0     | 4     | ✅     |
| Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 56     | 0      | 5       | 0     | 61    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for 0be6d110

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 147    | 0      | 30      | 3     | 177   | ✅     |
| Create      | 153    | 0      | 19      | 4     | 172   | ✅     |
| Plan        | 8      | 0      | 0       | 0     | 8     | ✅     |
| Govern      | 6      | 0      | 0       | 0     | 6     | ✅     |
| Data Stores | 4      | 0      | 0       | 0     | 4     | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
| Package     | 0      | 0      | 2       | 0     | 2     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 326    | 0      | 51      | 7     | 377   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

requested review from @tigerwnz

removed review request for @tigerwnz

added 248 commits

• 3d923014...8b632ab7 - 246 commits from branch master
• 8893b022 - Update pipeline_triggers plan limits to 25k for trials and opensource
• 0be6d110 - Add spec coverage for migration

Compare with previous version

reset approvals from @furkanayhan by pushing to the branch

requested review from @tigerwnz

removed pipeline:mr-approved label

approved this merge request

added databaseapproved label and removed databasereviewed label

added pipeline:mr-approved label

resolved all threads

enabled an automatic merge when the pipeline for 2f8b1832 succeeds

merged

Hello @lma-git

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. You're welcome to improve it.

mentioned in commit c6aa9d5c

added workflowstaging-canary label and removed workflowin review 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-production label and removed workflowproduction label

mentioned in merge request kubitus-project/kubitus-installer!2869 (merged)

mentioned in merge request !151826 (merged)

added releasedpublished label

added pipelinetier-3 label