Add IID to Feature Flags and Backfill with Param in AtomicInternalId
What does this MR do?
Implementation of !20871 (closed) using SQL and a backfill
parameter in AtomicInternalId
.
Migration Performance
Running the backfill in #database-lab
yields the following results:
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 = operations_feature_flags_with_calculated_iid.iid_num FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY id ASC) AS iid_num FROM operations_feature_flags) AS operations_feature_flags_with_calculated_iid WHERE operations_feature_flags.id = operations_feature_flags_with_calculated_iid.id AND operations_feature_flags.iid IS NULL
ModifyTable on public.operations_feature_flags (cost=84.60..104.24 rows=3 width=112) (actual time=19.396..19.396 rows=0 loops=1)
Buffers: shared hit=6745 read=24 dirtied=39
I/O Timings: read=9.105
-> Hash Join (cost=84.60..104.24 rows=3 width=112) (actual time=0.836..1.732 rows=581 loops=1)
Hash Cond: (operations_feature_flags_with_calculated_iid.id = operations_feature_flags.id)
Buffers: shared hit=297
-> Subquery Scan (cost=64.48..81.91 rows=581 width=56) (actual time=0.311..0.893 rows=581 loops=1)
Buffers: shared hit=14
-> WindowAgg (cost=64.48..76.10 rows=581 width=20) (actual time=0.290..0.693 rows=581 loops=1)
Buffers: shared hit=14
-> Sort (cost=64.48..65.94 rows=581 width=12) (actual time=0.279..0.358 rows=581 loops=1)
Sort Key: operations_feature_flags_1.project_id, operations_feature_flags_1.id
Sort Method: quicksort Memory: 52kB
Buffers: shared hit=14
-> Seq Scan on public.operations_feature_flags operations_feature_flags_1 (cost=0.00..37.81 rows=581 width=12) (actual time=0.005..0.077 rows=581 loops=1)
Buffers: shared hit=8
-> Hash (cost=20.07..20.07 rows=3 width=68) (actual time=0.504..0.504 rows=581 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 68kB
Buffers: shared hit=283
-> Index Scan using index_operations_feature_flags_on_project_id_and_iid on public.operations_feature_flags (cost=0.28..20.07 rows=3 width=68) (actual time=0.052..0.285 rows=581 loops=1)
Index Cond: (operations_feature_flags.iid IS NULL)
Buffers: shared hit=283
Time: 20.496 ms
- planning: 0.976 ms
- execution: 19.520 ms
- I/O read: 9.105 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 6745 (~52.70 MiB) from the buffer pool
- reads: 24 (~192.00 KiB) from the OS file cache, including disk I/O
- dirtied: 39 (~312.00 KiB)
- writes: 0
It is expected to backfill around 700
rows on gitlab.com
:
gitlabhq_production=> SELECT COUNT(id) FROM operations_feature_flags;
count
-------
676
(1 row)
gitlabhq_production=>
Screenshots
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
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