Skip to content

Scope issue model iid to namespace

Alexandru Croitor requested to merge scope_issue_iid_to_namespace into master

What does this MR do and why?

This MR changes the scope of the allocated IIDs for issues to namespace rather than project. Originally issues were only allowed at projject level. We want to enhance that by allowing work items(built on top of issues) to group level, see &8308

Currently issues are associated with a project through issues#project_id and also with its corresponding project namespace through issues#namespace_id. issues#namespace_id also allows us to associate an issue with a group.

One of the needs in order to have issues in groups is to have the IID allocation scoped to the given group.

So this is what this MR is changing.

Changing the scope to namespace in has_internal_id :iid, scope: :namespace from has_internal_id :iid, scope: :project is enough to have the code allocate new IIDs as it will create a record with given scope if one does not exist when a new issue is created.

However it will leave behind a record with issues scoped to project. So I am also adding a migration that would speed up the transition to namespace scope of issues and also cleanup records scoped to project.

This MR is extracted as a part of a bigger MR in order to scope down the size of the original MR !111664 (merged)

Screenshots or screen recordings

  • Insert SQL plan for 1000 records: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16262/commands/55894

    SQL & Plan 
    INSERT INTO internal_ids (usage, last_value, namespace_id)  SELECT 0, last_value, project_namespace_id
FROM internal_ids
INNER JOIN projects ON projects.id = internal_ids.project_id
WHERE internal_ids.id IN(
  SELECT "internal_ids"."id"
  FROM "internal_ids"
  WHERE "internal_ids"."id"
  BETWEEN 20000 AND 40000 AND "internal_ids"."usage" = 0 AND "internal_ids"."project_id" IS NOT NULL AND "internal_ids"."id" >= 31000 AND "internal_ids"."id" < 32000  )
ON CONFLICT (usage, namespace_id)
WHERE namespace_id IS NOT NULL DO NOTHING RETURNING id;
     ModifyTable on public.internal_ids  (cost=1.31..896.19 rows=95 width=24) (actual time=9.042..1911.407 rows=583 loops=1)
   Buffers: shared hit=14446 read=874 dirtied=56 written=5
   I/O Timings: read=1804.121 write=0.000
   ->  Nested Loop  (cost=1.31..896.19 rows=95 width=24) (actual time=8.495..1864.449 rows=583 loops=1)
         Buffers: shared hit=4983 read=860 dirtied=37
         I/O Timings: read=1802.172 write=0.000
         ->  Nested Loop  (cost=0.87..835.20 rows=95 width=8) (actual time=5.102..359.531 rows=583 loops=1)
               Buffers: shared hit=2757 read=155 dirtied=19
               I/O Timings: read=338.196 write=0.000
               ->  Index Scan using internal_ids_pkey on public.internal_ids internal_ids_2  (cost=0.43..507.21 rows=95 width=8) (actual time=5.081..350.000 rows=583 loops=1)
                     Index Cond: ((internal_ids_2.id >= 20000) AND (internal_ids_2.id <= 40000) AND (internal_ids_2.id >= 31000) AND (internal_ids_2.id < 32000))
                     Filter: ((internal_ids_2.project_id IS NOT NULL) AND (internal_ids_2.usage = 0))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=424 read=155 dirtied=19
                     I/O Timings: read=338.196 write=0.000
               ->  Index Scan using internal_ids_pkey on public.internal_ids internal_ids_1  (cost=0.43..3.45 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=583)
                     Index Cond: (internal_ids_1.id = internal_ids_2.id)
                     Buffers: shared hit=2333
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using projects_pkey on public.projects  (cost=0.44..0.64 rows=1 width=12) (actual time=2.541..2.541 rows=1 loops=583)
               Index Cond: (projects.id = internal_ids_1.project_id)
               Buffers: shared hit=1628 read=705 dirtied=17
               I/O Timings: read=1463.976 write=0.000

Time: 2.787 s
  - planning: 1.139 ms
  - execution: 2.786 s
    - I/O read: 1.804 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 14446 (~112.90 MiB) from the buffer pool
  - reads: 874 (~6.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 56 (~448.00 KiB)
  - writes: 5 (~40.00 KiB)

  • Insert SQL plan for 100 records: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16262/commands/55895

    SQL & Plan 
    INSERT INTO internal_ids (usage, last_value, namespace_id)
SELECT 0, last_value, project_namespace_id
FROM internal_ids
INNER JOIN projects ON projects.id = internal_ids.project_id
WHERE internal_ids.id IN(
  SELECT "internal_ids"."id"
  FROM "internal_ids"
  WHERE "internal_ids"."id"
  BETWEEN 40000 AND 60000 AND "internal_ids"."usage" = 0 AND "internal_ids"."project_id" IS NOT NULL AND "internal_ids"."id" >= 44000 AND "internal_ids"."id" < 44100
) ON CONFLICT (usage, namespace_id)
WHERE namespace_id IS NOT NULL DO NOTHING RETURNING id;
    ModifyTable on public.internal_ids  (cost=1.31..97.85 rows=10 width=24) (actual time=14.223..159.051 rows=5 loops=1)
   Buffers: shared hit=189 read=54 dirtied=12
   I/O Timings: read=148.594 write=0.000
   ->  Nested Loop  (cost=1.31..97.85 rows=10 width=24) (actual time=13.960..155.277 rows=5 loops=1)
         Buffers: shared hit=105 read=53 dirtied=11
         I/O Timings: read=148.547 write=0.000
         ->  Nested Loop  (cost=0.87..91.43 rows=10 width=8) (actual time=8.157..134.249 rows=5 loops=1)
               Buffers: shared hit=75 read=44 dirtied=11
               I/O Timings: read=128.048 write=0.000
               ->  Index Scan using internal_ids_pkey on public.internal_ids internal_ids_2  (cost=0.43..56.91 rows=10 width=8) (actual time=8.133..134.117 rows=5 loops=1)
                     Index Cond: ((internal_ids_2.id >= 40000) AND (internal_ids_2.id <= 60000) AND (internal_ids_2.id >= 44000) AND (internal_ids_2.id < 44100))
                     Filter: ((internal_ids_2.project_id IS NOT NULL) AND (internal_ids_2.usage = 0))
                     Rows Removed by Filter: 82
                     Buffers: shared hit=55 read=44 dirtied=11
                     I/O Timings: read=128.048 write=0.000
               ->  Index Scan using internal_ids_pkey on public.internal_ids internal_ids_1  (cost=0.43..3.45 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=5)
                     Index Cond: (internal_ids_1.id = internal_ids_2.id)
                     Buffers: shared hit=20
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using projects_pkey on public.projects  (cost=0.44..0.64 rows=1 width=12) (actual time=4.156..4.157 rows=1 loops=5)
               Index Cond: (projects.id = internal_ids_1.project_id)
               Buffers: shared hit=11 read=9
               I/O Timings: read=20.499 write=0.000

Time: 180.882 ms
  - planning: 1.226 ms
  - execution: 179.656 ms
    - I/O read: 148.594 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 189 (~1.50 MiB) from the buffer pool
  - reads: 54 (~432.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 12 (~96.00 KiB)
  - writes: 0

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.

Edited by Alexandru Croitor

Merge request reports