Backfill NuGet metadatum normalized version

What does this MR do and why?

In !121260 (merged) we introduced a new normalized_version column in the packages_nuget_metadata table. Now we need to backfill this column for the existing metadatum records using a batched background migration script.

Screenshots or screen recordings

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

Before	After

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.

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

Related to #273697 (closed)

changed milestone to %16.3

added Category:Package Registry NuGet Repository SLOMissed backend customer devopspackage grouppackage registry missed:16.1 priority2 ruby sectionci severity2 typebug workflowin review labels

assigned to @mkhalifa3

removed workflowin review label

added database databasereview pending documentation labels

	2 Warnings
	df551b8b: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	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).

	1 Message
	This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge.

Documentation review

The following files require a review from a technical writer:

• db/docs/batched_background_migrations/backfill_nuget_normalized_version.yml (Link to current live version)

The review does not need to block merging this merge request. See the:

• Metadata for the *.md files that you've changed. The first few lines of each *.md file identify the stage and group most closely associated with your docs change.
• The Technical Writer assigned for that stage and group.
• Documentation workflows for information on when to assign a merge request for review.

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	Smriti Garg (@sgarg_gitlab) (UTC+5.5)	Avielle Wolfe (@avielle) (UTC+2)
database	Dmytro Biryukov (@dbiryukov) (UTC+2)	Prabakaran Murugesan (@praba.m7n) (UTC+2)

Please check reviewer's status!

• Reviewer is available!
• 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 danger-review job that generated this comment.

Generated by Danger

@mkhalifa3 Some end-to-end (E2E) tests have been selected based on the stage label on this MR.

Please start the trigger-omnibus-and-follow-up-e2e job in the qa stage and ensure the tests in follow-up-e2e:package-and-test-ee pipeline are passing before this MR is merged. (The E2E test pipeline is computationally intensive and we cannot afford running it automatically for all pushes/rebases. Therefore, this job must be triggered manually after significant changes at least once.)

If you would like to run all E2E tests, please apply the pipeline:run-all-e2e label and trigger a new pipeline. This will run all tests in e2e:package-and-test pipeline.

The E2E test jobs are allowed to fail due to flakiness. For the list of known failures please refer to the latest pipeline triage issue.

Once done, please apply the emoji on this comment.

For any questions or help in reviewing the E2E test results, please reach out on the internal #quality Slack channel.

added 1 commit

• 9ad5b241 - Merge master branch

Compare with previous version

Allure report

allure-report-publisher generated test report!

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

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 47     | 0      | 0       | 0     | 47    | ✅     |
| Verify      | 8      | 0      | 0       | 0     | 8     | ✅     |
| Data Stores | 20     | 0      | 0       | 0     | 20    | ✅     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
| Create      | 38     | 0      | 0       | 2     | 38    | ❗     |
| Manage      | 12     | 0      | 1       | 0     | 13    | ✅     |
| Govern      | 34     | 0      | 0       | 0     | 34    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 159    | 0      | 2       | 2     | 161   | ❗     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for d293ad93

expand test summary

+-----------------------------------------------------------------------+
|                            suites summary                             |
+------------------+--------+--------+---------+-------+-------+--------+
|                  | passed | failed | skipped | flaky | total | result |
+------------------+--------+--------+---------+-------+-------+--------+
| Package          | 260    | 0      | 21      | 0     | 281   | ✅     |
| Create           | 151    | 0      | 20      | 12    | 171   | ❗     |
| Framework sanity | 0      | 0      | 2       | 0     | 2     | ➖     |
| Monitor          | 8      | 0      | 0       | 0     | 8     | ✅     |
| Data Stores      | 4      | 0      | 0       | 0     | 4     | ✅     |
| Govern           | 4      | 0      | 0       | 0     | 4     | ✅     |
| Manage           | 2      | 0      | 0       | 0     | 2     | ✅     |
| Plan             | 8      | 0      | 0       | 0     | 8     | ✅     |
+------------------+--------+--------+---------+-------+-------+--------+
| Total            | 437    | 0      | 43      | 12    | 480   | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+

Started database testing pipeline (limited access). This comment will be updated once the pipeline has finished running.

added bugfunctional label

removed documentation label

Error: Database testing pipeline failure

Failed pipeline ID: 2193802

The database testing pipeline has failed for pipeline ID 2193802. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

changed title from Support nuget version normalization to Backfill NuGet metadatum normalized version

changed the description

added 808 commits

• 9ad5b241...f29fad1d - 807 commits from branch master
• 7857cf89 - Support nuget version normalization

Compare with previous version

added documentation label

added 1 commit

• d2015e79 - Backfill nuget normalized version

Compare with previous version

added 1 commit

• 2b2b5d1d - Backfill nuget normalized version

Compare with previous version

added 1 commit

• c60e31f5 - Backfill nuget normalized version

Compare with previous version

Error: Database testing pipeline failure

Failed pipeline ID: 2205648

The database testing pipeline has failed for pipeline ID 2205648. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

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
20230811103457 - QueueBackfillNugetNormalizedVersion	Post deploy	3.1 s		+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	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811103457 - QueueBackfillNugetNormalizedVersion

