Swap primary key with bigint column for ci_pipeline_variables
What does this MR do and why?
Swap the integer primary key with bigint column for ci_pipeline_variables
See the second step at https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#swap-the-columns-release-n--1
The first step !118878 (merged) is merged and executed on production, see #408936 (comment 1391375272)
The unique index index_on_ci_pipeline_variables_on_id_convert_to_bigint
is created by !120946 (merged) (async prepare) and !120950 (merged) (concurrent create)
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #408936 (closed)
Merge request reports
Activity
changed milestone to %16.1
assigned to @tianwenchen
removed workflowin dev label
- A deleted user
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, documentation, QA labels.Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer database Dominic Bauer (
@bauerdominic
) (UTC+2, 6 hours behind@tianwenchen
)Pavel Shutsin (
@pshutsin
) (UTC+2, 6 hours behind@tianwenchen
)~"migration" No reviewer available No maintainer available To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
Dangeradded 1 commit
- d72902bf - Swap primary key with bigint column for ci_pipeline_variables
- A deleted user
added Data WarehouseImpact Check label
- Resolved by Krasimir Angelov
marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed
- Resolved by Tianwen Chen
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 20230516070036 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.9 s +0.00 B Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 14 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: 20230516070036 - SwapCiPipelineVariablesPkWithBigint
- Type: Post deploy
- Duration: 3.9 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 1 32.8 ms 32.8 ms 32.8 ms 0 CREATE UNIQUE INDEX CONCURRENTLY "index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables" ("id_convert_to_bigint")
1 18.4 ms 18.4 ms 18.4 ms 0 ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
1 17.2 ms 17.2 ms 17.2 ms 0 ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
1 16.5 ms 16.5 ms 16.5 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
1 13.4 ms 13.4 ms 13.4 ms 0 ALTER FUNCTION "trigger_023e82d8e257" RE
SET ALL1 11.5 ms 11.5 ms 11.5 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
1 5.3 ms 5.3 ms 5.3 ms 0 ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
1 0.3 ms 0.3 ms 0.3 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
1 0.3 ms 0.3 ms 0.3 ms 0 ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
1 0.1 ms 0.1 ms 0.1 ms 0 LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SwapCiPipelineVariablesPkWithBigint
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 14 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
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-1940853-10056159-main
2023-05-16T06:56:14Z 2023-05-16T01:10:51Z 2023-05-16 21:33:17 +0000 database-testing-1940853-10056159-ci
2023-05-16T06:56:15Z 2023-05-16T04:46:05Z 2023-05-16 21:33:17 +0000 Database migrations (on the ci database)
3 Warnings 20230516070036 - SwapCiPipelineVariablesPkWithBigint had a query that exceeded timing
guidelines. Run time should not exceed 100ms, but it was 9124336.47ms. Please consider possible
options to improve the query performance.CREATE UNIQUE INDEX CONCURRENTLY
"index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables"
("id_convert_to_bigint")20230516070036 - SwapCiPipelineVariablesPkWithBigint took 152.35min. Please add a comment
that mentions Release Managers (@gitlab-org/release/managers
) so they are informed.20230516070036 - SwapCiPipelineVariablesPkWithBigint may need a batched background migration
to comply with timing guidelines. It took 152.35min, but should not exceed 10.0minMigrations 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 20230516070036 - SwapCiPipelineVariablesPkWithBigint Post deploy 9142.0 s -26.42 GiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 1 Migration: 20230516070036 - SwapCiPipelineVariablesPkWithBigint- Type: Post deploy
- Duration: 9142.0 s
- Database size change: -26.42 GiB
Calls Total Time Max Time Mean Time Rows Query 1 9124336.5 ms 9124336.5 ms 9124336.5 ms 0 CREATE UNIQUE INDEX CONCURRENTLY "index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables" ("id_convert_to_bigint")
1 33.9 ms 33.9 ms 33.9 ms 0 LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
1 27.1 ms 27.1 ms 27.1 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
1 25.4 ms 25.4 ms 25.4 ms 0 ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
1 23.8 ms 23.8 ms 23.8 ms 0 ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
1 18.9 ms 18.9 ms 18.9 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
1 13.6 ms 13.6 ms 13.6 ms 0 ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
1 9.9 ms 9.9 ms 9.9 ms 0 ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
1 8.7 ms 8.7 ms 8.7 ms 0 ALTER FUNCTION "trigger_023e82d8e257" RE
SET ALL1 6.1 ms 6.1 ms 6.1 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SwapCiPipelineVariablesPkWithBigint
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 1 Other information
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-1940853-10056159-main
2023-05-16T06:56:14Z 2023-05-16T01:10:51Z 2023-05-16 21:33:17 +0000 database-testing-1940853-10056159-ci
2023-05-16T06:56:15Z 2023-05-16T04:46:05Z 2023-05-16 21:33:17 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
- Resolved by Krasimir Angelov
mentioned in issue #408936 (closed)
requested review from @ghavenga
mentioned in epic &7522
- A deleted user
added database-testing-automation label
removed review request for @ghavenga
- Resolved by Tianwen Chen
mentioned in commit 17740a6d
mentioned in merge request !120946 (merged)
mentioned in commit a144271d
mentioned in merge request !120950 (merged)
mentioned in commit 1fc81daf
added 1 commit
- dbb391b3 - Swap primary key with bigint column for ci_pipeline_variables
mentioned in commit 372f3224
mentioned in commit efbd5e10
added 3910 commits
-
dbb391b3...5bfdefc2 - 3909 commits from branch
master
- 378cee1d - Swap primary key with bigint column for ci_pipeline_variables
-
dbb391b3...5bfdefc2 - 3909 commits from branch
added sectionci label and removed sectionops label
mentioned in commit 1d9c5c9a
mentioned in commit 99b7ecef
added 1 commit
- 80c409c3 - Swap primary key with bigint column for ci_pipeline_variables
Allure report
allure-report-publisher
generated test report!e2e-test-on-gdk:
test report for 3c6e3638expand test summary
+-----------------------------------------------------------------------+ | suites summary | +------------------+--------+--------+---------+-------+-------+--------+ | | passed | failed | skipped | flaky | total | result | +------------------+--------+--------+---------+-------+-------+--------+ | Create | 8 | 0 | 1 | 6 | 9 | ❗ | | Manage | 1 | 0 | 0 | 0 | 1 | ✅ | | Monitor | 4 | 0 | 0 | 4 | 4 | ❗ | | Plan | 4 | 0 | 0 | 3 | 4 | ❗ | | Data Stores | 2 | 0 | 0 | 1 | 2 | ❗ | | Framework sanity | 0 | 0 | 1 | 0 | 1 | ➖ | | Govern | 2 | 0 | 0 | 0 | 2 | ✅ | +------------------+--------+--------+---------+-------+-------+--------+ | Total | 21 | 0 | 2 | 14 | 23 | ❗ | +------------------+--------+--------+---------+-------+-------+--------+
requested review from @mfanGitLab
- Resolved by Krasimir Angelov
@mfanGitLab could you help to do the backend this MR? Cheers!
requested review from @ghavenga
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 20230519023720 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.2 s -8.00 KiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 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: 20230519023720 - SwapCiPipelineVariablesPkWithBigint
- Type: Post deploy
- Duration: 3.2 s
- Database size change: -8.00 KiB
Calls Total Time Max Time Mean Time Rows Query 1 14.8 ms 14.8 ms 14.8 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
1 9.4 ms 9.4 ms 9.4 ms 0 ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
1 8.7 ms 8.7 ms 8.7 ms 0 ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
1 5.8 ms 5.8 ms 5.8 ms 0 ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
1 5.3 ms 5.3 ms 5.3 ms 0 ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
1 4.3 ms 4.3 ms 4.3 ms 0 ALTER FUNCTION "trigger_023e82d8e257" RE
SET ALL1 3.0 ms 3.0 ms 3.0 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
1 2.7 ms 2.7 ms 2.7 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
1 1.0 ms 1.0 ms 1.0 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
1 0.4 ms 0.4 ms 0.4 ms 0 LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SwapCiPipelineVariablesPkWithBigint
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 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
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2035936-10370091-main
2023-06-19T02:37:50Z 2023-06-19T00:11:02Z 2023-06-19 14:43:28 +0000 database-testing-2035936-10370091-ci
2023-06-19T02:37:50Z 2023-06-19T00:45:54Z 2023-06-19 14:43:28 +0000 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 20230519023720 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.7 s -19.63 GiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 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: 20230519023720 - SwapCiPipelineVariablesPkWithBigint
- Type: Post deploy
- Duration: 3.7 s
- Database size change: -19.63 GiB
Calls Total Time Max Time Mean Time Rows Query 1 4.0 ms 4.0 ms 4.0 ms 0 ALTER FUNCTION "trigger_023e82d8e257" RE
SET ALL1 3.9 ms 3.9 ms 3.9 ms 0 ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
1 2.7 ms 2.7 ms 2.7 ms 0 ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
1 2.5 ms 2.5 ms 2.5 ms 0 ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
1 2.0 ms 2.0 ms 2.0 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
1 1.8 ms 1.8 ms 1.8 ms 0 ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
1 1.4 ms 1.4 ms 1.4 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
1 1.2 ms 1.2 ms 1.2 ms 0 ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
1 0.9 ms 0.9 ms 0.9 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
1 0.2 ms 0.2 ms 0.2 ms 0 LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SwapCiPipelineVariablesPkWithBigint
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 13 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
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2035936-10370091-main
2023-06-19T02:37:50Z 2023-06-19T00:11:02Z 2023-06-19 14:43:28 +0000 database-testing-2035936-10370091-ci
2023-06-19T02:37:50Z 2023-06-19T00:45:54Z 2023-06-19 14:43:28 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
mentioned in issue gitlab-org/database-team/team-tasks#353 (closed)
- Resolved by Krasimir Angelov
removed review request for @mfanGitLab
added 1 commit
- ddfbfe8e - Ensure to recreate the index for bigint column when stepping down
added 1 commit
- 3c6e3638 - Ensure to recreate the index for bigint column when stepping down
@ghavenga
, thanks for approving this merge request.This is the first time the merge request is approved. To ensure full test coverage, a new pipeline will be started shortly.
For more info, please refer to the following links: