Skip to content
Snippets Groups Projects

Swap packages_build_infos pipeline_id to bigint

Merged Swap packages_build_infos pipeline_id to bigint
454420-prepare-indexes-for-ci_pipelines-loose-fks-3 into master
Merged Panos Kanellidis requested to merge 454420-prepare-indexes-for-ci_pipelines-loose-fks-3 into master

What does this MR do and why?

As a part of migrating ci_pipeline-referencing columns to bigint, we need to create indexes for packages_build_infos.pipeline_id_convert_to_bigint.

This MR schedules the indexes to be created asynchronously on gitlab.com, a follow-up MR will be created for self-managed instances.

Required Stop?

16.11 is already being set as a required stop, which based on this comment, enables us to merge the finalization MRs as soon as the next release (or when it's done on Gitlab.com)

MR 3/8

Related to #454420 (closed)

Edited by Panos Kanellidis

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
  • Ghost User
    Ghost User @ghost1
    Contributor
    3 Warnings
    :warning: f3dbd5b6: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
    :warning: f43604cb: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
    :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.
    1 Message
    :book: 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:

    1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
    2. Prepare your MR for database review according to the docs.
    3. Assign and mention the database reviewer suggested by Reviewer Roulette.

    The following files require a review from the Database team:

    • db/post_migrate/20240416103114_ensure_backfill_packages_build_infos_pipeline_id_convert_to_bigint_is_completed.rb
    • db/post_migrate/20240416103210_create_indexes_for_packages_build_infos_pipeline_id_convert_to_bigint.rb
    • db/post_migrate/20240419131607_swap_packages_build_infos_pipeline_id_convert_to_bigint.rb
    • db/schema_migrations/20240416103114
    • db/schema_migrations/20240416103210
    • db/schema_migrations/20240419131607
    • db/structure.sql

    Reviewer roulette

    Category Reviewer Maintainer
    database @dskim_gitlab profile link current availability (UTC+10, 7 hours ahead of author) @DylanGriffith profile link current availability (UTC+10, 7 hours ahead of author)

    Please check reviewer's status!

    • available Reviewer is available!
    • unavailable Reviewer is unavailable!

    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 Ghost User
  • 🤖 GitLab Bot 🤖 removed docs-only label

    removed docs-only label

  • Panos Kanellidis added 1 commit

    added 1 commit

    Compare with previous version

  • Panos Kanellidis added 1 commit

    added 1 commit

    Compare with previous version

  • Ghost User
    Ghost User @ghost1
    Contributor

    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
    20240416103114 - EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted Post deploy 1.9 s :white_check_mark: +0.00 B
    20240416103210 - CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint Post deploy 183.3 s :white_check_mark: +1.34 GiB
    20240419131607 - SwapPackagesBuildInfosPipelineIdConvertToBigint Post deploy 2.1 s :white_check_mark: -1.41 GiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 18
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 2
    5 minutes + 0

    Migration: 20240416103114 - EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted

    • Type: Post deploy
    • Duration: 1.9 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 3.2 ms 3.2 ms 3.2 ms 1 
    UPDATE "batched_background_migrations"  SET "updated_at" = $1, "status" = $2
      WHERE "batched_background_migrations"."id" = $3
    1 3.2 ms 3.2 ms 3.2 ms 1 
    SELECT "batched_background_migrations".*
      FROM "batched_background_migrations"  WHERE "batched_background_migrations"."job_class_name" = $1 AND "batched_background_migrations"."table_name" = $2 AND "batched_background_migrations"."column_name" = $3 AND (job_arguments = $4) AND "batched_background_migrations"."gitlab_schema" = $5
      ORDER BY "batched_background_migrations"."id" ASC
      LIMIT $6
    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"."id" != $3 AND "batched_background_migrations"."job_class_name" = $4 AND "batched_background_migrations"."table_name" = $5 AND "batched_background_migrations"."column_name" = $6
      LIMIT $7
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 5
    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: 20240416103210 - CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint

    • Type: Post deploy
    • Duration: 183.3 s
    • Database size change: +1.34 GiB
    Calls Total Time Max Time Mean Time Rows Query
    1 90630.3 ms 90630.3 ms 90630.3 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_build_infos_on_pipeline_id_bigint" ON "packages_build_infos" ("pipeline_id_convert_to_bigint")
    1 90228.3 ms 90228.3 ms 90228.3 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_build_infos_package_id_pipeline_id_bigint_id" ON "packages_build_infos" ("package_id", "pipeline_id_convert_to_bigint", "id")
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 2
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 2
    5 minutes + 0

    Migration: 20240419131607 - SwapPackagesBuildInfosPipelineIdConvertToBigint

    • Type: Post deploy
    • Duration: 2.1 s
    • Database size change: -1.41 GiB
    Calls Total Time Max Time Mean Time Rows Query
    1 16.1 ms 16.1 ms 16.1 ms 0 
    ALTER FUNCTION "trigger_388e93f88fdd" RE
    SET ALL
    1 14.0 ms 14.0 ms 14.0 ms 0 
    DROP INDEX index_packages_build_infos_on_pipeline_id
    1 8.1 ms 8.1 ms 8.1 ms 0 
    DROP INDEX index_packages_build_infos_package_id_pipeline_id_id
    1 2.6 ms 2.6 ms 2.6 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "pipeline_id" TO "temp_name_for_renaming"
    1 0.3 ms 0.3 ms 0.3 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "temp_name_for_renaming" TO "pipeline_id_convert_to_bigint"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER INDEX "index_packages_build_infos_package_id_pipeline_id_bigint_id" RENAME TO "index_packages_build_infos_package_id_pipeline_id_id"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "pipeline_id_convert_to_bigint" TO "pipeline_id"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER INDEX "index_packages_build_infos_on_pipeline_id_bigint" RENAME TO "index_packages_build_infos_on_pipeline_id"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    LOCK TABLE packages_build_infos IN ACCESS EXCLUSIVE MODE
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for SwapPackagesBuildInfosPipelineIdConvertToBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 11
    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
    20240328032449 - DropMergeRequestDiffLlmSummaryTable Post deploy 2.1 s :warning: -750.01 MiB
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-3157665-13650373-main 2024-04-23T12:00:44Z 2024-04-19T16:09:46Z 2024-04-24 00:09:08 +0000
    database-testing-3157665-13650373-ci 2024-04-23T12:00:45Z 2024-04-23T08:45:40Z 2024-04-24 00:09:08 +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
    20240416103114 - EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted Post deploy 2.6 s :white_check_mark: +0.00 B
    20240416103210 - CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint Post deploy 3.7 s :white_check_mark: +16.00 KiB
    20240419131607 - SwapPackagesBuildInfosPipelineIdConvertToBigint Post deploy 2.8 s :white_check_mark: -16.00 KiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 16
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240416103114 - EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted

    • Type: Post deploy
    • Duration: 2.6 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 EnsureBackfillPackagesBuildInfosPipelineIdConvertToBigintIsCompleted
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 2
    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: 20240416103210 - CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint

    • Type: Post deploy
    • Duration: 3.7 s
    • Database size change: +16.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 61.0 ms 61.0 ms 61.0 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_build_infos_on_pipeline_id_bigint" ON "packages_build_infos" ("pipeline_id_convert_to_bigint")
    1 1.8 ms 1.8 ms 1.8 ms 0 
    CREATE INDEX CONCURRENTLY "index_packages_build_infos_package_id_pipeline_id_bigint_id" ON "packages_build_infos" ("package_id", "pipeline_id_convert_to_bigint", "id")
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for CreateIndexesForPackagesBuildInfosPipelineIdConvertToBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20240419131607 - SwapPackagesBuildInfosPipelineIdConvertToBigint

    • Type: Post deploy
    • Duration: 2.8 s
    • Database size change: -16.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 5.7 ms 5.7 ms 5.7 ms 0 
    DROP INDEX index_packages_build_infos_on_pipeline_id
    1 5.0 ms 5.0 ms 5.0 ms 0 
    DROP INDEX index_packages_build_infos_package_id_pipeline_id_id
    1 0.3 ms 0.3 ms 0.3 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "pipeline_id_convert_to_bigint" TO "pipeline_id"
    1 0.3 ms 0.3 ms 0.3 ms 0 
    ALTER INDEX "index_packages_build_infos_package_id_pipeline_id_bigint_id" RENAME TO "index_packages_build_infos_package_id_pipeline_id_id"
    1 0.3 ms 0.3 ms 0.3 ms 0 
    ALTER INDEX "index_packages_build_infos_on_pipeline_id_bigint" RENAME TO "index_packages_build_infos_on_pipeline_id"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "pipeline_id" TO "temp_name_for_renaming"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER FUNCTION "trigger_388e93f88fdd" RE
    SET ALL
    1 0.2 ms 0.2 ms 0.2 ms 0 
    ALTER TABLE "packages_build_infos" RENAME COLUMN "temp_name_for_renaming" TO "pipeline_id_convert_to_bigint"
    1 0.2 ms 0.2 ms 0.2 ms 0 
    LOCK TABLE packages_build_infos IN ACCESS EXCLUSIVE MODE
    2 0.0 ms 0.0 ms 0.0 ms 2 
    SELECT pg_backend_pid()
    Histogram for SwapPackagesBuildInfosPipelineIdConvertToBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 11
    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-3157665-13650373-main 2024-04-23T12:00:44Z 2024-04-19T16:09:46Z 2024-04-24 00:09:08 +0000
    database-testing-3157665-13650373-ci 2024-04-23T12:00:45Z 2024-04-23T08:45:40Z 2024-04-24 00:09:08 +0000

    Job artifacts

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

    Edited by Ghost User
  • A deleted user added database-testing-automation label

    added database-testing-automation label

  • Panos Kanellidis changed the description

    changed the description

  • Panos Kanellidis requested review from @morefice

    requested review from @morefice

  • Panos Kanellidis changed milestone to %17.0

    changed milestone to %17.0

  • Max Orefice added databasereviewed label and removed databasereview pending label

    added databasereviewed label and removed databasereview pending label

  • Max Orefice approved this merge request

    approved this merge request

  • 🤖 GitLab Bot 🤖 added pipeline:mr-approved label

    added pipeline:mr-approved label

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