Create index asynchronously for system_note_metadata
What does this MR do and why?
Related to #424114 (closed)
system_note_metadata
table's primary key column id
needs to be converted to use bigint
type.
id_convert_to_bigint
is the new column that's been backfilled and uses bigint
. We need to create a new index on the column. The index will be used to support the new primary key (this step is a part of https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#swap-the-columns-release-n--1). The new index take a long time to create and will need to be prepared asynchronously for .com in this MR.
Merge request reports
Activity
changed milestone to %16.10
assigned to @euko
- A deleted user
added databasereview pending label
3 Warnings 4d15308a: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines. 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).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.1 Message This merge request adds or changes files that require a review from the Database team. This merge request requires a database review. To make sure these changes are reviewed, take the following steps:
- Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
- Prepare your MR for database review according to the docs.
- Assign and mention the database reviewer suggested by Reviewer Roulette.
The following files require a review from the Database team:
db/post_migrate/20240109025151_create_index_on_id_convert_to_bigint_for_system_note_metadata_async.rb
db/schema_migrations/20240109025151
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 @cablett
(UTC+13, 4 hours ahead of author)
@jon_jenkins
(UTC-6, 15 hours behind author)
Please check reviewer's status!
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
DangerDatabase 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 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync Post deploy 1.7 s +8.00 KiB [note] Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 3 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: 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
- Type: Post deploy
- Duration: 1.7 s
- Database size change: +8.00 KiB [note]
Calls Total Time Max Time Mean Time Rows Query 1 5.1 ms 5.1 ms 5.1 ms 1 INSERT INTO "postgres_async_indexes" ("created_at", "updated_at", "name", "definition", "table_name") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 3 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 20240102065444 - RemoveMilestoneIdColumnFromVulnerabilities Post deploy 2.0 s -622.51 MiB 20240104101601 - FinalizeBackfillPartitionIdCiPipelineChatData Post deploy 1.4 s +0.00 B 20240104223119 - AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads Post deploy 418.9 s +590.46 MiB 20240108072319 - AddFkToCiBuildTraceMetadataOnPartitionIdAndTraceArtifactId2 Post deploy 2.2 s +0.00 B 20240108072546 - AddFkToCiJobArtifactStatesOnPartitionIdAndJobArtifactId2 Post deploy 1.7 s +0.00 B Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2723977-12441203-main
2024-01-09T03:41:09Z 2024-01-09T03:10:02Z 2024-01-09 15:53:48 +0000 database-testing-2723977-12441203-ci
2024-01-09T03:41:09Z 2024-01-09T00:46:17Z 2024-01-09 15:53:48 +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 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync Post deploy 2.6 s +8.00 KiB [note] Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 3 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: 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
- Type: Post deploy
- Duration: 2.6 s
- Database size change: +8.00 KiB [note]
Calls Total Time Max Time Mean Time Rows Query 1 5.5 ms 5.5 ms 5.5 ms 1 INSERT INTO "postgres_async_indexes" ("created_at", "updated_at", "name", "definition", "table_name") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 3 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 20240102065444 - RemoveMilestoneIdColumnFromVulnerabilities Post deploy 2.9 s -8.00 KiB 20240104101601 - FinalizeBackfillPartitionIdCiPipelineChatData Post deploy 2.6 s +0.00 B 20240104223119 - AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads Post deploy 3.8 s +8.00 KiB [note] 20240108072319 - AddFkToCiBuildTraceMetadataOnPartitionIdAndTraceArtifactId2 Post deploy 2.8 s +0.00 B 20240108072546 - AddFkToCiJobArtifactStatesOnPartitionIdAndJobArtifactId2 Post deploy 2.5 s +0.00 B Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-2723977-12441203-main
2024-01-09T03:41:09Z 2024-01-09T03:10:02Z 2024-01-09 15:53:48 +0000 database-testing-2723977-12441203-ci
2024-01-09T03:41:09Z 2024-01-09T00:46:17Z 2024-01-09 15:53:48 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
mentioned in merge request !141331 (merged)
- Resolved by Jon Jenkins
E2E Test Result Summary
allure-report-publisher
generated test report!e2e-test-on-gdk:
test report for 4d15308aexpand test summary
+------------------------------------------------------------------+ | suites summary | +-------------+--------+--------+---------+-------+-------+--------+ | | passed | failed | skipped | flaky | total | result | +-------------+--------+--------+---------+-------+-------+--------+ | Release | 5 | 0 | 0 | 0 | 5 | ✅ | | Create | 54 | 0 | 7 | 0 | 61 | ✅ | | Monitor | 7 | 0 | 0 | 0 | 7 | ✅ | | Plan | 54 | 0 | 1 | 0 | 55 | ✅ | | Package | 15 | 0 | 1 | 0 | 16 | ✅ | | Govern | 67 | 0 | 0 | 0 | 67 | ✅ | | Verify | 31 | 0 | 0 | 0 | 31 | ✅ | | Data Stores | 23 | 0 | 0 | 0 | 23 | ✅ | | Analytics | 2 | 0 | 0 | 0 | 2 | ✅ | | Manage | 0 | 0 | 1 | 0 | 1 | ➖ | +-------------+--------+--------+---------+-------+-------+--------+ | Total | 258 | 0 | 10 | 0 | 268 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+
e2e-package-and-test:
test report for 4d15308aexpand test summary
+------------------------------------------------------------------+ | suites summary | +-------------+--------+--------+---------+-------+-------+--------+ | | passed | failed | skipped | flaky | total | result | +-------------+--------+--------+---------+-------+-------+--------+ | Plan | 242 | 0 | 13 | 0 | 255 | ✅ | | Create | 141 | 0 | 14 | 2 | 155 | ✅ | | Monitor | 4 | 0 | 0 | 0 | 4 | ✅ | | Govern | 3 | 0 | 0 | 0 | 3 | ✅ | | Data Stores | 2 | 0 | 0 | 0 | 2 | ✅ | | Package | 0 | 0 | 1 | 0 | 1 | ➖ | +-------------+--------+--------+---------+-------+-------+--------+ | Total | 392 | 0 | 28 | 2 | 420 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+
- A deleted user
added database-testing-automation label
- Resolved by Jon Jenkins
Hi @jdrpereira, could you review the MR?
requested review from @jdrpereira
added databasereviewed label and removed databasereview pending label
requested review from @jon_jenkins
- Resolved by Jon Jenkins
@jdrpereira
, 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.
added pipeline:mr-approved label
added databaseapproved label and removed databasereviewed label
@jon_jenkins, did you forget to run a pipeline before you merged this work? Based on our code review process, if the latest pipeline was created more than 4 hours ago, you should:
- Ensure the merge request is not in Draft status.
- Start a pipeline (especially important for Community contribution merge requests).
- Set the merge request to auto-merge.
This is a guideline, not a rule. Please consider replying to this comment for transparency.
This message was generated automatically. You're welcome to improve it.
Hello @euko
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 89446aae
added workflowstaging-canary label and removed workflowin dev label
added workflowcanary label and removed workflowstaging-canary label
added workflowstaging label and removed workflowcanary label
added workflowproduction label and removed workflowstaging label
added releasedcandidate label
mentioned in merge request !141333 (merged)
changed milestone to %16.9
mentioned in merge request kubitus-project/kubitus-installer!2720 (merged)