Skip to content
Snippets Groups Projects

Create index asynchronously for system_note_metadata

Merged euko requested to merge 424114-create-bigint-primary-key-index-async into master
All threads resolved!

What does this MR do and why?

Related to #424114 (closed)

system_note_metadata table's primary key column id needs to be converted to use bigint type.

id_convert_to_bigint is the new column that's been backfilled and uses bigint. We need to create a new index on the column. The index will be used to support the new primary key (this step is a part of https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#swap-the-columns-release-n--1). The new index take a long time to create and will need to be prepared asynchronously for .com in this MR.

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
  • euko changed milestone to %16.10

    changed milestone to %16.10

  • euko assigned to @euko

    assigned to @euko

  • A deleted user added databasereview pending label
  • Contributor
    3 Warnings
    :warning: 4d15308a: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
    :warning: New migrations added but db/structure.sql wasn't updated

    Usually, when adding new migrations, db/structure.sql should be
    updated too (unless the migration isn't changing the DB schema
    and isn't the most recent one).

    :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/20240109025151_create_index_on_id_convert_to_bigint_for_system_note_metadata_async.rb
    • db/schema_migrations/20240109025151

    Reviewer roulette

    Changes that require review have been detected!

    Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

    Category Reviewer Maintainer
    database @cablett profile link current availability (UTC+13, 4 hours ahead of author) @jon_jenkins profile link current availability (UTC-6, 15 hours behind author)

    Please check reviewer's status!

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

    Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

    To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

    Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

    If needed, you can retry the :repeat: danger-review job that generated this comment.

    Generated by :no_entry_sign: Danger

  • 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
    20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync Post deploy 1.7 s :white_check_mark: +8.00 KiB [note]
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    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: 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync

    • Type: Post deploy
    • Duration: 1.7 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 5.1 ms 5.1 ms 5.1 ms 1
    INSERT INTO "postgres_async_indexes" ("created_at", "updated_at", "name", "definition", "table_name") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    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
    20240102065444 - RemoveMilestoneIdColumnFromVulnerabilities Post deploy 2.0 s :white_check_mark: -622.51 MiB
    20240104101601 - FinalizeBackfillPartitionIdCiPipelineChatData Post deploy 1.4 s :white_check_mark: +0.00 B
    20240104223119 - AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads Post deploy 418.9 s :white_check_mark: +590.46 MiB
    20240108072319 - AddFkToCiBuildTraceMetadataOnPartitionIdAndTraceArtifactId2 Post deploy 2.2 s :warning: +0.00 B
    20240108072546 - AddFkToCiJobArtifactStatesOnPartitionIdAndJobArtifactId2 Post deploy 1.7 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2723977-12441203-main 2024-01-09T03:41:09Z 2024-01-09T03:10:02Z 2024-01-09 15:53:48 +0000
    database-testing-2723977-12441203-ci 2024-01-09T03:41:09Z 2024-01-09T00:46:17Z 2024-01-09 15:53:48 +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
    20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync Post deploy 2.6 s :white_check_mark: +8.00 KiB [note]
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    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: 20240109025151 - CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync

    • Type: Post deploy
    • Duration: 2.6 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 5.5 ms 5.5 ms 5.5 ms 1
    INSERT INTO "postgres_async_indexes" ("created_at", "updated_at", "name", "definition", "table_name") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateIndexOnIdConvertToBigintForSystemNoteMetadataAsync
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    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
    20240102065444 - RemoveMilestoneIdColumnFromVulnerabilities Post deploy 2.9 s :white_check_mark: -8.00 KiB
    20240104101601 - FinalizeBackfillPartitionIdCiPipelineChatData Post deploy 2.6 s :white_check_mark: +0.00 B
    20240104223119 - AddIndexOwaspTop10WithProjectIdOnVulnerabilityReads Post deploy 3.8 s :white_check_mark: +8.00 KiB [note]
    20240108072319 - AddFkToCiBuildTraceMetadataOnPartitionIdAndTraceArtifactId2 Post deploy 2.8 s :warning: +0.00 B
    20240108072546 - AddFkToCiJobArtifactStatesOnPartitionIdAndJobArtifactId2 Post deploy 2.5 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2723977-12441203-main 2024-01-09T03:41:09Z 2024-01-09T03:10:02Z 2024-01-09 15:53:48 +0000
    database-testing-2723977-12441203-ci 2024-01-09T03:41:09Z 2024-01-09T00:46:17Z 2024-01-09 15:53:48 +0000

    Job artifacts


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

  • euko mentioned in merge request !141331 (merged)

    mentioned in merge request !141331 (merged)

  • euko
  • Contributor

    E2E Test Result Summary

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :white_check_mark: test report for 4d15308a

    expand test summary
    +------------------------------------------------------------------+
    |                          suites summary                          |
    +-------------+--------+--------+---------+-------+-------+--------+
    |             | passed | failed | skipped | flaky | total | result |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Release     | 5      | 0      | 0       | 0     | 5     | ✅     |
    | Create      | 54     | 0      | 7       | 0     | 61    | ✅     |
    | Monitor     | 7      | 0      | 0       | 0     | 7     | ✅     |
    | Plan        | 54     | 0      | 1       | 0     | 55    | ✅     |
    | Package     | 15     | 0      | 1       | 0     | 16    | ✅     |
    | Govern      | 67     | 0      | 0       | 0     | 67    | ✅     |
    | Verify      | 31     | 0      | 0       | 0     | 31    | ✅     |
    | Data Stores | 23     | 0      | 0       | 0     | 23    | ✅     |
    | Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
    | Manage      | 0      | 0      | 1       | 0     | 1     | ➖     |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Total       | 258    | 0      | 10      | 0     | 268   | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+

    e2e-package-and-test: :white_check_mark: test report for 4d15308a

    expand test summary
    +------------------------------------------------------------------+
    |                          suites summary                          |
    +-------------+--------+--------+---------+-------+-------+--------+
    |             | passed | failed | skipped | flaky | total | result |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Plan        | 242    | 0      | 13      | 0     | 255   | ✅     |
    | Create      | 141    | 0      | 14      | 2     | 155   | ✅     |
    | Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
    | Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
    | Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
    | Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Total       | 392    | 0      | 28      | 2     | 420   | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+
  • euko requested review from @jdrpereira

    requested review from @jdrpereira

  • João Pereira approved this merge request

    approved this merge request

  • added databasereviewed label and removed databasereview pending label

  • João Pereira requested review from @jon_jenkins

    requested review from @jon_jenkins

  • Jon Jenkins approved this merge request

    approved this merge request

  • added databaseapproved label and removed databasereviewed label

  • Jon Jenkins resolved all threads

    resolved all threads

  • merged

  • @jon_jenkins, did you forget to run a pipeline before you merged this work? Based on our code review process, if the latest pipeline was created more than 4 hours ago, you should:

    1. Ensure the merge request is not in Draft status.
    2. Start a pipeline (especially important for Community contribution merge requests).
    3. Set the merge request to auto-merge.

    This is a guideline, not a rule. Please consider replying to this comment for transparency.

    This message was generated automatically. You're welcome to improve it.

  • Hello @euko :wave:

    The database team is looking for ways to improve the database review process and we would love your help!

    If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

    @gitlab-org/database-team

    And someone will be by shortly!

    Thanks for your help! :heart:

    This message was generated automatically. You're welcome to improve it.

  • Jon Jenkins mentioned in commit 89446aae

    mentioned in commit 89446aae

  • added workflowstaging label and removed workflowcanary label

  • euko mentioned in merge request !141333 (merged)

    mentioned in merge request !141333 (merged)

  • euko changed milestone to %16.9

    changed milestone to %16.9

  • Please register or sign in to reply
    Loading