• Type: Post deploy
• Duration: 3.1 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	23.2 ms	23.2 ms	23.2 ms	1	SELECT MAX("package_id") FROM "packages_nuget_metadata"
1	21.3 ms	21.3 ms	21.3 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") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id"
1	5.6 ms	5.6 ms	5.6 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 "batched_background_migrations"."gitlab_schema" = $6 LIMIT $7
1	4.5 ms	4.5 ms	4.5 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 QueueBackfillNugetNormalizedVersion

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	5
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Background Migration: BackfillNugetNormalizedVersion

Sampled 12 batches. Estimated Time to complete: 1 hour and 50 minutes

• Interval: 120s
• Total tuple count: 335283
• Max batch size: 0
• Estimated seconds to complete: 6600s
• Estimated number of batches: 55
• Average batch time: 9.49s
• Batch size: 6000
• N. of batches sampled: 12
• 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
144	42078.3 ms	1539.5 ms	292.2 ms	72000	SELECT "packages_packages"."id", "packages_packages"."version" FROM "packages_packages" WHERE "packages_packages"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250, $251, $252, $253, $254, $255, $256, $257, $258, $259, $260, $261, $262, $263, $264, $265, $266, $267, $268, $269, $270, $271, $272, $273, $274, $275, $276, $277, $278, $279, $280, $281, $282, $283, $284, $285, $286, $287, $288, $289, $290, $291, $292, $293, $294, $295, $296, $297, $298, $299, $300, $301, $302, $303, $304, $305, $306, $307, $308, $309, $310, $311, $312, $313, $314, $315, $316, $317, $318, $319, $320, $321, $322, $323, $324, $325, $326, $327, $328, $329, $330, $331, $332, $333, $334, $335, $336, $337, $338, $339, $340, $341, $342, $343, $344, $345, $346, $347, $348, $349, $350, $351, $352, $353, $354, $355, $356, $357, $358, $359, $360, $361, $362, $363, $364, $365, $366, $367, $368, $369, $370, $371, $372, $373, $374, $375, $376, $377, $378, $379, $380, $381, $382, $383, $384, $385, $386, $387, $388, $389, $390, $391, $392, $393, $394, $395, $396, $397, $398, $399, $400, $401, $402, $403, $404, $405, $406, $407, $408, $409, $410, $411, $412, $413, $414, $415, $416, $417, $418, $419, $420, $421, $422, $423, $424, $425, $426, $427, $428, $429, $430, $431, $432, $433, $434, $435, $436, $437, $438, $439, $440, $441, $442, $443, $444, $445, $446, $447, $448, $449, $450, $451, $452, $453, $454, $455, $456, $457, $458, $459, $460, $461, $462, $463, $464, $465, $466, $467, $468, $469, $470, $471, $472, $473, $474, $475, $476, $477, $478, $479, $480, $481, $482, $483, $484, $485, $486, $487, $488, $489, $490, $491, $492, $493, $494, $495, $496, $497, $498, $499, $500)
144	3077.6 ms	353.6 ms	21.4 ms	72000	UPDATE packages_nuget_metadata SET normalized_version = v.normalized_version FROM ( VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8), ($9, $10), ($11, $12), ($13, $14), ($15, $16), ($17, $18), ($19, $20), ($21, $22), ($23, $24), ($25, $26), ($27, $28), ($29, $30), ($31, $32), ($33, $34), ($35, $36), ($37, $38), ($39, $40), ($41, $42), ($43, $44), ($45, $46), ($47, $48), ($49, $50), ($51, $52), ($53, $54), ($55, $56), ($57, $58), ($59, $60), ($61, $62), ($63, $64), ($65, $66), ($67, $68), ($69, $70), ($71, $72), ($73, $74), ($75, $76), ($77, $78), ($79, $80), ($81, $82), ($83, $84), ($85, $86), ($87, $88), ($89, $90), ($91, $92), ($93, $94), ($95, $96), ($97, $98), ($99, $100), ($101, $102), ($103, $104), ($105, $106), ($107, $108), ($109, $110), ($111, $112), ($113, $114), ($115, $116), ($117, $118), ($119, $120), ($121, $122), ($123, $124), ($125, $126), ($127, $128), ($129, $130), ($131, $132), ($133, $134), ($135, $136), ($137, $138), ($139, $140), ($141, $142), ($143, $144), ($145, $146), ($147, $148), ($149, $150), ($151, $152), ($153, $154), ($155, $156), ($157, $158), ($159, $160), ($161, $162), ($163, $164), ($165, $166), ($167, $168), ($169, $170), ($171, $172), ($173, $174), ($175, $176), ($177, $178), ($179, $180), ($181, $182), ($183, $184), ($185, $186), ($187, $188), ($189, $190), ($191, $192), ($193, $194), ($195, $196), ($197, $198), ($199, $200), ($201, $202), ($203, $204), ($205, $206), ($207, $208), ($209, $210), ($211, $212), ($213, $214), ($215, $216), ($217, $218), ($219, $220), ($221, $222), ($223, $224), ($225, $226), ($227, $228), ($229, $230), ($231, $232), ($233, $234), ($235, $236), ($237, $238), ($239, $240), ($241, $242), ($243, $244), ($245, $246), ($247, $248), ($249, $250), ($251, $252), ($253, $254), ($255, $256), ($257, $258), ($259, $260), ($261, $262), ($263, $264), ($265, $266), ($267, $268), ($269, $270), ($271, $272), ($273, $274), ($275, $276), ($277, $278), ($279, $280), ($281, $282), ($283, $284), ($285, $286), ($287, $288), ($289, $290), ($291, $292), ($293, $294), ($295, $296), ($297, $298), ($299, $300), ($301, $302), ($303, $304), ($305, $306), ($307, $308), ($309, $310), ($311, $312), ($313, $314), ($315, $316), ($317, $318), ($319, $320), ($321, $322), ($323, $324), ($325, $326), ($327, $328), ($329, $330), ($331, $332), ($333, $334), ($335, $336), ($337, $338), ($339, $340), ($341, $342), ($343, $344), ($345, $346), ($347, $348), ($349, $350), ($351, $352), ($353, $354), ($355, $356), ($357, $358), ($359, $360), ($361, $362), ($363, $364), ($365, $366), ($367, $368), ($369, $370), ($371, $372), ($373, $374), ($375, $376), ($377, $378), ($379, $380), ($381, $382), ($383, $384), ($385, $386), ($387, $388), ($389, $390), ($391, $392), ($393, $394), ($395, $396), ($397, $398), ($399, $400), ($401, $402), ($403, $404), ($405, $406), ($407, $408), ($409, $410), ($411, $412), ($413, $414), ($415, $416), ($417, $418), ($419, $420), ($421, $422), ($423, $424), ($425, $426), ($427, $428), ($429, $430), ($431, $432), ($433, $434), ($435, $436), ($437, $438), ($439, $440), ($441, $442), ($443, $444), ($445, $446), ($447, $448), ($449, $450), ($451, $452), ($453, $454), ($455, $456), ($457, $458), ($459, $460), ($461, $462), ($463, $464), ($465, $466), ($467, $468), ($469, $470), ($471, $472), ($473, $474), ($475, $476), ($477, $478), ($479, $480), ($481, $482), ($483, $484), ($485, $486), ($487, $488), ($489, $490), ($491, $492), ($493, $494), ($495, $496), ($497, $498), ($499, $500), ($501, $502), ($503, $504), ($505, $506), ($507, $508), ($509, $510), ($511, $512), ($513, $514), ($515, $516), ($517, $518), ($519, $520), ($521, $522), ($523, $524), ($525, $526), ($527, $528), ($529, $530), ($531, $532), ($533, $534), ($535, $536), ($537, $538), ($539, $540), ($541, $542), ($543, $544), ($545, $546), ($547, $548), ($549, $550), ($551, $552), ($553, $554), ($555, $556), ($557, $558), ($559, $560), ($561, $562), ($563, $564), ($565, $566), ($567, $568), ($569, $570), ($571, $572), ($573, $574), ($575, $576), ($577, $578), ($579, $580), ($581, $582), ($583, $584), ($585, $586), ($587, $588), ($589, $590), ($591, $592), ($593, $594), ($595, $596), ($597, $598), ($599, $600), ($601, $602), ($603, $604), ($605, $606), ($607, $608), ($609, $610), ($611, $612), ($613, $614), ($615, $616), ($617, $618), ($619, $620), ($621, $622), ($623, $624), ($625, $626), ($627, $628), ($629, $630), ($631, $632), ($633, $634), ($635, $636), ($637, $638), ($639, $640), ($641, $642), ($643, $644), ($645, $646), ($647, $648), ($649, $650), ($651, $652), ($653, $654), ($655, $656), ($657, $658), ($659, $660), ($661, $662), ($663, $664), ($665, $666), ($667, $668), ($669, $670), ($671, $672), ($673, $674), ($675, $676), ($677, $678), ($679, $680), ($681, $682), ($683, $684), ($685, $686), ($687, $688), ($689, $690), ($691, $692), ($693, $694), ($695, $696), ($697, $698), ($699, $700), ($701, $702), ($703, $704), ($705, $706), ($707, $708), ($709, $710), ($711, $712), ($713, $714), ($715, $716), ($717, $718), ($719, $720), ($721, $722), ($723, $724), ($725, $726), ($727, $728), ($729, $730), ($731, $732), ($733, $734), ($735, $736), ($737, $738), ($739, $740), ($741, $742), ($743, $744), ($745, $746), ($747, $748), ($749, $750), ($751, $752), ($753, $754), ($755, $756), ($757, $758), ($759, $760), ($761, $762), ($763, $764), ($765, $766), ($767, $768), ($769, $770), ($771, $772), ($773, $774), ($775, $776), ($777, $778), ($779, $780), ($781, $782), ($783, $784), ($785, $786), ($787, $788), ($789, $790), ($791, $792), ($793, $794), ($795, $796), ($797, $798), ($799, $800), ($801, $802), ($803, $804), ($805, $806), ($807, $808), ($809, $810), ($811, $812), ($813, $814), ($815, $816), ($817, $818), ($819, $820), ($821, $822), ($823, $824), ($825, $826), ($827, $828), ($829, $830), ($831, $832), ($833, $834), ($835, $836), ($837, $838), ($839, $840), ($841, $842), ($843, $844), ($845, $846), ($847, $848), ($849, $850), ($851, $852), ($853, $854), ($855, $856), ($857, $858), ($859, $860), ($861, $862), ($863, $864), ($865, $866), ($867, $868), ($869, $870), ($871, $872), ($873, $874), ($875, $876), ($877, $878), ($879, $880), ($881, $882), ($883, $884), ($885, $886), ($887, $888), ($889, $890), ($891, $892), ($893, $894), ($895, $896), ($897, $898), ($899, $900), ($901, $902), ($903, $904), ($905, $906), ($907, $908), ($909, $910), ($911, $912), ($913, $914), ($915, $916), ($917, $918), ($919, $920), ($921, $922), ($923, $924), ($925, $926), ($927, $928), ($929, $930), ($931, $932), ($933, $934), ($935, $936), ($937, $938), ($939, $940), ($941, $942), ($943, $944), ($945, $946), ($947, $948), ($949, $950), ($951, $952), ($953, $954), ($955, $956), ($957, $958), ($959, $960), ($961, $962), ($963, $964), ($965, $966), ($967, $968), ($969, $970), ($971, $972), ($973, $974), ($975, $976), ($977, $978), ($979, $980), ($981, $982), ($983, $984), ($985, $986), ($987, $988), ($989, $990), ($991, $992), ($993, $994), ($995, $996), ($997, $998), ($999, $1000) ) AS v(package_id, normalized_version) WHERE packages_nuget_metadata.package_id = v.package_id
24	16.9 ms	13.3 ms	0.7 ms	24	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"
132	87.5 ms	4.4 ms	0.7 ms	66000	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3 AND "packages_nuget_metadata"."package_id" < $4
144	143.0 ms	4.2 ms	1.0 ms	132	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3 ORDER BY "packages_nuget_metadata"."package_id" ASC LIMIT $4 OFFSET $5
12	9.3 ms	2.7 ms	0.8 ms	12	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "started_at" = $2, "status" = $3, "attempts" = $4 WHERE "batched_background_migration_jobs"."id" = $5
12	5.4 ms	2.0 ms	0.5 ms	12	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "finished_at" = $2, "status" = $3, "metrics" = $4 WHERE "batched_background_migration_jobs"."id" = $5
12	6.8 ms	0.9 ms	0.6 ms	6000	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3
12	0.8 ms	0.1 ms	0.1 ms	12	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))
24	1.1 ms	0.1 ms	0.0 ms	24	SELECT "batched_background_migration_jobs".* FROM "batched_background_migration_jobs" WHERE "batched_background_migration_jobs"."id" = $1 LIMIT $2
12	0.5 ms	0.1 ms	0.0 ms	12	SELECT "batched_background_migrations".* FROM "batched_background_migrations" WHERE "batched_background_migrations"."id" = $1 LIMIT $2
12	0.5 ms	0.1 ms	0.0 ms	12	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL ORDER BY "packages_nuget_metadata"."package_id" ASC LIMIT $3

