Backfill namespace_id on issues table
What does this MR do and why?
This MR handles backfilling issues.namespace_id = issues.project.project_namespace_id. This is a first part of the initiative to add work items to group level.
Before we can get work items to group level, we need to make sure current issues can work with project namespace first. So we we want to backfill issues.namespace_id and make sure we do not miss any instances where namespace_id might be missing.
Steps
- set namespace_id on issue save, !91387 (merged)
- Backfill namespace_id, this MR
👈 - Finalize backfilling and introduce not null constraint on
issues.namespace_id
This MR is to be merged a week or two after !91387 (merged) is deployed on prod and we can check that all newly created and updated issues set namespace_id correctly.
Screenshots or screen recordings
Batched jobs duration
local batched background migration jobs durations
Most jobs take under 2 minutes locally.
gitlabhq_dblab=# select row_number() over() as nr, id, started_at, finished_at, finished_at - started_at as duration, min_value, max_value, batch_size, sub_batch_size, status from batched_background_migration_jobs where batched_background_migration_id = 207;
nr | id | started_at | finished_at | duration | min_value | max_value | batch_size | sub_batch_size | status
----+--------+-------------------------------+-------------------------------+-----------------+-----------+-----------+------------+----------------+--------
1 | 111455 | 2022-07-14 11:33:59.314237+00 | 2022-07-14 11:35:01.03716+00 | 00:01:01.722923 | 4 | 1987 | 1000 | 10 | 3
2 | 111456 | 2022-07-14 11:35:04.219174+00 | 2022-07-14 11:36:06.96534+00 | 00:01:02.746166 | 1988 | 3586 | 1000 | 10 | 3
3 | 111457 | 2022-07-14 11:36:09.716324+00 | 2022-07-14 11:37:11.827472+00 | 00:01:02.111148 | 3587 | 4977 | 1000 | 10 | 3
4 | 111458 | 2022-07-14 11:37:14.966077+00 | 2022-07-14 11:38:10.967374+00 | 00:00:56.001297 | 4978 | 6390 | 1000 | 10 | 3
5 | 111459 | 2022-07-14 11:38:13.929449+00 | 2022-07-14 11:40:32.868075+00 | 00:02:18.938626 | 6391 | 7797 | 1000 | 10 | 3
6 | 111460 | 2022-07-14 11:40:35.607336+00 | 2022-07-14 11:41:34.62479+00 | 00:00:59.017454 | 7798 | 9377 | 1000 | 10 | 3
7 | 111461 | 2022-07-14 11:41:37.431425+00 | 2022-07-14 11:42:36.434744+00 | 00:00:59.003319 | 9379 | 10583 | 1000 | 10 | 3
8 | 111462 | 2022-07-14 11:42:39.314034+00 | 2022-07-14 11:43:36.224108+00 | 00:00:56.910074 | 10584 | 11792 | 1000 | 10 | 3
9 | 111463 | 2022-07-14 11:43:38.958961+00 | 2022-07-14 11:44:35.554576+00 | 00:00:56.595615 | 11793 | 13076 | 1000 | 10 | 3
10 | 111464 | 2022-07-14 11:44:38.433336+00 | 2022-07-14 11:45:37.196379+00 | 00:00:58.763043 | 13077 | 14275 | 1000 | 10 | 3
11 | 111465 | 2022-07-14 11:45:40.116927+00 | 2022-07-14 11:46:39.47072+00 | 00:00:59.353793 | 14276 | 15625 | 1000 | 10 | 3
12 | 111466 | 2022-07-14 11:46:42.311588+00 | 2022-07-14 11:47:42.67725+00 | 00:01:00.365662 | 15626 | 16957 | 1000 | 10 | 3
13 | 111467 | 2022-07-14 11:47:45.479687+00 | 2022-07-14 11:48:44.010696+00 | 00:00:58.531009 | 16958 | 18226 | 1000 | 10 | 3
14 | 111468 | 2022-07-14 11:48:46.809218+00 | 2022-07-14 11:49:45.3795+00 | 00:00:58.570282 | 18227 | 19479 | 1000 | 10 | 3
15 | 111469 | 2022-07-14 11:49:48.229669+00 | 2022-07-14 11:50:48.178005+00 | 00:00:59.948336 | 19481 | 20726 | 1000 | 10 | 3
16 | 111470 | 2022-07-14 11:50:50.995077+00 | 2022-07-14 11:51:52.567886+00 | 00:01:01.572809 | 20727 | 21991 | 1000 | 10 | 3
17 | 111471 | 2022-07-14 11:51:55.587581+00 | 2022-07-14 11:52:54.533628+00 | 00:00:58.946047 | 21992 | 23166 | 1000 | 10 | 3
18 | 111472 | 2022-07-14 11:52:57.204056+00 | 2022-07-14 11:53:54.539679+00 | 00:00:57.335623 | 23167 | 25129 | 1000 | 10 | 3
19 | 111473 | 2022-07-14 11:53:57.253135+00 | 2022-07-14 11:54:57.290407+00 | 00:01:00.037272 | 25130 | 26280 | 1000 | 10 | 3
20 | 111474 | 2022-07-14 11:55:00.002358+00 | 2022-07-14 11:55:53.73568+00 | 00:00:53.733322 | 26283 | 27610 | 1000 | 10 | 3
21 | 111475 | 2022-07-14 11:55:58.488705+00 | 2022-07-14 11:57:05.217726+00 | 00:01:06.729021 | 27611 | 29281 | 1200 | 10 | 3
22 | 111476 | 2022-07-14 11:57:11.083733+00 | 2022-07-14 11:58:35.552816+00 | 00:01:24.469083 | 29282 | 31146 | 1440 | 10 | 3
23 | 111477 | 2022-07-14 11:58:40.368502+00 | 2022-07-14 12:00:21.619068+00 | 00:01:41.250566 | 31147 | 33343 | 1728 | 10 | 3
24 | 111478 | 2022-07-14 12:00:26.039415+00 | 2022-07-14 12:02:24.755435+00 | 00:01:58.71602 | 33344 | 35793 | 2073 | 10 | 3
25 | 111479 | 2022-07-14 12:02:29.558795+00 | 2022-07-14 12:04:43.218006+00 | 00:02:13.659211 | 35794 | 38505 | 2431 | 10 | 3
26 | 111480 | 2022-07-14 12:04:47.242994+00 | 2022-07-14 12:07:06.137202+00 | 00:02:18.894208 | 38506 | 41600 | 2431 | 10 | 3
27 | 111481 | 2022-07-14 12:07:10.782517+00 | 2022-07-14 12:09:32.854383+00 | 00:02:22.071866 | 41601 | 44938 | 2264 | 10 | 3
28 | 111482 | 2022-07-14 12:09:37.420685+00 | 2022-07-14 12:11:27.766245+00 | 00:01:50.34556 | 44939 | 46924 | 1973 | 10 | 3
29 | 111483 | 2022-07-14 12:11:32.183084+00 | 2022-07-14 12:13:08.903907+00 | 00:01:36.720823 | 46925 | 51212 | 1837 | 10 | 3
30 | 111484 | 2022-07-14 12:13:12.720524+00 | 2022-07-14 12:15:11.466369+00 | 00:01:58.745845 | 51213 | 53431 | 1837 | 10 | 3
31 | 111485 | 2022-07-14 12:15:16.136025+00 | 2022-07-14 12:17:35.401755+00 | 00:02:19.26573 | 53432 | 55548 | 1817 | 10 | 3
32 | 111486 | 2022-07-14 12:17:40.338808+00 | 2022-07-14 12:19:50.742306+00 | 00:02:10.403498 | 55549 | 57558 | 1659 | 10 | 3
33 | 111487 | 2022-07-14 12:19:55.384482+00 | 2022-07-14 12:21:52.995+00 | 00:01:57.610518 | 57563 | 59323 | 1486 | 10 | 3
34 | 111488 | 2022-07-14 12:21:57.745181+00 | 2022-07-14 12:25:06.812414+00 | 00:03:09.067233 | 59324 | 60709 | 1370 | 10 | 3
35 | 111489 | 2022-07-14 12:25:11.449576+00 | 2022-07-14 12:27:07.792744+00 | 00:01:56.343168 | 60710 | 61811 | 1041 | 10 | 3
36 | 111490 | 2022-07-14 12:27:12.439035+00 | 2022-07-14 12:29:08.421948+00 | 00:01:55.982913 | 61812 | 62966 | 1000 | 10 | 3
37 | 111491 | 2022-07-14 12:29:12.883466+00 | 2022-07-14 12:32:00.768693+00 | 00:02:47.885227 | 62967 | 63968 | 1000 | 10 | 3
38 | 111492 | 2022-07-14 12:32:05.381799+00 | 2022-07-14 12:34:20.330275+00 | 00:02:14.948476 | 63969 | 65092 | 1000 | 10 | 3
39 | 111493 | 2022-07-14 12:34:25.597099+00 | 2022-07-14 12:35:51.630851+00 | 00:01:26.033752 | 65093 | 66411 | 1000 | 10 | 3
40 | 111494 | 2022-07-14 12:35:56.120826+00 | 2022-07-14 12:37:29.719906+00 | 00:01:33.59908 | 66412 | 67658 | 1000 | 10 | 3
41 | 111495 | 2022-07-14 12:37:33.715555+00 | 2022-07-14 12:39:01.1906+00 | 00:01:27.475045 | 67659 | 68866 | 1000 | 10 | 3
42 | 111496 | 2022-07-14 12:39:06.087308+00 | 2022-07-14 12:40:33.401737+00 | 00:01:27.314429 | 68867 | 70257 | 1144 | 10 | 3
43 | 111497 | 2022-07-14 12:40:38.24182+00 | 2022-07-14 12:42:31.320986+00 | 00:01:53.079166 | 70258 | 71922 | 1372 | 10 | 3
44 | 111498 | 2022-07-14 12:42:35.974523+00 | 2022-07-14 12:44:37.128395+00 | 00:02:01.153872 | 71923 | 74242 | 1533 | 10 | 3
45 | 111499 | 2022-07-14 12:44:42.160752+00 | 2022-07-14 12:46:42.7409+00 | 00:02:00.580148 | 74243 | 76085 | 1533 | 10 | 3
46 | 111500 | 2022-07-14 12:46:47.056113+00 | 2022-07-14 12:49:23.8276+00 | 00:02:36.771487 | 76086 | 77831 | 1533 | 10 | 3
47 | 111501 | 2022-07-14 12:49:28.331616+00 | 2022-07-14 12:51:00.918106+00 | 00:01:32.58649 | 77832 | 79432 | 1336 | 10 | 3
48 | 111502 | 2022-07-14 12:51:05.507791+00 | 2022-07-14 12:53:33.715281+00 | 00:02:28.20749 | 79433 | 80935 | 1322 | 10 | 3
49 | 111503 | 2022-07-14 12:53:38.074526+00 | 2022-07-14 12:55:20.571056+00 | 00:01:42.49653 | 80936 | 82634 | 1173 | 10 | 3
50 | 111504 | 2022-07-14 12:55:25.045738+00 | 2022-07-14 12:57:08.836994+00 | 00:01:43.791256 | 82635 | 83941 | 1125 | 10 | 3
51 | 111505 | 2022-07-14 12:57:13.569178+00 | 2022-07-14 12:58:32.56122+00 | 00:01:18.992042 | 83942 | 85297 | 1125 | 10 | 3
52 | 111506 | 2022-07-14 12:58:37.333775+00 | 2022-07-14 13:00:29.701123+00 | 00:01:52.367348 | 85298 | 86891 | 1287 | 10 | 3
53 | 111507 | 2022-07-14 13:00:34.5145+00 | 2022-07-14 13:02:57.224315+00 | 00:02:22.709815 | 86894 | 88599 | 1405 | 10 | 3
54 | 111508 | 2022-07-14 13:03:01.845281+00 | 2022-07-14 13:05:12.92226+00 | 00:02:11.076979 | 88600 | 90198 | 1334 | 10 | 3
55 | 111509 | 2022-07-14 13:05:17.583785+00 | 2022-07-14 13:07:19.629614+00 | 00:02:02.045829 | 90199 | 91652 | 1218 | 10 | 3
56 | 111510 | 2022-07-14 13:07:24.001286+00 | 2022-07-14 13:09:06.744832+00 | 00:01:42.743546 | 91653 | 92948 | 1123 | 10 | 3
57 | 111511 | 2022-07-14 13:09:11.744732+00 | 2022-07-14 13:10:57.51158+00 | 00:01:45.766848 | 92949 | 94306 | 1111 | 10 | 3
58 | 111512 | 2022-07-14 13:11:01.204333+00 | 2022-07-14 13:12:38.286247+00 | 00:01:37.081914 | 94307 | 95665 | 1111 | 10 | 3
59 | 111513 | 2022-07-14 13:12:42.782589+00 | 2022-07-14 13:14:22.887366+00 | 00:01:40.104777 | 95666 | 97146 | 1199 | 10 | 3
60 | 111514 | 2022-07-14 13:14:27.449695+00 | 2022-07-14 13:16:31.656231+00 | 00:02:04.206536 | 97147 | 98765 | 1324 | 10 | 3
61 | 111515 | 2022-07-14 13:16:35.431728+00 | 2022-07-14 13:18:08.945985+00 | 00:01:33.514257 | 98766 | 100000 | 1324 | 10 | 3
(61 rows)metrics
The metrics data locally shows that most updates took between 200-300ms, with ~10% going over 300ms and ~0.5% over 0.5s-- all updates count
select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s
from batched_background_migration_jobs where batched_background_migration_id = 207) metrics
count
-------
7808
(1 row)
-- updates over 300ms count
gitlabhq_dblab=# select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s
gitlabhq_dblab(# from batched_background_migration_jobs where batched_background_migration_id = 207) metrics where metrics.s > 0.3;
count
-------
762
(1 row)
-- updates over 500ms count
gitlabhq_dblab=# select count(*) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s from batched_background_migration_jobs where batched_background_migration_id = 207) metrics where metrics.s > 0.5;
count
-------
46
(1 row)
--max update duration
gitlabhq_dblab=# select max(metrics.s) from (select id, unnest(concat('{', substring(metrics#>>'{timings, update_all}' from 2 for(length(metrics#>>'{timings, update_all}') - 2)), '}')::float[]) as s from batched_background_migration_jobs where batched_background_migration_id = 207) metrics;
max
-------------------
82.21609899998293
(1 row)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.
Edited by Alexandru Croitor