Prepare indexes for bigint column conversions
What does this MR do?
Prepare indexes for asynchronous creation for tables that need to have columns converted from int
to bigint
. We need to do this for tables which have large indexes on GitLab.com, so deployments can complete in a reasonable time. The indexes should be created as part of the reindexing process that runs every weekend. Async index creation was added in !66478 (merged)
For example, with ci_job_artifacts
we have 5 indexes that contain the id
or job_id
columns, both of which are being converted from int
to bigint
. The migration in this MR schedules those 5 indexes to be created asynchronously, before the migration to swap the columns is merged. Once the swap migration runs, the add_concurrent_index
will be a no-op since the indexes will already exist. On self-managed installations, the indexes will be created as part of the swap migration as normal.
The related MRs for the swaps are: !65601 (merged), !66625 (merged), !66088 (merged)
How to setup and validate locally
Note the migration only inserts a record into postgres_async_indexes
for each index, so runtime is not a concern.
rails db:migrate:up VERSION=20210804151444
== 20210804151444 PrepareIndexesForCiJobArtifactBigintConversion: migrating ===
-- index_exists?(:ci_job_artifacts, :id_convert_to_bigint, {:unique=>true, :name=>:index_ci_job_artifact_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0039s
-- add_index_options(:ci_job_artifacts, :id_convert_to_bigint, {:unique=>true, :name=>:index_ci_job_artifact_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:ci_job_artifacts, [:project_id, :id_convert_to_bigint], {:where=>"file_type = 18", :name=>:index_ci_job_artifacts_for_terraform_reports_bigint, :algorithm=>:concurrently})
-> 0.0030s
-- add_index_options(:ci_job_artifacts, [:project_id, :id_convert_to_bigint], {:where=>"file_type = 18", :name=>:index_ci_job_artifacts_for_terraform_reports_bigint, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:ci_job_artifacts, :id_convert_to_bigint, {:where=>"file_type = 18", :name=>:index_ci_job_artifacts_id_for_terraform_reports_bigint, :algorithm=>:concurrently})
-> 0.0028s
-- add_index_options(:ci_job_artifacts, :id_convert_to_bigint, {:where=>"file_type = 18", :name=>:index_ci_job_artifacts_id_for_terraform_reports_bigint, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:ci_job_artifacts, [:expire_at, :job_id_convert_to_bigint], {:name=>:index_ci_job_artifacts_on_expire_at_and_job_id_bigint, :algorithm=>:concurrently})
-> 0.0026s
-- add_index_options(:ci_job_artifacts, [:expire_at, :job_id_convert_to_bigint], {:name=>:index_ci_job_artifacts_on_expire_at_and_job_id_bigint, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:ci_job_artifacts, [:job_id_convert_to_bigint, :file_type], {:unique=>true, :name=>:index_ci_job_artifacts_on_job_id_and_file_type_bigint, :algorithm=>:concurrently})
-> 0.0027s
-- add_index_options(:ci_job_artifacts, [:job_id_convert_to_bigint, :file_type], {:unique=>true, :name=>:index_ci_job_artifacts_on_job_id_and_file_type_bigint, :algorithm=>:concurrently})
-> 0.0000s
== 20210804151444 PrepareIndexesForCiJobArtifactBigintConversion: migrated (0.0531s)
rails db:migrate:down VERSION=20210804151444
== 20210804151444 PrepareIndexesForCiJobArtifactBigintConversion: reverting ===
== 20210804151444 PrepareIndexesForCiJobArtifactBigintConversion: reverted (0.0183s)
rails db:migrate:up VERSION=20210804153307
== 20210804153307 PrepareIndexesForTaggingBigintConversion: migrating =========
-- index_exists?(:taggings, :id_convert_to_bigint, {:unique=>true, :name=>:index_taggings_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0032s
-- add_index_options(:taggings, :id_convert_to_bigint, {:unique=>true, :name=>:index_taggings_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:taggings, [:taggable_id_convert_to_bigint, :taggable_type], {:name=>:i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type, :algorithm=>:concurrently})
-> 0.0020s
-- add_index_options(:taggings, [:taggable_id_convert_to_bigint, :taggable_type], {:name=>:i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:taggings, [:taggable_id_convert_to_bigint, :taggable_type, :context], {:name=>:i_taggings_on_taggable_bigint_and_taggable_type_and_context, :algorithm=>:concurrently})
-> 0.0019s
-- add_index_options(:taggings, [:taggable_id_convert_to_bigint, :taggable_type, :context], {:name=>:i_taggings_on_taggable_bigint_and_taggable_type_and_context, :algorithm=>:concurrently})
-> 0.0000s
-- index_exists?(:taggings, [:tag_id, :taggable_id_convert_to_bigint, :taggable_type, :context, :tagger_id, :tagger_type], {:unique=>true, :name=>:taggings_idx_tmp, :algorithm=>:concurrently})
-> 0.0018s
-- add_index_options(:taggings, [:tag_id, :taggable_id_convert_to_bigint, :taggable_type, :context, :tagger_id, :tagger_type], {:unique=>true, :name=>:taggings_idx_tmp, :algorithm=>:concurrently})
-> 0.0000s
== 20210804153307 PrepareIndexesForTaggingBigintConversion: migrated (0.0388s)
rails db:migrate:down VERSION=20210804153307
== 20210804153307 PrepareIndexesForTaggingBigintConversion: reverting =========
== 20210804153307 PrepareIndexesForTaggingBigintConversion: reverted (0.0175s)
rails db:migrate:up VERSION=20210804154407
== 20210804154407 PrepareIndexesForCiStageBigintConversion: migrating =========
-- index_exists?(:ci_stages, :id_convert_to_bigint, {:unique=>true, :name=>:index_ci_stages_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0032s
-- add_index_options(:ci_stages, :id_convert_to_bigint, {:unique=>true, :name=>:index_ci_stages_on_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0001s
-- index_exists?(:ci_stages, [:pipeline_id, :id_convert_to_bigint], {:where=>"status in (0, 1, 2, 8, 9, 10)", :name=>:index_ci_stages_on_pipeline_id_and_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0022s
-- add_index_options(:ci_stages, [:pipeline_id, :id_convert_to_bigint], {:where=>"status in (0, 1, 2, 8, 9, 10)", :name=>:index_ci_stages_on_pipeline_id_and_id_convert_to_bigint, :algorithm=>:concurrently})
-> 0.0000s
== 20210804154407 PrepareIndexesForCiStageBigintConversion: migrated (0.0262s)
rails db:migrate:down VERSION=20210804154407
== 20210804154407 PrepareIndexesForCiStageBigintConversion: reverting =========
== 20210804154407 PrepareIndexesForCiStageBigintConversion: reverted (0.0140s)
Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) -
I have tested this MR in all supported browsers, or it's not needed. -
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Merge request reports
Activity
changed milestone to %14.2
added database devopssystems groupdatabase typemaintenance labels
assigned to @pbair
added typefeature label
- A deleted user
added databasereview pending label
2 Warnings 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 tooling, ~"tooling::pipelines", ~"tooling::workflow", 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 Michał Zając ( @Quintasan
) (UTC+2, 6 hours ahead of@pbair
)Steve Abrams ( @sabrams
) (UTC-6, 2 hours behind@pbair
)~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
Dangermarked the checklist item I have included changelog trailers, or none are needed. (Does this MR need a changelog?) as completed
marked the checklist item I have added/updated documentation, or it's not needed. (Is documentation required?) as completed
marked the checklist item I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) as completed
marked the checklist item I have self-reviewed this MR per code review guidelines. as completed
marked the checklist item This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) as completed
marked the checklist item I have followed the style guides. as completed
marked the checklist item This change is backwards compatible across updates, or this does not apply. as completed
marked the checklist item I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) as completed
marked the checklist item I have tested this MR in all supported browsers, or it's not needed. as completed
marked the checklist item I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed. as completed
marked the checklist item I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) as completed
Database migrations
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 20210804151444 - PrepareIndexesForCiJobArtifactBigintConversion Post deploy 1.5 s +0.00 B 20210804153307 - PrepareIndexesForTaggingBigintConversion Post deploy 1.3 s +0.00 B 20210804154407 - PrepareIndexesForCiStageBigintConversion Post deploy 1.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 67 0.1 seconds - 1 second 3 1 second - 5 minutes 0 5 minutes + 0 Migration: 20210804151444 - PrepareIndexesForCiJobArtifactBigintConversion
- Type: Post deploy
- Duration: 1.5 s
- Database size change: +0.00 B
Query Calls Total Time Max Time Mean Time Rows SELECT "feature_gates".*
FROM "feature_gates"
WHERE "feature_gates"."feature_key" = $1 /*application:test*/1 0.8 ms 0.8 ms 0.8 ms 0 Histogram for PrepareIndexesForCiJobArtifactBigintConversion
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 27 0.1 seconds - 1 second 1 1 second - 5 minutes 0 5 minutes + 0 Migration: 20210804153307 - PrepareIndexesForTaggingBigintConversion
- Type: Post deploy
- Duration: 1.3 s
- Database size change: +0.00 B
Histogram for PrepareIndexesForTaggingBigintConversion
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 21 0.1 seconds - 1 second 1 1 second - 5 minutes 0 5 minutes + 0 Migration: 20210804154407 - PrepareIndexesForCiStageBigintConversion
- Type: Post deploy
- Duration: 1.2 s
- Database size change: +0.00 B
Histogram for PrepareIndexesForCiStageBigintConversion
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 19 0.1 seconds - 1 second 1 1 second - 5 minutes 0 5 minutes + 0
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change Clone Details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-726562
2021-08-04 18:13:29 UTC 2021-08-04 16:01:24 UTC 2021-08-05 06:14:38 +0000 Artifacts
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
- A deleted user
added database-testing-automation label
- Resolved by Patrick Bair
@stomlinson Can you take a second look at this? I think this matches the swap MRs but I could've missed something.
We'll wait for maintainer approval once we see if we want to add any more indexes for this weekend.
requested review from @stomlinson
added databaseapproved label and removed databasereview pending label
removed review request for @stomlinson
added Engineering Allocation label
Setting label(s) Category:Database sectionenablement based on groupdatabase.
added Category:Database sectioncore platform labels
- Resolved by Patrick Bair
@sabrams Can you do the database maintainer review? This MR needs a feature flag to be enabled on production or else the records won't be created when the migration runs. So if you approve the changes, I'll take care of merging later when it's ready.
requested review from @sabrams
- Resolved by Steve Abrams
@sabrams, thanks for approving this merge request.Please consider starting a new pipeline if:
- This is the first time the merge request is approved, or
- The merge request is ready to be merged, and there has not been a merge request pipeline in the last 2 hours.
For more info, refer to the guideline.
removed review request for @sabrams
enabled an automatic merge when the pipeline for 4f112f18 succeeds
mentioned in commit c232fae4
added workflowstaging label
added workflowcanary label and removed workflowstaging label
added workflowproduction label and removed workflowcanary label
mentioned in merge request !65601 (merged)
added releasedcandidate label
added releasedpublished label and removed releasedcandidate label
mentioned in merge request kubitus-project/kubitus-installer!123 (merged)
mentioned in merge request !70279 (merged)
removed typefeature label
added devopsdata stores label and removed devopssystems label
added groupdatabase frameworks label and removed groupdatabase [DEPRECATED] label
added pipeline:mr-approved label