Skip to content
Snippets Groups Projects

Migrate data to system_access_group_microsoft_applications

Merged Andrew Evans requested to merge 495499-split-microsoft-applications into master

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.

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:

  1. 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
  2. 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)
  3. 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"
  4. Ensure the data is populated in the system_access_microsoft_applications table

Switch to this branch, and then:

  1. Run migrations
  2. Ensure the group saml entry in system_access_microsoft_applications is copied to system_access_group_microsoft_applications
  3. 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
  4. Update the data for the group's Azure integration, changing the client-xid or graph_endpoint values
  5. Validate that the change is written to both tables
  6. Enable the feature flag: ::Feature.enable(:group_microsoft_applications_table)
  7. Quit and restart the Rails console session to refresh the feature flag cache
  8. Validate that the model now returns the new GroupMicrosoftApplication object:
     group = Group.find_by_path('atevans-sso')
    group.system_access_microsoft_application
  9. Update the group's "Microsoft Azure Integration" values again in the web UI
  10. Validate on the console that the GroupMicrosoftApplication record was updated
  11. Validate on the console that the legacy MicrosoftApplication record was also updated

Related to #495499 (closed)

Edited by Andrew Evans

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • Reviewer roulette

    Category Reviewer Maintainer
    backend @adie.po profile link current availability (UTC+0, 8 hours ahead of author) @wandering_person profile link current availability (UTC+7, 15 hours ahead of author)
    database @dskim_gitlab profile link current availability (UTC+11, 19 hours ahead of author) @pshutsin profile link current availability (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 :repeat: danger-review job that generated this comment.

    Generated by :no_entry_sign: Danger

    Edited by ****
  • mentioned in issue #495499 (closed)

    • Resolved by Doug Stull

      Database migrations (on the main database)

      1 Warnings
      :warning: 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 NULL

      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 6.4 s :warning: +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
      :warning:

      Migration: 20241218181140 - SplitMicrosoftApplicationsData

      * Type: Post deploy
      * Duration: 6.4 s
      * Database size change: +232.00 KiB
      Calls 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 NULL
      1 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 NULL
      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 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 $5
      101 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 = $5
      101 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 = $5
      101 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 $2
      101 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 $3
      Histogram 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 $2
      7 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 = $5
      1201 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 $5
      14 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 $5
      7 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 = $5
      14 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 $2
      7 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 $4
      7 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 $3
      Histogram 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 information
      Other migrations pending on GitLab.com
      Migration Type Total runtime Result DB size change
      20241219144523 - QueueSkipCompanyOnboardingStep Post deploy 5.8 s :white_check_mark: +0.00 B
      20241226131806 - FinalizeMigrateOsSbomOccurrencesToComponentsWithoutPrefix Post deploy 5.2 s :white_check_mark: +0.00 B
      20241231055516 - CleanupProjectSettingPagesDefaultDomainRedirectRename Post deploy 5.1 s :white_check_mark: +0.00 B
      20250106173236 - QueueFixIssuesWorkItemTypeIdValues Post deploy 5.1 s :white_check_mark: +0.00 B
      20250107134154 - FinalizeBackfillVulnerabilityUserMentionsProjectId Post deploy 4.8 s :white_check_mark: +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

      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
      20241218181140 - SplitMicrosoftApplicationsData Post deploy 7.7 s :white_check_mark: +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

      * Type: Post deploy
      * Duration: 7.7 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 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

      Job artifacts


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

      Edited by ****
  • Andrew Evans added 502 commits

    added 502 commits

    Compare with previous version

  • 🤖 GitLab Bot 🤖 changed milestone to %17.9

    changed milestone to %17.9

  • Andrew Evans added 1 commit

    added 1 commit

    • 11d1c24b - Add ON CONFLICT clause to migration

    Compare with previous version

  • Andrew Evans
  • Andrew Evans
  • Andrew Evans changed the description

    changed the description

  • Andrew Evans added 1 commit

    added 1 commit

    Compare with previous version

  • requested review from @dstull and @tachyons-gitlab

  • Doug Stull
  • Doug Stull
  • Doug Stull requested changes

    requested changes

  • Andrew Evans
  • Andrew Evans
  • Andrew Evans added 1227 commits

    added 1227 commits

    Compare with previous version

  • 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 :white_check_mark: +0.00 B
    20250127052138 - QueueSplitMicrosoftApplicationsTable Post deploy 5.5 s :white_check_mark: +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

    * Type: Regular
    * Duration: 5.6 s
    * Database size change: +0.00 B
    Calls 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

    * Type: Post deploy
    * Duration: 5.5 s
    * Database size change: +0.00 B
    Calls 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 $21
    1 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 $6
    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 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 NOTHING
    1 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 NOTHING
    2 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 = $5
    1 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 = $5
    2 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 $2
    1 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 $5
    1 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 $3
    Histogram 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
    #### 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

    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
    20250117211155 - SystemAccessGroupMicrosoftApplicationsUniqueGroupId Regular 8.3 s :white_check_mark: +0.00 B
    20250127052138 - QueueSplitMicrosoftApplicationsTable Post deploy 7.2 s :white_check_mark: +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

    * Type: Regular
    * Duration: 8.3 s
    * Database size change: +0.00 B
    Calls 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

    * Type: Post deploy
    * Duration: 7.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 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

    Job artifacts


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

    Edited by ****
  • Andrew Evans added 1 commit

    added 1 commit

    • ffb9ea58 - Smaller batches for data migration

    Compare with previous version

  • Andrew Evans added 729 commits

    added 729 commits

    Compare with previous version

  • 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.

  • Andrew Evans added 1 commit

    added 1 commit

    • b2cc0070 - Reduce batch size for copy query

    Compare with previous version

  • Aboobacker MK
  • Andrew Evans added 1 commit

    added 1 commit

    • de50a626 - Lower batch size to improve migration perf

    Compare with previous version

  • Andrew Evans added 1 commit

    added 1 commit

    • 0aea30a5 - Remove upsert, use batched raw sql to insert records

    Compare with previous version

  • Andrew Evans added 232 commits

    added 232 commits

    Compare with previous version

  • Andrew Evans added 1 commit

    added 1 commit

    • bc2055ad - Remove migration spec for removed migration

    Compare with previous version

  • Andrew Evans added 1 commit

    added 1 commit

    • f4fbf361 - Remove unrelated changes from structure.sql

    Compare with previous version

  • Andrew Evans added 1 commit

    added 1 commit

    • 57936e6b - Remove EE model from non-EE background migration

    Compare with previous version

  • Andrew Evans requested review from @dstull

    requested review from @dstull

  • added databasereviewed label and removed databasereview pending label

  • Doug Stull requested review from @ahegyi

    requested review from @ahegyi

  • Doug Stull approved this merge request

    approved this merge request

  • added pipelinetier-2 label and removed pipelinetier-1 label

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Please register or sign in to reply
    Loading