Migrate data to system_access_group_microsoft_applications
What does this MR do and why?
Migrates data from SystemAccess::MicrosoftApplication
to SystemAccess::GroupMicrosoftApplication
. This is to enable the table to be split between instance application records for instance-wide SAML sync, and per-group application records for group-based SAML sync. This way we can shard the data appropriately for Cells.
- Adds a migration to copy all group-SAML records to the new table
- Updates logic to write changes to both tables during the transition
- Adds feature flag to control which table is read from by application controllers and SAML sync workers
- Adds specs to ensure the new and old tables are compatible in all call-sites
During the transition to using the new table, data will be written to both tables so that we can seamlessly enable and disable the feature flag without disruption for users.
References
Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.
- original issue: #495499 (closed)
- also implements: #495500 (closed)
- rollout for feature flag: #512497
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
No user-visible changes
How to set up and validate locally
Before switching to this branch:
- Ensure you have a SAML provider configured in gitlab.yml , and also the
group_saml
provider enabled. You may need to be simulating a SaaS instance - Go to https://gdk.test:3443/admin/application_settings/general and set up the Azure Integration under "Microsoft Azure Integration" (you do not have to use real values for client-xid and token)
- Go to the group settings page for a group with SAML SSO configured, for example https://gdk.test:3443/groups/atevans-sso/-/saml and set up the data under "Microsoft Azure Integration"
- Ensure the data is populated in the
system_access_microsoft_applications
table
Switch to this branch, and then:
- Run migrations
- Ensure the group saml entry in
system_access_microsoft_applications
is copied tosystem_access_group_microsoft_applications
- On the Rails console, load the group and validate that the MicrosoftApplication is reachable:
group = Group.find_by_path('atevans-sso') group.system_access_microsoft_application
- Update the data for the group's Azure integration, changing the
client-xid
orgraph_endpoint
values - Validate that the change is written to both tables
- Enable the feature flag:
::Feature.enable(:group_microsoft_applications_table)
- Quit and restart the Rails console session to refresh the feature flag cache
- Validate that the model now returns the new GroupMicrosoftApplication object:
group = Group.find_by_path('atevans-sso') group.system_access_microsoft_application
- Update the group's "Microsoft Azure Integration" values again in the web UI
- Validate on the console that the
GroupMicrosoftApplication
record was updated - Validate on the console that the legacy
MicrosoftApplication
record was also updated
Related to #495499 (closed)
Merge request reports
Activity
changed milestone to %17.8
assigned to @atevans
added pipelinetier-1 label
added backend database databasereview pending feature flag labels
- Resolved by Andrew Evans
- Resolved by Andrew Evans
- Resolved by Andrew Evans
Reviewer roulette
Category Reviewer Maintainer backend @adie.po
(UTC+0, 8 hours ahead of author)
@wandering_person
(UTC+7, 15 hours ahead of author)
database @dskim_gitlab
(UTC+11, 19 hours ahead of author)
@pshutsin
(UTC+1, 9 hours ahead of 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
DangerEdited by ****mentioned in issue #495499 (closed)
- Resolved by Doug Stull
Database migrations (on the main database)
1 Warnings 20241218181140 - SplitMicrosoftApplicationsData had a query that exceeded timing guidelines. Run
time should not exceed 100ms, but it was 724.36ms. Please consider possible options to improve the
query performance.INSERT INTO system_access_group_microsoft_applications (temp_source_id,
group_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret,
encrypted_client_secret_iv, created_at, updated_at)
SELECT id, namespace_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint,
encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at
FROM system_access_microsoft_applications
WHERE namespace_id IS NOT NULLMigrations 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 20241218181140 - SplitMicrosoftApplicationsData Post deploy 6.4 s +232.00 KiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 1 0.1 seconds - 1 second 4 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20241218181140 - SplitMicrosoftApplicationsData
* Duration: 6.4 s
* Database size change: +232.00 KiBCalls Total Time Max Time Mean Time Rows Query 1 724.4 ms 724.4 ms 724.4 ms 228 INSERT INTO system_access_group_microsoft_applications (temp_source_id, group_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at)
SELECT id, namespace_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at
FROM system_access_microsoft_applications
WHERE namespace_id IS NOT NULL1 7.5 ms 7.5 ms 7.5 ms 14 INSERT INTO system_access_group_microsoft_graph_access_tokens (temp_source_id, system_access_group_microsoft_application_id, group_id, expires_in, encrypted_token, encrypted_token_iv, created_at, updated_at)
SELECT mgat.id, gma.id, gma.group_id, mgat.expires_in, mgat.encrypted_token, mgat.encrypted_token_iv, mgat.created_at, mgat.updated_at
FROM system_access_microsoft_graph_access_tokens mgat
LEFT JOIN system_access_group_microsoft_applications gma ON gma.temp_source_id = mgat.system_access_microsoft_application_id
WHERE gma.id IS NOT NULL1 0.0 ms 0.0 ms 0.0 ms 1 SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SplitMicrosoftApplicationsData
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 1 0.1 seconds - 1 second 4 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Background Migration: SkipCompanyOnboardingStep
Sampled 101 batches. Estimated Time to complete: 1 week, 1 day, 12 hours, and 36 minutes
- Interval: 120s
- Max batch size: 10000
- Estimated seconds to complete: 736560s
- Average batch time: 7.07s
- Batch size: 3000
- N. of batches sampled: 101
- 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 1100 142430.7 ms 300.5 ms 129.5 ms 586 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)101 12974.7 ms 202.1 ms 128.5 ms 40 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)202 36.6 ms 7.4 ms 0.2 ms 202 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
1201 196.6 ms 3.8 ms 0.2 ms 1100 SELECT users.id
FROM users
WHERE users.id BETWEEN $1 AND $2 AND users.id >= $3
ORDER BY users.id ASC
LIMIT $4
OFFSET $5101 15.1 ms 2.3 ms 0.1 ms 101 UPDATE batched_background_migration_jobs
SET updated_at = $1, started_at = $2, status = $3, attempts = $4
WHERE batched_background_migration_jobs.id = $5101 16.4 ms 0.8 ms 0.2 ms 101 UPDATE batched_background_migration_jobs
SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
WHERE batched_background_migration_jobs.id = $5101 7.3 ms 0.4 ms 0.1 ms 101 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)202 8.8 ms 0.4 ms 0.0 ms 202 SELECT batched_background_migration_jobs.*
FROM batched_background_migration_jobs
WHERE batched_background_migration_jobs.id = $1
LIMIT $2101 3.7 ms 0.1 ms 0.0 ms 101 SELECT users.id
FROM users
WHERE users.id BETWEEN $1 AND $2
ORDER BY users.id ASC
LIMIT $3Histogram of batch runtimes for SkipCompanyOnboardingStep
Batch Runtime Count 0 seconds - 10 seconds 101 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 222 0.1 seconds - 0.5 seconds 2988 0.5 seconds - 1 second 0 1 second - 2 seconds 0 2 seconds - 5 seconds 0 5 seconds + 0 Background Migration: FixIssuesWorkItemTypeIdValues
Sampled 7 batches. Estimated Time to complete: 2 weeks, 2 days, 7 hours, and 34 minutes
- Interval: 120s
- Max batch size: 30000
- Estimated seconds to complete: 1409640s
- Average batch time: 138.77s
- Batch size: 10000
- N. of batches sampled: 7
- 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 1201 435523.3 ms 47461.6 ms 362.6 ms 60002 UPDATE issues
SET work_item_type_id = work_item_types.id
FROM work_item_types
WHERE issues.correct_work_item_type_id = work_item_types.correct_id AND issues.id BETWEEN $1 AND $27 18.5 ms 11.3 ms 2.6 ms 7 UPDATE batched_background_migration_jobs
SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
WHERE batched_background_migration_jobs.id = $51201 94.6 ms 1.5 ms 0.1 ms 1194 SELECT issues.id
FROM issues
WHERE issues.id BETWEEN $1 AND $2 AND issues.id >= $3
ORDER BY issues.id ASC
LIMIT $4
OFFSET $514 3.5 ms 1.5 ms 0.3 ms 14 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
7 1.5 ms 1.0 ms 0.2 ms 7 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)1194 45.2 ms 0.2 ms 0.0 ms 1194 SELECT min(id), max(id)
FROM issues
WHERE issues.id BETWEEN $1 AND $2 AND issues.id >= $3 AND issues.id < $4
LIMIT $57 0.7 ms 0.1 ms 0.1 ms 7 UPDATE batched_background_migration_jobs
SET updated_at = $1, started_at = $2, status = $3, attempts = $4
WHERE batched_background_migration_jobs.id = $514 0.8 ms 0.1 ms 0.1 ms 14 SELECT batched_background_migration_jobs.*
FROM batched_background_migration_jobs
WHERE batched_background_migration_jobs.id = $1
LIMIT $27 0.3 ms 0.0 ms 0.0 ms 7 SELECT min(id), max(id)
FROM issues
WHERE issues.id BETWEEN $1 AND $2 AND issues.id >= $3
LIMIT $47 0.2 ms 0.0 ms 0.0 ms 7 SELECT issues.id
FROM issues
WHERE issues.id BETWEEN $1 AND $2
ORDER BY issues.id ASC
LIMIT $3Histogram of batch runtimes for FixIssuesWorkItemTypeIdValues
Batch Runtime Count 0 seconds - 10 seconds 1 10 seconds - 1 minute 0 1 minute - 2 minutes 2 2 minutes - 3 minutes 2 3 minutes - 5 minutes 2 5 minutes + 0 Histogram across all sampled batches of FixIssuesWorkItemTypeIdValues
Query Runtime Count 0 seconds - 0.1 seconds 0 0.1 seconds - 0.5 seconds 3586 0.5 seconds - 1 second 58 1 second - 2 seconds 3 2 seconds - 5 seconds 1 5 seconds + 11 Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change 20241219144523 - QueueSkipCompanyOnboardingStep Post deploy 5.8 s +0.00 B 20241226131806 - FinalizeMigrateOsSbomOccurrencesToComponentsWithoutPrefix Post deploy 5.2 s +0.00 B 20241231055516 - CleanupProjectSettingPagesDefaultDomainRedirectRename Post deploy 5.1 s +0.00 B 20250106173236 - QueueFixIssuesWorkItemTypeIdValues Post deploy 5.1 s +0.00 B 20250107134154 - FinalizeBackfillVulnerabilityUserMentionsProjectId Post deploy 4.8 s +0.00 B Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-4072023-16738059-main
2025-01-10T03:59:19Z 2025-01-08T23:12:29Z 2025-01-10 16:45:55 +0000 database-testing-4072023-16738059-ci
2025-01-10T03:59:18Z 2025-01-09T22:49:28Z 2025-01-10 16:45:55 +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 20241218181140 - SplitMicrosoftApplicationsData Post deploy 7.7 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 2 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20241218181140 - SplitMicrosoftApplicationsData
* Duration: 7.7 s
* Database size change: +0.00 BCalls 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 SplitMicrosoftApplicationsData
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 0 0.1 seconds - 1 second 2 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-4072023-16738059-main
2025-01-10T03:59:19Z 2025-01-08T23:12:29Z 2025-01-10 16:45:55 +0000 database-testing-4072023-16738059-ci
2025-01-10T03:59:18Z 2025-01-09T22:49:28Z 2025-01-10 16:45:55 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
Edited by ****
added database-testing-automation label
added 502 commits
-
9133c171...778d94f8 - 501 commits from branch
master
- 085bc85d - Split microsoft_applications tables
-
9133c171...778d94f8 - 501 commits from branch
changed milestone to %17.9
added missed-deliverable missed:17.8 labels
- Resolved by Andrew Evans
- Resolved by Andrew Evans
requested review from @dstull and @tachyons-gitlab
- Resolved by Drew Blessing
@tachyons-gitlab can you do the initial backend review for groupauthentication ?
@dstull can you do the initial database review?
Thanks!
- Resolved by Doug Stull
- Resolved by Andrew Evans
- Resolved by Drew Blessing
- Resolved by Andrew Evans
added 1227 commits
-
ff75d6f9...72ecc190 - 1223 commits from branch
master
- 8244299b - Split microsoft_applications tables
- 6d473a29 - Add ON CONFLICT clause to migration
- 7205642c - Update migration milestone
- cf086e8e - Add unique index, fix query performance
Toggle commit list-
ff75d6f9...72ecc190 - 1223 commits from branch
- Resolved by Doug Stull
added Data WarehouseImpact Check label
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 20250117211155 - SystemAccessGroupMicrosoftApplicationsUniqueGroupId Regular 5.6 s +0.00 B 20250127052138 - QueueSplitMicrosoftApplicationsTable Post deploy 5.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 7 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20250117211155 - SystemAccessGroupMicrosoftApplicationsUniqueGroupId
* Duration: 5.6 s
* Database size change: +0.00 BCalls Total Time Max Time Mean Time Rows Query 1 12.3 ms 12.3 ms 12.3 ms 0 TRUNCATE TABLE "system_access_group_microsoft_graph_access_tokens", "system_access_group_microsoft_applications"
1 6.5 ms 6.5 ms 6.5 ms 0 CREATE UNIQUE INDEX "index_system_access_group_microsoft_applications_on_group_id" ON "system_access_group_microsoft_applications" ("group_id")
1 4.5 ms 4.5 ms 4.5 ms 0 DROP INDEX "index_system_access_group_microsoft_applications_on_group_id"
1 0.0 ms 0.0 ms 0.0 ms 1 SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SystemAccessGroupMicrosoftApplicationsUniqueGroupId
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 3 0.1 seconds - 1 second 3 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20250127052138 - QueueSplitMicrosoftApplicationsTable
* Duration: 5.5 s
* Database size change: +0.00 BCalls Total Time Max Time Mean Time Rows Query 1 6.4 ms 6.4 ms 6.4 ms 1 INSERT INTO "batched_background_migrations" ("created_at", "updated_at", "max_value", "batch_size", "sub_batch_size", "interval", "status", "job_class_name", "table_name", "column_name", "total_tuple_count", "started_at", "gitlab_schema", "queued_migration_version") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) RETURNING "id"
1 2.3 ms 2.3 ms 2.3 ms 1 SELECT MAX("id")
FROM "system_access_microsoft_applications"1 1.3 ms 1.3 ms 1.3 ms 0 SELECT $1 AS one
FROM "batched_background_migrations" WHERE "batched_background_migrations"."job_class_name" = $2 AND "batched_background_migrations"."table_name" = $3 AND "batched_background_migrations"."column_name" = $4 AND (job_arguments = $5) AND (ARRAY[$6] <@ ARRAY[$7, $8, $9, $10, $11, $12, $13]) AND "batched_background_migrations"."gitlab_schema" IN ($14, $15, $16, $17, $18, $19, $20)
LIMIT $211 0.0 ms 0.0 ms 0.0 ms 0 SELECT $1 AS one
FROM "batched_background_migrations" WHERE "batched_background_migrations"."job_arguments" = $2 AND "batched_background_migrations"."job_class_name" = $3 AND "batched_background_migrations"."table_name" = $4 AND "batched_background_migrations"."column_name" = $5
LIMIT $62 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for QueueSplitMicrosoftApplicationsTable
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 2 0.1 seconds - 1 second 4 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Background Migration: SplitMicrosoftApplicationsTable
Sampled 1 batches. Estimated Time to complete: 0 seconds
- Interval: 120s
- Max batch size: 0
- Estimated seconds to complete: 0s
- Average batch time: 3.38s
- Batch size: 1000
- N. of batches sampled: 1
- 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 2 664.7 ms 401.6 ms 332.4 ms 200 INSERT INTO system_access_group_microsoft_applications (temp_source_id, group_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at)
SELECT id, namespace_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at
FROM (
SELECT system_access_microsoft_applications.*
FROM system_access_microsoft_applications
WHERE system_access_microsoft_applications.id BETWEEN $1 AND $2 AND system_access_microsoft_applications.id >= $3 AND system_access_microsoft_applications.id < $4 AND system_access_microsoft_applications.namespace_id IS NOT NULL
) sama ON CONFLICT DO NOTHING1 108.0 ms 108.0 ms 108.0 ms 42 INSERT INTO system_access_group_microsoft_applications (temp_source_id, group_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at)
SELECT id, namespace_id, enabled, tenant_xid, client_xid, login_endpoint, graph_endpoint, encrypted_client_secret, encrypted_client_secret_iv, created_at, updated_at
FROM (
SELECT system_access_microsoft_applications.*
FROM system_access_microsoft_applications
WHERE system_access_microsoft_applications.id BETWEEN $1 AND $2 AND system_access_microsoft_applications.id >= $3 AND system_access_microsoft_applications.namespace_id IS NOT NULL
) sama ON CONFLICT DO NOTHING2 10.2 ms 10.0 ms 5.1 ms 2 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
1 0.2 ms 0.2 ms 0.2 ms 1 UPDATE batched_background_migration_jobs
SET updated_at = $1, started_at = $2, status = $3, attempts = $4
WHERE batched_background_migration_jobs.id = $51 0.1 ms 0.1 ms 0.1 ms 1 UPDATE batched_background_migration_jobs
SET updated_at = $1, finished_at = $2, status = $3, metrics = $4
WHERE batched_background_migration_jobs.id = $52 0.1 ms 0.0 ms 0.0 ms 2 SELECT batched_background_migration_jobs.*
FROM batched_background_migration_jobs
WHERE batched_background_migration_jobs.id = $1
LIMIT $21 0.0 ms 0.0 ms 0.0 ms 1 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)3 0.1 ms 0.0 ms 0.0 ms 2 SELECT system_access_microsoft_applications.id
FROM system_access_microsoft_applications
WHERE system_access_microsoft_applications.id BETWEEN $1 AND $2 AND system_access_microsoft_applications.id >= $3
ORDER BY system_access_microsoft_applications.id ASC
LIMIT $4
OFFSET $51 0.0 ms 0.0 ms 0.0 ms 1 SELECT system_access_microsoft_applications.id
FROM system_access_microsoft_applications
WHERE system_access_microsoft_applications.id BETWEEN $1 AND $2
ORDER BY system_access_microsoft_applications.id ASC
LIMIT $3Histogram of batch runtimes for SplitMicrosoftApplicationsTable
Batch Runtime Count 0 seconds - 10 seconds 1 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 SplitMicrosoftApplicationsTable
Query Runtime Count 0 seconds - 0.1 seconds 10 0.1 seconds - 0.5 seconds 3 0.5 seconds - 1 second 1 1 second - 2 seconds 0 2 seconds - 5 seconds 0 5 seconds + 0 No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-4145284-17024964-main
2025-01-31T06:20:46Z 2025-01-31T04:09:55Z 2025-01-31 18:31:47 +0000 database-testing-4145284-17024964-ci
2025-01-31T06:20:46Z 2025-01-31T04:44:58Z 2025-01-31 18:31:47 +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 20250117211155 - SystemAccessGroupMicrosoftApplicationsUniqueGroupId Regular 8.3 s +0.00 B 20250127052138 - QueueSplitMicrosoftApplicationsTable Post deploy 7.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 0 0.1 seconds - 1 second 6 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20250117211155 - SystemAccessGroupMicrosoftApplicationsUniqueGroupId
* Duration: 8.3 s
* Database size change: +0.00 BCalls Total Time Max Time Mean Time Rows Query 1 17.2 ms 17.2 ms 17.2 ms 0 DROP INDEX "index_system_access_group_microsoft_applications_on_group_id"
1 4.2 ms 4.2 ms 4.2 ms 0 CREATE UNIQUE INDEX "index_system_access_group_microsoft_applications_on_group_id" ON "system_access_group_microsoft_applications" ("group_id")
2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for SystemAccessGroupMicrosoftApplicationsUniqueGroupId
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 0 0.1 seconds - 1 second 4 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20250127052138 - QueueSplitMicrosoftApplicationsTable
* Duration: 7.2 s
* Database size change: +0.00 BCalls 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 QueueSplitMicrosoftApplicationsTable
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 0 0.1 seconds - 1 second 2 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-4145284-17024964-main
2025-01-31T06:20:46Z 2025-01-31T04:09:55Z 2025-01-31 18:31:47 +0000 database-testing-4145284-17024964-ci
2025-01-31T06:20:46Z 2025-01-31T04:44:58Z 2025-01-31 18:31:47 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
Edited by ****added 729 commits
-
ffb9ea58...7c93bd9b - 727 commits from branch
master
- 90bed4d5 - Split microsoft_applications tables
- 60b8c597 - Add unique index, fix query performance
-
ffb9ea58...7c93bd9b - 727 commits from branch
mentioned in epic gitlab-org#15359
- Resolved by Drew Blessing
This MR is smaller than the last cells one but is still quite large. Since we have a feature flag and intend that this functionality should work in the same way before the flag is enabled, this would be a great candidate for splitting into multiple MRs. At a minimum, database migrations should be separable from many of the code changes. Smaller MRs mean it's easier to reason about things and not miss potential issues.
- Resolved by Andrew Evans
- Resolved by Adam Hegyi
added 1 commit
- 0aea30a5 - Remove upsert, use batched raw sql to insert records
added 232 commits
-
0aea30a5...65f4780b - 230 commits from branch
master
- 3c0c4be5 - Split microsoft_applications tables
- d9f64be4 - Remove feature flag, new code functionality
-
0aea30a5...65f4780b - 230 commits from branch
added 1 commit
- 57936e6b - Remove EE model from non-EE background migration
requested review from @dstull
added databasereviewed label and removed databasereview pending label
requested review from @ahegyi
added pipeline:mr-approved label
added pipelinetier-2 label and removed pipelinetier-1 label