Skip to content
Snippets Groups Projects

Swap primary key with bigint column for ci_pipeline_variables

Merged Tianwen Chen requested to merge 408936-swap-pk into master

What does this MR do and why?

Swap the integer primary key with bigint column for ci_pipeline_variables

See the second step at https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#swap-the-columns-release-n--1

The first step !118878 (merged) is merged and executed on production, see #408936 (comment 1391375272)

The unique index index_on_ci_pipeline_variables_on_id_convert_to_bigint is created by !120946 (merged) (async prepare) and !120950 (merged) (concurrent create)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #408936 (closed)

Edited by Tianwen Chen

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
  • Tianwen Chen marked this merge request as ready

    marked this merge request as ready

  • Tianwen Chen marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

    marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

    • Contributor
      Resolved by Tianwen Chen

      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
      20230516070036 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.9 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 14
      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: 20230516070036 - SwapCiPipelineVariablesPkWithBigint

      • Type: Post deploy
      • Duration: 3.9 s
      • Database size change: +0.00 B
      Calls Total Time Max Time Mean Time Rows Query
      1 32.8 ms 32.8 ms 32.8 ms 0
      CREATE UNIQUE INDEX CONCURRENTLY "index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables" ("id_convert_to_bigint")
      1 18.4 ms 18.4 ms 18.4 ms 0
      ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
      1 17.2 ms 17.2 ms 17.2 ms 0
      ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
      1 16.5 ms 16.5 ms 16.5 ms 0
      ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
      1 13.4 ms 13.4 ms 13.4 ms 0
      ALTER FUNCTION "trigger_023e82d8e257" RE
      SET ALL
      1 11.5 ms 11.5 ms 11.5 ms 0
      ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
      1 5.3 ms 5.3 ms 5.3 ms 0
      ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
      1 0.3 ms 0.3 ms 0.3 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
      1 0.3 ms 0.3 ms 0.3 ms 0
      ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
      1 0.2 ms 0.2 ms 0.2 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
      1 0.2 ms 0.2 ms 0.2 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
      1 0.1 ms 0.1 ms 0.1 ms 0
      LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
      2 0.0 ms 0.0 ms 0.0 ms 2
      SELECT pg_backend_pid()
      Histogram for SwapCiPipelineVariablesPkWithBigint
      Query Runtime Count
      0 seconds - 0.01 seconds 0
      0.01 seconds - 0.1 seconds 14
      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-1940853-10056159-main 2023-05-16T06:56:14Z 2023-05-16T01:10:51Z 2023-05-16 21:33:17 +0000
      database-testing-1940853-10056159-ci 2023-05-16T06:56:15Z 2023-05-16T04:46:05Z 2023-05-16 21:33:17 +0000

      Job artifacts

      Database migrations (on the ci database)

      3 Warnings
      :warning: 20230516070036 - SwapCiPipelineVariablesPkWithBigint had a query that exceeded timing
      guidelines
      . Run time should not exceed 100ms, but it was 9124336.47ms. Please consider possible
      options to improve the query performance.
      CREATE UNIQUE INDEX CONCURRENTLY
      "index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables"
      ("id_convert_to_bigint")
      :warning: 20230516070036 - SwapCiPipelineVariablesPkWithBigint took 152.35min. Please add a comment
      that mentions Release Managers (@gitlab-org/release/managers) so they are informed.
      :warning: 20230516070036 - SwapCiPipelineVariablesPkWithBigint may need a batched background migration
      to comply with timing guidelines. It took 152.35min, but should not exceed 10.0min

      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
      20230516070036 - SwapCiPipelineVariablesPkWithBigint Post deploy 9142.0 s :warning: -26.42 GiB
      Runtime Histogram for all migrations
      Query Runtime Count
      0 seconds - 0.01 seconds 0
      0.01 seconds - 0.1 seconds 13
      0.1 seconds - 1 second 0
      1 second - 5 seconds 0
      5 seconds - 15 seconds 0
      15 seconds - 5 minutes 0
      5 minutes + 1

      :warning: Migration: 20230516070036 - SwapCiPipelineVariablesPkWithBigint

      • Type: Post deploy
      • Duration: 9142.0 s
      • Database size change: -26.42 GiB
      Calls Total Time Max Time Mean Time Rows Query
      1 9124336.5 ms 9124336.5 ms 9124336.5 ms 0
      CREATE UNIQUE INDEX CONCURRENTLY "index_ci_pipeline_variables_on_id_convert_to_bigint" ON "ci_pipeline_variables" ("id_convert_to_bigint")
      1 33.9 ms 33.9 ms 33.9 ms 0
      LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
      1 27.1 ms 27.1 ms 27.1 ms 0
      ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
      1 25.4 ms 25.4 ms 25.4 ms 0
      ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
      1 23.8 ms 23.8 ms 23.8 ms 0
      ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
      1 18.9 ms 18.9 ms 18.9 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
      1 13.6 ms 13.6 ms 13.6 ms 0
      ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
      1 9.9 ms 9.9 ms 9.9 ms 0
      ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
      1 8.7 ms 8.7 ms 8.7 ms 0
      ALTER FUNCTION "trigger_023e82d8e257" RE
      SET ALL
      1 6.1 ms 6.1 ms 6.1 ms 0
      ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
      1 0.2 ms 0.2 ms 0.2 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
      1 0.2 ms 0.2 ms 0.2 ms 0
      ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
      2 0.0 ms 0.0 ms 0.0 ms 2
      SELECT pg_backend_pid()
      Histogram for SwapCiPipelineVariablesPkWithBigint
      Query Runtime Count
      0 seconds - 0.01 seconds 0
      0.01 seconds - 0.1 seconds 13
      0.1 seconds - 1 second 0
      1 second - 5 seconds 0
      5 seconds - 15 seconds 0
      15 seconds - 5 minutes 0
      5 minutes + 1

      Other information

      No other migrations pending on GitLab.com

      Clone details
      Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
      database-testing-1940853-10056159-main 2023-05-16T06:56:14Z 2023-05-16T01:10:51Z 2023-05-16 21:33:17 +0000
      database-testing-1940853-10056159-ci 2023-05-16T06:56:15Z 2023-05-16T04:46:05Z 2023-05-16 21:33:17 +0000

      Job artifacts


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

  • Tianwen Chen changed the description

    changed the description

  • mentioned in issue #408936 (closed)

  • Tianwen Chen requested review from @ghavenga

    requested review from @ghavenga

  • Marius Bobin mentioned in epic &7522

    mentioned in epic &7522

  • Tianwen Chen removed review request for @ghavenga

    removed review request for @ghavenga

  • Tianwen Chen mentioned in commit 17740a6d

    mentioned in commit 17740a6d

  • Tianwen Chen mentioned in merge request !120946 (merged)

    mentioned in merge request !120946 (merged)

  • Tianwen Chen mentioned in commit a144271d

    mentioned in commit a144271d

  • Tianwen Chen mentioned in merge request !120950 (merged)

    mentioned in merge request !120950 (merged)

  • Tianwen Chen marked this merge request as draft

    marked this merge request as draft

  • Tianwen Chen mentioned in commit 1fc81daf

    mentioned in commit 1fc81daf

  • Tianwen Chen added 1 commit

    added 1 commit

    • dbb391b3 - Swap primary key with bigint column for ci_pipeline_variables

    Compare with previous version

  • Tianwen Chen mentioned in commit 372f3224

    mentioned in commit 372f3224

  • Tianwen Chen mentioned in commit efbd5e10

    mentioned in commit efbd5e10

  • Tianwen Chen added 3910 commits

    added 3910 commits

    Compare with previous version

  • 🤖 GitLab Bot 🤖 added sectionci label and removed sectionops label

    added sectionci label and removed sectionops label

  • Tianwen Chen mentioned in commit 1d9c5c9a

    mentioned in commit 1d9c5c9a

  • Tianwen Chen mentioned in commit 99b7ecef

    mentioned in commit 99b7ecef

  • Tianwen Chen added 1 commit

    added 1 commit

    • 80c409c3 - Swap primary key with bigint column for ci_pipeline_variables

    Compare with previous version

  • Contributor

    Allure report

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :exclamation: test report for 3c6e3638

    expand test summary
    +-----------------------------------------------------------------------+
    |                            suites summary                             |
    +------------------+--------+--------+---------+-------+-------+--------+
    |                  | passed | failed | skipped | flaky | total | result |
    +------------------+--------+--------+---------+-------+-------+--------+
    | Create           | 8      | 0      | 1       | 6     | 9     | ❗     |
    | Manage           | 1      | 0      | 0       | 0     | 1     | ✅     |
    | Monitor          | 4      | 0      | 0       | 4     | 4     | ❗     |
    | Plan             | 4      | 0      | 0       | 3     | 4     | ❗     |
    | Data Stores      | 2      | 0      | 0       | 1     | 2     | ❗     |
    | Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
    | Govern           | 2      | 0      | 0       | 0     | 2     | ✅     |
    +------------------+--------+--------+---------+-------+-------+--------+
    | Total            | 21     | 0      | 2       | 14    | 23    | ❗     |
    +------------------+--------+--------+---------+-------+-------+--------+
  • Tianwen Chen marked this merge request as ready

    marked this merge request as ready

  • Tianwen Chen requested review from @mfanGitLab

    requested review from @mfanGitLab

  • Tianwen Chen requested review from @ghavenga

    requested review from @ghavenga

  • Tianwen Chen changed the description

    changed the description

  • 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
    20230519023720 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.2 s :white_check_mark: -8.00 KiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 13
    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: 20230519023720 - SwapCiPipelineVariablesPkWithBigint

    • Type: Post deploy
    • Duration: 3.2 s
    • Database size change: -8.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 14.8 ms 14.8 ms 14.8 ms 0
    ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
    1 9.4 ms 9.4 ms 9.4 ms 0
    ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
    1 8.7 ms 8.7 ms 8.7 ms 0
    ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
    1 5.8 ms 5.8 ms 5.8 ms 0
    ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
    1 5.3 ms 5.3 ms 5.3 ms 0
    ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
    1 4.3 ms 4.3 ms 4.3 ms 0
    ALTER FUNCTION "trigger_023e82d8e257" RE
    SET ALL
    1 3.0 ms 3.0 ms 3.0 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
    1 2.7 ms 2.7 ms 2.7 ms 0
    ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
    1 1.0 ms 1.0 ms 1.0 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
    1 0.4 ms 0.4 ms 0.4 ms 0
    LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for SwapCiPipelineVariablesPkWithBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 13
    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-2035936-10370091-main 2023-06-19T02:37:50Z 2023-06-19T00:11:02Z 2023-06-19 14:43:28 +0000
    database-testing-2035936-10370091-ci 2023-06-19T02:37:50Z 2023-06-19T00:45:54Z 2023-06-19 14:43:28 +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
    20230519023720 - SwapCiPipelineVariablesPkWithBigint Post deploy 3.7 s :white_check_mark: -19.63 GiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 13
    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: 20230519023720 - SwapCiPipelineVariablesPkWithBigint

    • Type: Post deploy
    • Duration: 3.7 s
    • Database size change: -19.63 GiB
    Calls Total Time Max Time Mean Time Rows Query
    1 4.0 ms 4.0 ms 4.0 ms 0
    ALTER FUNCTION "trigger_023e82d8e257" RE
    SET ALL
    1 3.9 ms 3.9 ms 3.9 ms 0
    ALTER SEQUENCE ci_pipeline_variables_id_seq OWNED BY ci_pipeline_variables.id
    1 2.7 ms 2.7 ms 2.7 ms 0
    ALTER TABLE ci_pipeline_variables DROP CONSTRAINT ci_pipeline_variables_pkey CASCADE
    1 2.5 ms 2.5 ms 2.5 ms 0
    ALTER TABLE ci_pipeline_variables ADD CONSTRAINT ci_pipeline_variables_pkey PRIMARY KEY USING INDEX ci_pipeline_variables_pkey
    1 2.0 ms 2.0 ms 2.0 ms 0
    ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id"SET DEFAULT nextval('ci_pipeline_variables_id_seq'::regclass)
    1 1.8 ms 1.8 ms 1.8 ms 0
    ALTER TABLE "ci_pipeline_variables" ALTER COLUMN "id_convert_to_bigint"SET DEFAULT 0
    1 1.4 ms 1.4 ms 1.4 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id" TO "id_tmp"
    1 1.2 ms 1.2 ms 1.2 ms 0
    ALTER INDEX "index_ci_pipeline_variables_on_id_convert_to_bigint" RENAME TO "ci_pipeline_variables_pkey"
    1 0.9 ms 0.9 ms 0.9 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_convert_to_bigint" TO "id"
    1 0.2 ms 0.2 ms 0.2 ms 0
    LOCK TABLE ci_pipeline_variables IN ACCESS EXCLUSIVE MODE
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE ci_pipeline_variables RENAME COLUMN "id_tmp" TO "id_convert_to_bigint"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for SwapCiPipelineVariablesPkWithBigint
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 13
    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-2035936-10370091-main 2023-06-19T02:37:50Z 2023-06-19T00:11:02Z 2023-06-19 14:43:28 +0000
    database-testing-2035936-10370091-ci 2023-06-19T02:37:50Z 2023-06-19T00:45:54Z 2023-06-19 14:43:28 +0000

    Job artifacts


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

  • Max Fan removed review request for @mfanGitLab

    removed review request for @mfanGitLab

  • Tianwen Chen added 1 commit

    added 1 commit

    • ddfbfe8e - Ensure to recreate the index for bigint column when stepping down

    Compare with previous version

  • Tianwen Chen added 1 commit

    added 1 commit

    • 3c6e3638 - Ensure to recreate the index for bigint column when stepping down

    Compare with previous version

  • Gregory Havenga approved this merge request

    approved this merge request

  • :wave: @ghavenga, thanks for approving this merge request.

    This is the first time the merge request is approved. To ensure full test coverage, a new pipeline will be started shortly.

    For more info, please refer to the following links:

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