Histogram of batch runtimes for BackfillNugetNormalizedVersion

Batch Runtime	Count
0 seconds - 10 seconds	6
10 seconds - 1 minute	6
1 minute - 2 minutes	0
2 minutes - 3 minutes	0
3 minutes - 5 minutes	0
5 minutes +	0

Histogram across all sampled batches of BackfillNugetNormalizedVersion

Query Runtime	Count
0 seconds - 0.1 seconds	575
0.1 seconds - 0.5 seconds	77
0.5 seconds - 1 second	24
1 second - 2 seconds	8
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-2216672-10904620-main	2023-08-15T10:04:40Z	2023-08-15T08:10:42Z	2023-08-15 22:11:14 +0000
database-testing-2216672-10904620-ci	2023-08-15T10:04:40Z	2023-08-12T15:50:05Z	2023-08-15 22:11:14 +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
20230811103457 - QueueBackfillNugetNormalizedVersion	Post deploy	2.5 s		+0.00 B

Runtime Histogram for all migrations

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: 20230811103457 - QueueBackfillNugetNormalizedVersion

• Type: Post deploy
• Duration: 2.5 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 QueueBackfillNugetNormalizedVersion

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

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230728171609 - AddCiJobAnnotationsPlanLimits	Regular	3.1 s		+0.00 B
20230701053315 - EnsureAgainBackfillForCiPipelineVariablesPipelineIdIsFinished	Post deploy	2.7 s		+0.00 B
20230712052619 - DropIndexDeploymentsOnProjectIdAndStatus	Post deploy	4.3 s		+0.00 B
20230712054057 - DropIndexDeploymentsOnProjectIdSha	Post deploy	3.5 s		+0.00 B
20230712055956 - DropIndexDeploymentsOnEnvironmentIdAndIidAndProjectId	Post deploy	3.7 s		+0.00 B
20230726024322 - AddNotValidForeignKeyForCiPipelineVariablesPipelineId	Post deploy	4.4 s		+8.00 KiB [note]
20230727102936 - DropPreparedAtIndex	Post deploy	4.0 s		+8.00 KiB [note]
20230727103144 - AddPreparedAtCreatedAtIndex	Post deploy	4.0 s		+0.00 B
20230727132342 - PrepareIndexOnVulnerabilityOccurrencesUuidAsync	Post deploy	3.0 s		+0.00 B
20230728122928 - PrepareIndexOnVulnerabilityOccurrencesUuidIncludingVulnerabilityIdAsync	Post deploy	2.7 s		+0.00 B
20230804121704 - RemoveNamespacesUserDetailsEnterpriseGroupIdFk	Post deploy	2.9 s		+0.00 B
20230804121705 - RemoveNamespacesUserDetailsProvisionedByGroupIdFk	Post deploy	2.9 s		+0.00 B
20230809090349 - EnsureIdUniquenessForPCiBuildsV2	Post deploy	3.0 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2216672-10904620-main	2023-08-15T10:04:40Z	2023-08-15T08:10:42Z	2023-08-15 22:11:14 +0000
database-testing-2216672-10904620-ci	2023-08-15T10:04:40Z	2023-08-12T15:50:05Z	2023-08-15 22:11:14 +0000

