Backfill identifier_names to vulnerability_reads

What does this MR do and why?

New backfill migration for vulnerability_reads.identifier_names as the old BBM is abandoned.

It is intended to be used for the filtering by identifier feature in #432419 (closed).

Changelog: other

Database

The estimate from db testing pipeline job for this migration to complete is available in !163088 (comment 2054374348).

Estimated Time to complete: 2 weeks, 3 days, 9 hours, 25 minutes, and 20 seconds

On production this is expected to finish sooner since we have migration optimization strategy.

We have set the Batch size to be 12000 and sub-batch size as 40 and with that we are getting:

Mean query time: 150.2 ms

Max query time: 12748.0 ms.

The max query time seems to be happen rarely when the DB is busy/congested and is not worth the effort for this one time migration task to further optimize.

Output of the post-deployment migration

bundle exec rake db:migrate:up:main VERSION=20241007115637

main: == [advisory_lock_connection] object_id: 128940, pg_backend_pid: 46825
main: == 20241007115637 QueueBackfillIdentifierNamesOfVulnerabilityReads: migrating =
main: == 20241007115637 QueueBackfillIdentifierNamesOfVulnerabilityReads: migrated (0.0772s)

main: == [advisory_lock_connection] object_id: 128940, pg_backend_pid: 46825

bundle exec rake db:migrate:down:main VERSION=20241007115637

main: == [advisory_lock_connection] object_id: 128920, pg_backend_pid: 47302
main: == 20241007115637 QueueBackfillIdentifierNamesOfVulnerabilityReads: reverting =
main: == 20241007115637 QueueBackfillIdentifierNamesOfVulnerabilityReads: reverted (0.0314s)

main: == [advisory_lock_connection] object_id: 128920, pg_backend_pid: 47302

Update query used in each sub-batch

UPDATE
  vulnerability_reads vr
SET
  identifier_names = selected_ids.names
FROM
  (
    WITH
      batched_relation AS MATERIALIZED (
        SELECT
          vulnerability_reads.*
        FROM
          vulnerability_reads
        WHERE
          vulnerability_reads.id BETWEEN 104513160
          AND 104513180
          AND vulnerability_reads.id >= 104513160
          AND vulnerability_reads.id < 104513201
        LIMIT
          40
      )
    SELECT
      vulnerability_reads.id AS id,
      array_agg(
        vi.name
        ORDER BY
          vi.name
      ) AS names
    FROM
      batched_relation vulnerability_reads
      JOIN vulnerability_occurrences vo ON vulnerability_reads.vulnerability_id = vo.vulnerability_id
      JOIN vulnerability_occurrence_identifiers voi ON vo.id = voi.occurrence_id
      JOIN vulnerability_identifiers vi ON voi.identifier_id = vi.id
    GROUP BY
      vulnerability_reads.id
  ) selected_ids
WHERE
  vr.id = selected_ids.id

 ModifyTable on public.vulnerability_reads vr  (cost=105.69..159.44 rows=0 width=0) (actual time=699.540..699.565 rows=0 loops=1)
   Buffers: shared hit=881 read=445 dirtied=82
   I/O Timings: read=688.751 write=0.000
   ->  Nested Loop  (cost=105.69..159.44 rows=15 width=102) (actual time=95.848..96.198 rows=7 loops=1)
         Buffers: shared hit=142 read=37 dirtied=3
         I/O Timings: read=94.390 write=0.000
         ->  Subquery Scan on selected_ids  (cost=105.12..105.67 rows=15 width=104) (actual time=95.826..96.061 rows=7 loops=1)
               Buffers: shared hit=107 read=37 dirtied=3
               I/O Timings: read=94.390 write=0.000
               ->  Aggregate  (cost=105.12..105.52 rows=15 width=40) (actual time=95.812..96.004 rows=7 loops=1)
                     Group Key: vulnerability_reads.id
                     Buffers: shared hit=107 read=37 dirtied=3
                     I/O Timings: read=94.390 write=0.000
                     CTE batched_relation
                       ->  Limit  (cost=0.57..24.42 rows=15 width=289) (actual time=14.061..16.777 rows=7 loops=1)
                             Buffers: shared hit=15 read=6
                             I/O Timings: read=16.610 write=0.000
                             ->  Index Scan using vulnerability_reads_pkey on public.vulnerability_reads vulnerability_reads_1  (cost=0.57..24.42 rows=15 width=289) (actual time=14.057..16.764 rows=7 loops=1)
                                   Index Cond: ((vulnerability_reads_1.id >= 104513160) AND (vulnerability_reads_1.id <= 104513180) AND (vulnerability_reads_1.id >= 104513160) AND (vulnerability_reads_1.id < 104513201))
                                   Buffers: shared hit=15 read=6
                                   I/O Timings: read=16.610 write=0.000
                     ->  Sort  (cost=80.70..80.77 rows=28 width=29) (actual time=95.684..95.697 rows=9 loops=1)
                           Sort Key: vulnerability_reads.id
                           Sort Method: quicksort  Memory: 25kB
                           Buffers: shared hit=97 read=37 dirtied=3
                           I/O Timings: read=94.390 write=0.000
                           ->  Nested Loop  (cost=1.58..80.03 rows=28 width=29) (actual time=52.567..95.623 rows=9 loops=1)
                                 Buffers: shared hit=94 read=37 dirtied=3
                                 I/O Timings: read=94.390 write=0.000
                                 ->  Nested Loop  (cost=1.14..67.17 rows=28 width=16) (actual time=47.017..75.644 rows=9 loops=1)
                                       Buffers: shared hit=66 read=29 dirtied=3
                                       I/O Timings: read=74.653 write=0.000
                                       ->  Nested Loop  (cost=0.57..54.23 rows=15 width=16) (actual time=38.708..54.797 rows=7 loops=1)
                                             Buffers: shared hit=39 read=17
                                             I/O Timings: read=54.111 write=0.000
                                             ->  CTE Scan on batched_relation vulnerability_reads  (cost=0.00..0.30 rows=15 width=16) (actual time=14.068..16.822 rows=7 loops=1)
                                                   Buffers: shared hit=15 read=6
                                                   I/O Timings: read=16.610 write=0.000
                                             ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences vo  (cost=0.57..3.58 rows=1 width=16) (actual time=5.417..5.419 rows=1 loops=7)
                                                   Index Cond: (vo.vulnerability_id = vulnerability_reads.vulnerability_id)
                                                   Buffers: shared hit=24 read=11
                                                   I/O Timings: read=37.501 write=0.000
                                       ->  Index Only Scan using index_vulnerability_occurrence_identifiers_on_unique_keys on public.vulnerability_occurrence_identifiers voi  (cost=0.57..0.76 rows=10 width=16) (actual time=2.399..2.970 rows=1 loops=7)
                                             Index Cond: (voi.occurrence_id = vo.id)
                                             Heap Fetches: 3
                                             Buffers: shared hit=27 read=12 dirtied=3
                                             I/O Timings: read=20.543 write=0.000
                                 ->  Index Scan using vulnerability_identifiers_pkey on public.vulnerability_identifiers vi  (cost=0.44..0.46 rows=1 width=29) (actual time=2.212..2.212 rows=1 loops=9)
                                       Index Cond: (vi.id = voi.identifier_id)
                                       Buffers: shared hit=28 read=8
                                       I/O Timings: read=19.737 write=0.000
         ->  Index Scan using vulnerability_reads_pkey on public.vulnerability_reads vr  (cost=0.57..3.58 rows=1 width=14) (actual time=0.013..0.013 rows=1 loops=7)
               Index Cond: (vr.id = selected_ids.id)
               Buffers: shared hit=35
               I/O Timings: read=0.000 write=0.000

