Backfill has_remediations column on vulnerability_reads table

What does this MR do and why?

Related to Database: Add hasRemediations filter to Vulnera... (#420617 - closed).

Database review

This MR introduces a post-deployment migration to schedule the background jobs.

Output of the post-deployment migration

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

main: == [advisory_lock_connection] object_id: 227080, pg_backend_pid: 57552
main: == 20231011142714 QueueBackfillHasRemediationsOfVulnerabilityReads: migrating =
main: == 20231011142714 QueueBackfillHasRemediationsOfVulnerabilityReads: migrated (0.0436s)
main: == [advisory_lock_connection] object_id: 227080, pg_backend_pid: 57552

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

main: == [advisory_lock_connection] object_id: 226720, pg_backend_pid: 31131
main: == 20231011142714 QueueBackfillHasRemediationsOfVulnerabilityReads: reverting =
main: == 20231011142714 QueueBackfillHasRemediationsOfVulnerabilityReads: reverted (0.0369s)
main: == [advisory_lock_connection] object_id: 226720, pg_backend_pid: 31131

Update query used in background migration

UPDATE vulnerability_reads
SET    has_remediations = true
FROM   (SELECT vulnerability_reads.vulnerability_id,
               vulnerability_occurrences.id
        FROM   "vulnerability_reads"
               INNER JOIN vulnerability_occurrences
                       ON vulnerability_reads.vulnerability_id =
                          vulnerability_occurrences.vulnerability_id
               INNER JOIN vulnerability_findings_remediations
                       ON vulnerability_occurrences.id =
       vulnerability_findings_remediations.vulnerability_occurrence_id
        WHERE  "vulnerability_reads"."vulnerability_id" BETWEEN 1 AND 11513
               AND "vulnerability_reads"."vulnerability_id" >= 750
               AND "vulnerability_reads"."vulnerability_id" < 801) AS sub_query
WHERE  vulnerability_reads.vulnerability_id = sub_query.vulnerability_id 

 ModifyTable on public.vulnerability_reads  (cost=2.13..207.78 rows=0 width=0) (actual time=168.113..168.117 rows=0 loops=1)
   Buffers: shared hit=412 read=66
   I/O Timings: read=165.915 write=0.000
   ->  Nested Loop  (cost=2.13..207.78 rows=1 width=25) (actual time=168.111..168.113 rows=0 loops=1)
         Buffers: shared hit=412 read=66
         I/O Timings: read=165.915 write=0.000
         ->  Nested Loop  (cost=1.56..207.15 rows=1 width=34) (actual time=168.110..168.112 rows=0 loops=1)
               Buffers: shared hit=412 read=66
               I/O Timings: read=165.915 write=0.000
               ->  Nested Loop  (cost=1.14..183.94 rows=41 width=36) (actual time=9.134..157.647 rows=51 loops=1)
                     Buffers: shared hit=261 read=64
                     I/O Timings: read=155.972 write=0.000
                     ->  Index Scan using index_vulnerability_reads_on_vulnerability_id on public.vulnerability_reads vulnerability_reads_1  (cost=0.57..36.55 rows=41 width=14) (actual time=4.175..112.015 rows=51 loops=1)
                           Index Cond: ((vulnerability_reads_1.vulnerability_id >= 1) AND (vulnerability_reads_1.vulnerability_id <= 11513) AND (vulnerability_reads_1.vulnerability_id >= 750) AND (vulnerability_reads_1.vulnerability_id < 801))
                           Buffers: shared hit=25 read=45
                           I/O Timings: read=111.397 write=0.000
                     ->  Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences  (cost=0.57..3.58 rows=1 width=22) (actual time=0.889..0.890 rows=1 loops=51)
                           Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerability_reads_1.vulnerability_id)
                           Buffers: shared hit=236 read=19
                           I/O Timings: read=44.576 write=0.000
               ->  Index Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations  (cost=0.43..0.52 rows=5 width=14) (actual time=0.203..0.203 rows=0 loops=51)
                     Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
                     Buffers: shared hit=151 read=2
                     I/O Timings: read=9.943 write=0.000
         ->  Index Scan using index_vulnerability_reads_on_vulnerability_id on public.vulnerability_reads  (cost=0.57..0.61 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (vulnerability_reads.vulnerability_id = vulnerability_occurrences.vulnerability_id)
               I/O Timings: read=0.000 write=0.000

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23061/commands/74278

Link: !133714 (comment 1599381695)

Estimated Time to complete: 1 week, 4 days, 8 hours, and 14 minutes

• Interval: 120s
• Total tuple count: 81677080
• Max batch size: 0
• Estimated seconds to complete: 980040s
• Estimated number of batches: 8167
• Average batch time: 57.68s
• Batch size: 10000
• N. of batches sampled: 31
• N. of failed batches: 0

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.

Related to #420617 (closed)