Job artifacts

---

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

added 1 commit

• b63c5c2a - Update backfill script

Compare with previous version

mentioned in issue #273697 (closed)

added 52 commits

• b63c5c2a...b521c938 - 50 commits from branch master
• 0b0d23a8 - Add support for nuget normalized version
• f848e6ef - Merge branch '273697-cleanup-nuget_normalized_version-feature-flag' into...

Compare with previous version

mentioned in commit gitlab-org-sandbox/gitlab-jh-validation@492d99a2

added database-testing-automation label

added 1 commit

• 22593deb - Merge branch '273697-cleanup-nuget_normalized_version-feature-flag' into...

Compare with previous version

mentioned in commit gitlab-org-sandbox/gitlab-jh-validation@3b6ed932

added 1 commit

• e5adbd1e - Merge branch '273697-cleanup-nuget_normalized_version-feature-flag' into...

Compare with previous version

mentioned in commit gitlab-org-sandbox/gitlab-jh-validation@26b6e36d

added 1 commit

• d293ad93 - Merge branch '273697-cleanup-nuget_normalized_version-feature-flag' into...

Compare with previous version

mentioned in commit gitlab-org-sandbox/gitlab-jh-validation@3fcea4ca

added 974 commits

• d293ad93...806d8f03 - 972 commits from branch master
• 46084d72 - Backfill nuget normalized version
• 844ea71d - Update backfill script

