Add namespace statistics adjustment service (!182917) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Adds a namespace level adjustment service that calculates vulnerability statistics based on the existing project level statistics using traversal_ids. It uses the existing scheduled worker to trigger the calculations.

Relevant issue

Add a scheduled job to calculate namespace leve... (#521325 - closed) • Gal Katz • 17.11

How to set up and validate locally

1. Make sure the new table exists

bin/rails db:migrate VERSION=20250224125821

2. Run the background job:

Go to http://gdk.test:3000/admin/background_jobs
Go to Cron tab.
click Enqueue now for vulnerability_namespace_statistics_schedule_worker

OR use one of****:

Vulnerabilities::NamespaceStatistics::ScheduleWorker.new.perform
Vulnerabilities::NamespaceStatistics::AdjustmentService.execute([<YOUR_FAVORITE_NAMESPACE_ID>])

Query plans

NamespaceStatistics::AdjustmentService

Raw SQL

INSERT INTO vulnerability_namespace_statistics (total, info, unknown, low, medium, high, critical, traversal_ids, namespace_id, created_at, updated_at) ( WITH namespace_data (
        namespace_id, traversal_ids, next_traversal_id
) AS (
            VALUES (9970, ARRAY[9970]::bigint[], ARRAY[9971]::bigint[]))
            SELECT
                SUM(total) AS total,
                SUM(info) AS info,
                SUM(unknown) AS unknown,
                SUM(low) AS low,
                SUM(medium) AS medium,
                SUM(high) AS high,
                SUM(critical) AS critical,
                namespace_data.traversal_ids AS traversal_ids,
                namespace_data.namespace_id AS namespace_id,
                now() AS created_at,
                now() AS updated_at
            FROM
                vulnerability_statistics,
                namespace_data
            WHERE
                vulnerability_statistics.archived = FALSE
                AND vulnerability_statistics.traversal_ids >= namespace_data.traversal_ids
                AND vulnerability_statistics.traversal_ids < namespace_data.next_traversal_id
            GROUP BY
                namespace_data.traversal_ids,
                namespace_id)
    ON CONFLICT (namespace_id)
        DO UPDATE SET
            total = EXCLUDED.total,
            info = EXCLUDED.info,
            unknown = EXCLUDED.unknown,
            low = EXCLUDED.low,
            medium = EXCLUDED.medium,
            high = EXCLUDED.high,
            critical = EXCLUDED.critical,
            updated_at = EXCLUDED.updated_at,
            traversal_ids = EXCLUDED.traversal_ids

Query plan

See this explain.

ModifyTable on public.vulnerability_namespace_statistics  (cost=0.42..2265.99 rows=0 width=0) (actual time=681.751..681.753 rows=0 loops=1)
   Buffers: shared hit=150 read=1170 dirtied=166 written=4
   WAL: records=204 fpi=158 bytes=1049392
   I/O Timings: read=665.951 write=0.106
   ->  Subquery Scan on *SELECT*  (cost=0.42..2265.99 rows=1 width=92) (actual time=680.544..680.546 rows=1 loops=1)
         Buffers: shared hit=142 read=1167 dirtied=159
         WAL: records=196 fpi=158 bytes=1048688
         I/O Timings: read=665.319 write=0.000
         ->  Aggregate  (cost=0.42..2265.96 rows=1 width=108) (actual time=677.954..677.955 rows=1 loops=1)
               Buffers: shared hit=133 read=1162 dirtied=158
               WAL: records=195 fpi=158 bytes=1048589
               I/O Timings: read=662.883 write=0.000
               ->  Index Scan using idx_vulnerability_statistics_on_traversal_ids_and_letter_grade on public.vulnerability_statistics  (cost=0.42..2239.01 rows=1539 width=28) (actual time=5.024..677.059 rows=1349 loops=1)
                     Index Cond: ((vulnerability_statistics.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_statistics.traversal_ids < '{9971}'::bigint[]))
                     Buffers: shared hit=133 read=1162 dirtied=158
                     WAL: records=195 fpi=158 bytes=1048589
                     I/O Timings: read=662.883 write=0.000
Settings: seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5', jit = 'off', effective_cache_size = '472585MB'

NamespaceStatistics::ScheduleWorker