Swap todos.note_id to bigint for self-managed instances

What does this MR do and why?

This MR swaps thetodos.note_id and todos.note_id_convert_to_bigint columns for self-managed instances. This was already done for GitLab.com with Swap todos.note_id to bigint (!118123 - merged). The process is outlined here.

The timeline for GitLab.com was the following:

• 15.9 Initialize the conversion of todos.note_id to b... (!110704 - merged)
• 16.0 Swap todos.note_id to bigint (!118123 - merged)
• 16.1 Cleanup PK conversion for few notes related tables (!123108 - merged)

Since we made the decision to commit schema changes to structure.sql for the above MRs, we now need to take into account the state of the schema from which the self-managed instance was created.

We have the following cases:

• Instances created from 15.8 and prior - swap the columns
• Instances created from 15.9, 15.10, and 15.11 - swap the columns
• Instances created from 16.0 - do not swap as the columns are already swapped
• Instances created from 16.1 - do not swap as the columns are already swapped, and the clean up is done (no more temp column and trigger)

Related to Swap integer and bigint columns for notes and d... (#417402 - closed).

How to set up and validate locally

I have done the following manual testing:

1. Create new instance from each of 15.8.0, 15.9.0, 16.0.0, and 16.1.0
2. Upgrade all instances to 16.1.2
3. Execute the migrations from this MR
4. Verify migrations execute successfully and todos table has the expected schema.

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.

changed milestone to %16.2

added Category:Database databaseactive devopsdata stores groupdatabase maintenancescalability priority1 sectioncore platform severity3 typemaintenance workflowin dev labels

assigned to @krasio

added backend database labels

	1 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).

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
backend	Ross Byrne (@robyrne) (UTC+1, 11 hours behind @krasio)	Jessie Young (@jessieay) (UTC+2, 10 hours behind @krasio)
database	Tianwen Chen (@tianwenchen) (UTC+8, 4 hours behind @krasio)	Michał Zając (@Quintasan) (UTC+2, 10 hours behind @krasio)
~"migration"	No reviewer available	No maintainer available

To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. 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 danger-review job that generated this comment.

Generated by Danger

Allure report

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for e001b86a

expand test summary

+-----------------------------------------------------------------------+
|                            suites summary                             |
+------------------+--------+--------+---------+-------+-------+--------+
|                  | passed | failed | skipped | flaky | total | result |
+------------------+--------+--------+---------+-------+-------+--------+
| Data Stores      | 22     | 0      | 0       | 15    | 22    | ❗     |
| Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
| Plan             | 51     | 0      | 0       | 45    | 51    | ❗     |
| Govern           | 21     | 0      | 0       | 18    | 21    | ❗     |
| Manage           | 13     | 0      | 1       | 12    | 14    | ❗     |
| Monitor          | 4      | 0      | 0       | 0     | 4     | ✅     |
| Create           | 27     | 0      | 1       | 20    | 28    | ❗     |
| Verify           | 8      | 0      | 0       | 8     | 8     | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+
| Total            | 146    | 0      | 3       | 118   | 149   | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+

mentioned in issue #417402 (closed)

@stomlinson Can you please have a look at this one and let me know what do you think? If it all seems good, we can un-draft and complete formal review.

requested review from @stomlinson

added workflowin review label and removed workflowin dev label

changed the description

mentioned in issue #389344 (closed)

approved this merge request

added databaseapproved label and removed databaseactive label

@stomlinson, 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:

• rspec predictive jobs
• jest predictive jobs
• merging a merge request.

added pipeline:mr-approved label

resolved all threads

mentioned in commit d660776a

added 1392 commits

• fe998760...969add2b - 1391 commits from branch master
• d660776a - Convert todos.note_id to bigint for self-managed

Compare with previous version

requested review from @dfrazao-gitlab

marked this merge request as ready

mentioned in merge request !126763 (merged)

mentioned in commit b879ee53

added 1 commit

• b879ee53 - Convert todos.note_id to bigint for self-managed

Compare with previous version

mentioned in commit e001b86a

added 318 commits

• b879ee53...4a6c393b - 317 commits from branch master
• e001b86a - Convert todos.note_id to bigint for self-managed

Compare with previous version

resolved all threads

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
20230710024518 - EnsureTodosBigintBackfillCompletedForSelfManaged	Post deploy	2.4 s		+0.00 B
20230710024903 - SwapTodosNoteIdToBigintForSelfManaged	Post deploy	1.8 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	4
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: 20230710024518 - EnsureTodosBigintBackfillCompletedForSelfManaged

• Type: Post deploy
• Duration: 2.4 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 EnsureTodosBigintBackfillCompletedForSelfManaged

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: 20230710024903 - SwapTodosNoteIdToBigintForSelfManaged

• Type: Post deploy
• Duration: 1.8 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 SwapTodosNoteIdToBigintForSelfManaged

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

Other information

No other migrations pending on GitLab.com

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2132548-10649370-main	2023-07-20T10:31:17Z	2023-07-20T07:05:16Z	2023-07-20 22:35:37 +0000
database-testing-2132548-10649370-ci	2023-07-20T10:31:17Z	2023-07-20T08:45:50Z	2023-07-20 22:35:37 +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
20230710024518 - EnsureTodosBigintBackfillCompletedForSelfManaged	Post deploy	3.1 s		+0.00 B
20230710024903 - SwapTodosNoteIdToBigintForSelfManaged	Post deploy	2.6 s		+0.00 B

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	4
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: 20230710024518 - EnsureTodosBigintBackfillCompletedForSelfManaged

• Type: Post deploy
• Duration: 3.1 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 EnsureTodosBigintBackfillCompletedForSelfManaged

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: 20230710024903 - SwapTodosNoteIdToBigintForSelfManaged

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

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

Other information

No other migrations pending on GitLab.com

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2132548-10649370-main	2023-07-20T10:31:17Z	2023-07-20T07:05:16Z	2023-07-20 22:35:37 +0000
database-testing-2132548-10649370-ci	2023-07-20T10:31:17Z	2023-07-20T08:45:50Z	2023-07-20 22:35:37 +0000

Job artifacts

---

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

added database-testing-automation label

approved this merge request

enabled an automatic merge when the pipeline for e0fd984d succeeds

MWPS set

merged

mentioned in commit ef5271b7

added workflowstaging-canary label and removed workflowin review label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

mentioned in issue #419486 (closed)

added workflowpost-deploy-db-staging label and removed workflowproduction label

added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label

added releasedcandidate label

mentioned in merge request !129117 (merged)

mentioned in merge request !128934 (merged)

added releasedpublished label and removed releasedcandidate label

mentioned in merge request kubitus-project/kubitus-installer!2364 (merged)

mentioned in merge request !132928 (merged)

mentioned in issue #438124 (closed)

added groupdatabase frameworks label and removed groupdatabase [DEPRECATED] label