Compare with previous version

added 3 commits

• c60e31f5 - Backfill nuget normalized version
• b63c5c2a - Update backfill script
• d293ad93 - Merge branch '273697-cleanup-nuget_normalized_version-feature-flag' into...

Compare with previous version

changed milestone to %16.4

added 1150 commits

• d293ad93...171e6985 - 1149 commits from branch master
• 4449a60c - Backfill nuget normalized version

Compare with previous version

Error: Database testing pipeline failure

Failed pipeline ID: 2233122

The database testing pipeline has failed for pipeline ID 2233122. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

Error: Database testing pipeline failure

Failed pipeline ID: 2235268

The database testing pipeline has failed for pipeline ID 2235268. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

added 69 commits

• 4449a60c...50026d7e - 68 commits from branch master
• 23b56fc6 - Backfill nuget normalized version

Compare with previous version

Error: Database testing pipeline failure

Failed pipeline ID: 2235889

The database testing pipeline has failed for pipeline ID 2235889. Please reach out in #database or to a database maintainer to help troubleshoot.

Why aren't details of the failure posted here?

Because migration testing pipelines use production data, and this merge request is public. Production data may contain sensitive information, so we only expose this data in job artifacts and output from the pipeline itself.

Why don't I have access to this pipeline?

Because migration testing pipelines use production data, sensitive information could be leaked in job logs. As a result, the reports are limited to:

• Members of the database group
• Database maintainers
• GitLab team members with an approved access request for Reporter access to https://ops.gitlab.net/gitlab-com/database-team/gitlab-com-database-testing/.

Can I just re-run the pipeline?

Possibly yes, if the errors were caused by an incident (or services being slow) and re-running could fix it. If you're doing something slow, remember:

• Migration testing jobs time out after 10 hours. Synchronous work on a big table can reach this limit, and re-running will mean you wait another 10 hours for it.
• Migration sampling runs 30 minutes per background migration. If you add 20 background migrations, you will hit the timeout. Consider breaking multiple background migrations apart into separate merge requests.

If you think re-running might fix it, re-trigger the pipeline by running the manual job db:gitlabcom-database-testing.

requested review from @praba.m7n and @dbiryukov

Hi @praba.m7n Can you please do the backend review if you have the capacity?

Hi @dbiryukov Can you please do the database review if you have the capacity?

Thanks both in advance

@mkhalifa3 Everything looks wonderful to me.

