Add IID to Operations Feature Flags and Backfill with SQL (!22130) · Merge requests · GitLab.org / GitLab

What does this MR do?

Implementation of !20871 (closed) using SQL.

Migration Performance

Performance of the backfill on GitLab.com in #database-lab:

exec ALTER TABLE "operations_feature_flags" ADD "iid" integer

exec CREATE UNIQUE INDEX CONCURRENTLY "index_operations_feature_flags_on_project_id_and_iid" ON "operations_feature_flags" ("project_id", "iid")

EXPLAIN UPDATE operations_feature_flags SET iid = feature_flags_with_calculated_iid.iid_num FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY iid, id ASC NULLS LAST) AS iid_num FROM operations_feature_flags) AS feature_flags_with_calculated_iid WHERE operations_feature_flags.id = feature_flags_with_calculated_iid.id

 ModifyTable on public.operations_feature_flags  (cost=83.28..125.72 rows=550 width=112) (actual time=27.872..27.872 rows=0 loops=1)
   Buffers: shared hit=6126 read=22 dirtied=35
   I/O Timings: read=14.127
   ->  Hash Join  (cost=83.28..125.72 rows=550 width=112) (actual time=0.961..1.378 rows=550 loops=1)
         Hash Cond: (operations_feature_flags.id = feature_flags_with_calculated_iid.id)
         Buffers: shared hit=20
         ->  Seq Scan on public.operations_feature_flags  (cost=0.00..33.50 rows=550 width=68) (actual time=0.007..0.150 rows=550 loops=1)
               Buffers: shared hit=7
         ->  Hash  (cost=76.41..76.41 rows=550 width=56) (actual time=0.907..0.907 rows=550 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 56kB
               Buffers: shared hit=13
               ->  Subquery Scan  (cost=58.53..76.41 rows=550 width=56) (actual time=0.363..0.769 rows=550 loops=1)
                     Buffers: shared hit=13
                     ->  WindowAgg  (cost=58.53..70.91 rows=550 width=24) (actual time=0.334..0.605 rows=550 loops=1)
                           Buffers: shared hit=13
                           ->  Sort  (cost=58.53..59.91 rows=550 width=16) (actual time=0.328..0.385 rows=550 loops=1)
                                 Sort Key: operations_feature_flags_1.project_id, operations_feature_flags_1.iid, operations_feature_flags_1.id
                                 Sort Method: quicksort  Memory: 50kB
                                 Buffers: shared hit=13
                                 ->  Seq Scan on public.operations_feature_flags operations_feature_flags_1  (cost=0.00..33.50 rows=550 width=16) (actual time=0.002..0.109 rows=550 loops=1)
                                       Buffers: shared hit=7

Time: 29.036 ms
  - planning: 1.045 ms
  - execution: 27.991 ms
    - I/O read: 14.127 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6126 (~47.90 MiB) from the buffer pool
  - reads: 22 (~176.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 35 (~280.00 KiB)
  - writes: 0

EXPLAIN DELETE FROM internal_ids WHERE project_id IN (SELECT DISTINCT(project_id) FROM operations_feature_flags) AND usage = 6

 ModifyTable on public.internal_ids  (cost=0.71..28.59 rows=1 width=34) (actual time=9.962..9.962 rows=0 loops=1)
   Buffers: shared hit=1 read=5
   I/O Timings: read=9.845
   ->  Merge Join  (cost=0.71..28.59 rows=1 width=34) (actual time=9.960..9.960 rows=0 loops=1)
         Buffers: shared hit=1 read=5
         I/O Timings: read=9.845
         ->  Subquery Scan  (cost=0.28..23.28 rows=338 width=32) (actual time=0.135..0.135 rows=1 loops=1)
               Buffers: shared read=3
               I/O Timings: read=0.071
               ->  Unique  (cost=0.28..19.90 rows=338 width=4) (actual time=0.114..0.114 rows=1 loops=1)
                     Buffers: shared read=3
                     I/O Timings: read=0.071
                     ->  Index Only Scan using index_operations_feature_flags_on_project_id_and_name on public.operations_feature_flags  (cost=0.28..18.52 rows=550 width=4) (actual time=0.112..0.112 rows=1 loops=1)
                           Heap Fetches: 0
                           Buffers: shared read=3
                           I/O Timings: read=0.071
         ->  Index Scan using index_internal_ids_on_usage_and_project_id on public.internal_ids  (cost=0.43..4.45 rows=1 width=10) (actual time=9.823..9.823 rows=0 loops=1)
               Index Cond: (internal_ids.usage = 6)
               Buffers: shared hit=1 read=2
               I/O Timings: read=9.774

Time: 11.233 ms
  - planning: 1.172 ms
  - execution: 10.061 ms
    - I/O read: 9.845 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Add IID to Operations Feature Flags and Backfill with SQL