Time: 711.209 ms
  - planning: 11.311 ms
  - execution: 699.898 ms
    - I/O read: 688.751 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 881 (~6.90 MiB) from the buffer pool
  - reads: 445 (~3.50 MiB) from the OS file cache, including disk I/O
  - dirtied: 82 (~656.00 KiB)
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32350/commands/99928

Estimated Time to complete: 2 weeks, 3 days, 9 hours, 25 minutes, and 20 seconds

• Interval: 160s
• Total tuple count: 112712050
• Max batch size: 0
• Estimated seconds to complete: 1502720s
• Estimated number of batches: 9392
• Average batch time: 148.12s
• Batch size: 12000
• 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.

Related to #460080 (closed)

lib/gitlab/background_migration/backfill_identifier_names_of_vulnerability_reads.rb

@@ -9,23 +9,7 @@ class BackfillIdentifierNamesOfVulnerabilityReads < BatchedMigrationJob
       UPDATE_SQL = <<~SQL
         UPDATE vulnerability_reads AS vr
         SET identifier_names = selected_ids.names
-        FROM (
-            SELECT
-                vr.id,
-                ARRAY_AGG(LOWER(vi.name) ORDER BY LOWER(vi.name)) AS names
-            FROM
-                vulnerability_reads vr
-            JOIN
-                vulnerability_occurrences vo ON vr.vulnerability_id = vo.vulnerability_id
-            JOIN
-                vulnerability_occurrence_identifiers voi ON vo.id = voi.occurrence_id
-            JOIN
-                vulnerability_identifiers vi ON voi.identifier_id = vi.id
-            WHERE
-                vr.id IN (?)
-            GROUP BY
-                vr.id
-        ) AS selected_ids
+        FROM (?) AS selected_ids
         WHERE vr.id = selected_ids.id
       SQL
 
@@ -35,7 +19,25 @@ class VulnerabilitiesRead < ::ApplicationRecord
 
       def perform
         each_sub_batch do |sub_batch|
-          update_query = VulnerabilitiesRead.sanitize_sql([UPDATE_SQL, sub_batch.select(:id)])
+          cte = Gitlab::SQL::CTE.new(:batched_relation, sub_batch.limit(100))
+
+          filtered_results = cte
+            .apply_to(VulnerabilitiesRead.all)
+            .joins(
+              'INNER JOIN vulnerability_occurrences vo ' \
+                'ON vulnerability_reads.vulnerability_id = vo.vulnerability_id'
+            )
+            .joins('INNER JOIN vulnerability_occurrence_identifiers voi ON vo.id = voi.occurrence_id')
+            .joins('INNER JOIN vulnerability_identifiers vi ON voi.identifier_id = vi.id')
+            .where(vulnerability_reads: { id: sub_batch.select(:id) })
+            .group("vulnerability_reads.id")
+            .select(
+              'vulnerability_reads.id AS id',
+              'ARRAY_AGG(LOWER(vi.name) ORDER BY LOWER(vi.name)) AS names'
+            )
+
+          update_query = VulnerabilitiesRead.sanitize_sql([UPDATE_SQL, filtered_results])
+
           connection.execute(update_query)
         end
       end