I left only one comment about the normalization process through model validation.

Please check and let me know what you think.

removed review request for @dbiryukov

requested review from @dbiryukov

approved this merge request

added databasereviewed label and removed databasereview pending label

requested review from @sabrams and removed review request for @dbiryukov

@dbiryukov, 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

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
20230811103457 - QueueBackfillNugetNormalizedVersion	Post deploy	3.2 s		+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	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20230811103457 - QueueBackfillNugetNormalizedVersion

• Type: Post deploy
• Duration: 3.2 s
• Database size change: +0.00 B

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	21.9 ms	21.9 ms	21.9 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") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13) RETURNING "id"
1	18.5 ms	18.5 ms	18.5 ms	1	SELECT MAX("package_id") FROM "packages_nuget_metadata"
1	1.7 ms	1.7 ms	1.7 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
1	1.5 ms	1.5 ms	1.5 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 "batched_background_migrations"."gitlab_schema" = $6 LIMIT $7
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for QueueBackfillNugetNormalizedVersion

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	5
0.1 seconds - 1 second	1
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Background Migration: BackfillUserPreferencesWithDefaults

Sampled 74 batches. Estimated Time to complete: 5 days, 5 hours, and 6 minutes

• Interval: 120s
• Total tuple count: 11260365
• Max batch size: 10000
• Estimated seconds to complete: 450360s
• Estimated number of batches: 3753
• Average batch time: 11.35s
• Batch size: 3000
• N. of batches sampled: 74
• 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
1036	87269.2 ms	1736.7 ms	84.2 ms	65884	UPDATE "user_preferences" SET "render_whitespace_in_code" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."id" < $5 AND "user_preferences"."render_whitespace_in_code" IS NULL
74	6279.4 ms	1654.8 ms	84.9 ms	4706	UPDATE "user_preferences" SET "render_whitespace_in_code" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."render_whitespace_in_code" IS NULL
1036	113427.6 ms	1595.9 ms	109.5 ms	38239	UPDATE "user_preferences" SET "time_display_relative" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."id" < $5 AND "user_preferences"."time_display_relative" IS NULL
74	8557.1 ms	1195.3 ms	115.6 ms	2731	UPDATE "user_preferences" SET "time_display_relative" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."time_display_relative" IS NULL
1036	143772.5 ms	1041.8 ms	138.8 ms	68313	UPDATE "user_preferences" SET "tab_width" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."id" < $5 AND "user_preferences"."tab_width" IS NULL
74	10419.0 ms	1026.7 ms	140.8 ms	4877	UPDATE "user_preferences" SET "tab_width" = $1 WHERE "user_preferences"."id" BETWEEN $2 AND $3 AND "user_preferences"."id" >= $4 AND "user_preferences"."tab_width" IS NULL
74	211.4 ms	38.2 ms	2.9 ms	74	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "finished_at" = $2, "status" = $3, "metrics" = $4 WHERE "batched_background_migration_jobs"."id" = $5
74	106.4 ms	18.7 ms	1.4 ms	74	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "started_at" = $2, "status" = $3, "attempts" = $4 WHERE "batched_background_migration_jobs"."id" = $5
1110	374.9 ms	9.5 ms	0.3 ms	1036	SELECT "user_preferences"."id" FROM "user_preferences" WHERE "user_preferences"."id" BETWEEN $1 AND $2 AND "user_preferences"."id" >= $3 ORDER BY "user_preferences"."id" ASC LIMIT $4 OFFSET $5
148	37.6 ms	5.2 ms	0.3 ms	148	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"
74	17.1 ms	2.7 ms	0.2 ms	74	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))
74	3.7 ms	0.2 ms	0.0 ms	74	SELECT "batched_background_migrations".* FROM "batched_background_migrations" WHERE "batched_background_migrations"."id" = $1 LIMIT $2
148	9.3 ms	0.1 ms	0.1 ms	148	SELECT "batched_background_migration_jobs".* FROM "batched_background_migration_jobs" WHERE "batched_background_migration_jobs"."id" = $1 LIMIT $2
74	3.6 ms	0.1 ms	0.0 ms	74	SELECT "user_preferences"."id" FROM "user_preferences" WHERE "user_preferences"."id" BETWEEN $1 AND $2 ORDER BY "user_preferences"."id" ASC LIMIT $3

Histogram of batch runtimes for BackfillUserPreferencesWithDefaults

Batch Runtime	Count
0 seconds - 10 seconds	47
10 seconds - 1 minute	27
1 minute - 2 minutes	0
2 minutes - 3 minutes	0
3 minutes - 5 minutes	0
5 minutes +	0

Histogram across all sampled batches of BackfillUserPreferencesWithDefaults

Query Runtime	Count
0 seconds - 0.1 seconds	3927
0.1 seconds - 0.5 seconds	928
0.5 seconds - 1 second	212
1 second - 2 seconds	39
2 seconds - 5 seconds	0
5 seconds +	0

Background Migration: BackfillNugetNormalizedVersion

Sampled 16 batches. Estimated Time to complete: 1 hour and 56 minutes

• Interval: 120s
• Total tuple count: 348706
• Max batch size: 0
• Estimated seconds to complete: 6960s
• Estimated number of batches: 58
• Average batch time: 24.9s
• Batch size: 6000
• N. of batches sampled: 16
• 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
384	197571.2 ms	6850.8 ms	514.5 ms	96000	SELECT "packages_packages"."id", "packages_packages"."version" FROM "packages_packages" WHERE "packages_packages"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250)
16	911.4 ms	854.3 ms	57.0 ms	16	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "finished_at" = $2, "status" = $3, "metrics" = $4 WHERE "batched_background_migration_jobs"."id" = $5
384	4225.3 ms	221.3 ms	11.0 ms	96000	UPDATE packages_nuget_metadata SET normalized_version = v.normalized_version FROM ( VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8), ($9, $10), ($11, $12), ($13, $14), ($15, $16), ($17, $18), ($19, $20), ($21, $22), ($23, $24), ($25, $26), ($27, $28), ($29, $30), ($31, $32), ($33, $34), ($35, $36), ($37, $38), ($39, $40), ($41, $42), ($43, $44), ($45, $46), ($47, $48), ($49, $50), ($51, $52), ($53, $54), ($55, $56), ($57, $58), ($59, $60), ($61, $62), ($63, $64), ($65, $66), ($67, $68), ($69, $70), ($71, $72), ($73, $74), ($75, $76), ($77, $78), ($79, $80), ($81, $82), ($83, $84), ($85, $86), ($87, $88), ($89, $90), ($91, $92), ($93, $94), ($95, $96), ($97, $98), ($99, $100), ($101, $102), ($103, $104), ($105, $106), ($107, $108), ($109, $110), ($111, $112), ($113, $114), ($115, $116), ($117, $118), ($119, $120), ($121, $122), ($123, $124), ($125, $126), ($127, $128), ($129, $130), ($131, $132), ($133, $134), ($135, $136), ($137, $138), ($139, $140), ($141, $142), ($143, $144), ($145, $146), ($147, $148), ($149, $150), ($151, $152), ($153, $154), ($155, $156), ($157, $158), ($159, $160), ($161, $162), ($163, $164), ($165, $166), ($167, $168), ($169, $170), ($171, $172), ($173, $174), ($175, $176), ($177, $178), ($179, $180), ($181, $182), ($183, $184), ($185, $186), ($187, $188), ($189, $190), ($191, $192), ($193, $194), ($195, $196), ($197, $198), ($199, $200), ($201, $202), ($203, $204), ($205, $206), ($207, $208), ($209, $210), ($211, $212), ($213, $214), ($215, $216), ($217, $218), ($219, $220), ($221, $222), ($223, $224), ($225, $226), ($227, $228), ($229, $230), ($231, $232), ($233, $234), ($235, $236), ($237, $238), ($239, $240), ($241, $242), ($243, $244), ($245, $246), ($247, $248), ($249, $250), ($251, $252), ($253, $254), ($255, $256), ($257, $258), ($259, $260), ($261, $262), ($263, $264), ($265, $266), ($267, $268), ($269, $270), ($271, $272), ($273, $274), ($275, $276), ($277, $278), ($279, $280), ($281, $282), ($283, $284), ($285, $286), ($287, $288), ($289, $290), ($291, $292), ($293, $294), ($295, $296), ($297, $298), ($299, $300), ($301, $302), ($303, $304), ($305, $306), ($307, $308), ($309, $310), ($311, $312), ($313, $314), ($315, $316), ($317, $318), ($319, $320), ($321, $322), ($323, $324), ($325, $326), ($327, $328), ($329, $330), ($331, $332), ($333, $334), ($335, $336), ($337, $338), ($339, $340), ($341, $342), ($343, $344), ($345, $346), ($347, $348), ($349, $350), ($351, $352), ($353, $354), ($355, $356), ($357, $358), ($359, $360), ($361, $362), ($363, $364), ($365, $366), ($367, $368), ($369, $370), ($371, $372), ($373, $374), ($375, $376), ($377, $378), ($379, $380), ($381, $382), ($383, $384), ($385, $386), ($387, $388), ($389, $390), ($391, $392), ($393, $394), ($395, $396), ($397, $398), ($399, $400), ($401, $402), ($403, $404), ($405, $406), ($407, $408), ($409, $410), ($411, $412), ($413, $414), ($415, $416), ($417, $418), ($419, $420), ($421, $422), ($423, $424), ($425, $426), ($427, $428), ($429, $430), ($431, $432), ($433, $434), ($435, $436), ($437, $438), ($439, $440), ($441, $442), ($443, $444), ($445, $446), ($447, $448), ($449, $450), ($451, $452), ($453, $454), ($455, $456), ($457, $458), ($459, $460), ($461, $462), ($463, $464), ($465, $466), ($467, $468), ($469, $470), ($471, $472), ($473, $474), ($475, $476), ($477, $478), ($479, $480), ($481, $482), ($483, $484), ($485, $486), ($487, $488), ($489, $490), ($491, $492), ($493, $494), ($495, $496), ($497, $498), ($499, $500) ) AS v(package_id, normalized_version) WHERE packages_nuget_metadata.package_id = v.package_id
32	60.5 ms	43.8 ms	1.9 ms	32	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"
384	366.8 ms	10.1 ms	1.0 ms	368	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3 ORDER BY "packages_nuget_metadata"."package_id" ASC LIMIT $4 OFFSET $5
16	20.9 ms	8.8 ms	1.3 ms	16	UPDATE "batched_background_migration_jobs" SET "updated_at" = $1, "started_at" = $2, "status" = $3, "attempts" = $4 WHERE "batched_background_migration_jobs"."id" = $5
368	178.8 ms	6.2 ms	0.5 ms	92000	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3 AND "packages_nuget_metadata"."package_id" < $4
16	6.7 ms	5.0 ms	0.4 ms	16	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))
16	7.0 ms	1.1 ms	0.4 ms	4000	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL AND "packages_nuget_metadata"."package_id" >= $3
32	1.9 ms	0.1 ms	0.1 ms	32	SELECT "batched_background_migration_jobs".* FROM "batched_background_migration_jobs" WHERE "batched_background_migration_jobs"."id" = $1 LIMIT $2
16	0.7 ms	0.1 ms	0.0 ms	16	SELECT "packages_nuget_metadata"."package_id" FROM "packages_nuget_metadata" WHERE "packages_nuget_metadata"."package_id" BETWEEN $1 AND $2 AND "packages_nuget_metadata"."normalized_version" IS NULL ORDER BY "packages_nuget_metadata"."package_id" ASC LIMIT $3
16	0.8 ms	0.1 ms	0.0 ms	16	SELECT "batched_background_migrations".* FROM "batched_background_migrations" WHERE "batched_background_migrations"."id" = $1 LIMIT $2

Histogram of batch runtimes for BackfillNugetNormalizedVersion

Batch Runtime	Count
0 seconds - 10 seconds	0
10 seconds - 1 minute	15
1 minute - 2 minutes	1
2 minutes - 3 minutes	0
3 minutes - 5 minutes	0
5 minutes +	0

Histogram across all sampled batches of BackfillNugetNormalizedVersion

Query Runtime	Count
0 seconds - 0.1 seconds	1252
0.1 seconds - 0.5 seconds	225
0.5 seconds - 1 second	176
1 second - 2 seconds	25
2 seconds - 5 seconds	1
5 seconds +	1

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230818085219 - QueueBackfillUserPreferencesWithDefaults	Post deploy	2.5 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2244675-10995660-main	2023-08-23T17:49:56Z	2023-08-23T15:18:14Z	2023-08-24 06:16:18 +0000
database-testing-2244675-10995660-ci	2023-08-23T17:49:56Z	2023-08-23T16:10:52Z	2023-08-24 06:16:18 +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
20230811103457 - QueueBackfillNugetNormalizedVersion	Post deploy	3.0 s		+0.00 B

Runtime Histogram for all migrations

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: 20230811103457 - QueueBackfillNugetNormalizedVersion

• Type: Post deploy
• Duration: 3.0 s
• Database size change: +0.00 B

Histogram for QueueBackfillNugetNormalizedVersion

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

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230818085219 - QueueBackfillUserPreferencesWithDefaults	Post deploy	2.5 s		+0.00 B

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2244675-10995660-main	2023-08-23T17:49:56Z	2023-08-23T15:18:14Z	2023-08-24 06:16:18 +0000
database-testing-2244675-10995660-ci	2023-08-23T17:49:56Z	2023-08-23T16:10:52Z	2023-08-24 06:16:18 +0000

Job artifacts

---

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

removed review request for @sabrams

added 2 commits

• 4449a60c - Backfill nuget normalized version
• a8ca1088 - Merge branch '273697-backfill-nuget-metadata-normalized-version' of...

Compare with previous version

added 409 commits

• a8ca1088...b617e934 - 408 commits from branch master
• 956f0833 - Backfill nuget normalized version

Compare with previous version

added 5 commits

• 956f0833...4ba2a7d7 - 4 commits from branch master
• b21b1ee8 - Backfill nuget normalized version

Compare with previous version

requested review from @sabrams

added workflowin review label

approved this merge request

added databaseapproved label and removed databasereviewed label

removed review request for @sabrams

added 1 commit

• 58d1e773 - Backfill nuget normalized version

Compare with previous version

added 1 commit

• df551b8b - Backfill nuget normalized version

Compare with previous version

requested review from @lauraX and removed review request for @praba.m7n

approved this merge request

approved this merge request

resolved all threads

enabled an automatic merge when the pipeline for 1c927a93 succeeds

merged

Hello @mkhalifa3

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!

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

mentioned in commit 3a870228

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

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

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

mentioned in issue #270976 (closed)

added releasedcandidate label

added releasedpublished label and removed releasedcandidate label

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

added finished_batched_background_migration label

added background-migrationfinished label

removed finished_batched_background_migration label

The BackfillNugetNormalizedVersion batched background migration is finalized!

This is an auto-generated comment from gitlab-com-database-testing/-/merge_requests/201.

added background-migrationfinalized label and removed background-migrationfinished label

added pipelinetier-3 label