Add namespace statistics group delete event handler

What does this MR do and why?

Adds namespace statistics group delete event handler, with some cleanup and renaming of existing services. This event handler recalculates the statistics of the groups's parent statistics and propagates the change up to all the ancestors.

Changelog: added
EE: true

Related issue

Update statistics on project or group delete or... (#534725 - closed) • Gal Katz, Yuval Siev • 18.2

Query plan

batch_delete_statistics_records

Raw SQL

DELETE FROM "vulnerability_statistics"
WHERE ("vulnerability_statistics"."id") IN (
        SELECT
            "vulnerability_statistics"."id"
        FROM
            "vulnerability_statistics"
        WHERE
            "vulnerability_statistics"."archived" = FALSE
            AND "vulnerability_statistics"."traversal_ids" >= '{9970}'
            AND "vulnerability_statistics"."traversal_ids" < '{9971}'
        LIMIT 500)

Query plan

See full plan here.

 ModifyTable on public.vulnerability_statistics  (cost=763.49..2416.19 rows=0 width=0) (actual time=92.511..92.513 rows=0 loops=1)
   Buffers: shared hit=2703 read=286 dirtied=413
   WAL: records=913 fpi=413 bytes=2317849
   I/O Timings: read=86.866 write=0.000
   ->  Nested Loop  (cost=763.49..2416.19 rows=500 width=38) (actual time=3.049..92.116 rows=500 loops=1)
         Buffers: shared hit=2203 read=286 dirtied=413
         WAL: records=413 fpi=413 bytes=2290849
         I/O Timings: read=86.866 write=0.000
         ->  HashAggregate  (cost=763.07..768.07 rows=500 width=40) (actual time=2.985..3.119 rows=500 loops=1)
               Group Key: "ANY_subquery".id
               Buffers: shared hit=489 dirtied=413
               WAL: records=413 fpi=413 bytes=2290849
               I/O Timings: read=0.000 write=0.000
               ->  Subquery Scan on ANY_subquery  (cost=0.55..761.82 rows=500 width=40) (actual time=0.040..2.852 rows=500 loops=1)
                     Buffers: shared hit=489 dirtied=413
                     WAL: records=413 fpi=413 bytes=2290849
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=0.55..756.82 rows=500 width=8) (actual time=0.034..2.750 rows=500 loops=1)
                           Buffers: shared hit=489 dirtied=413
                           WAL: records=413 fpi=413 bytes=2290849
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using idx_vulnerability_statistics_on_traversal_ids_and_letter_grade on public.vulnerability_statistics vulnerability_statistics_1  (cost=0.55..2220.95 rows=1468 width=8) (actual time=0.033..2.708 rows=500 loops=1)
                                 Index Cond: ((vulnerability_statistics_1.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_statistics_1.traversal_ids < '{9971}'::bigint[]))
                                 Buffers: shared hit=489 dirtied=413
                                 WAL: records=413 fpi=413 bytes=2290849
                                 I/O Timings: read=0.000 write=0.000
         ->  Index Scan using vulnerability_statistics_pkey on public.vulnerability_statistics  (cost=0.42..3.30 rows=1 width=14) (actual time=0.178..0.178 rows=1 loops=500)
               Index Cond: (vulnerability_statistics.id = "ANY_subquery".id)
               Buffers: shared hit=1714 read=286
               I/O Timings: read=86.866 write=0.000
Settings: random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB'

Statistic.by_group(parent_group).delete_all - Old version without index usage for comparison only:

Raw SQL

DELETE FROM "vulnerability_statistics"
WHERE "vulnerability_statistics"."traversal_ids" >= '{9970}'
    AND "vulnerability_statistics"."traversal_ids" < '{9971}'

Query plan

See this explain.

ModifyTable on public.vulnerability_statistics  (cost=0.00..74201.44 rows=0 width=0) (actual time=339.555..339.556 rows=0 loops=1)
   Buffers: shared hit=2659 read=16832 dirtied=2509
   WAL: records=3597 fpi=2508 bytes=16675876
   I/O Timings: read=199.378 write=0.000
   ->  Seq Scan on public.vulnerability_statistics  (cost=0.00..74201.44 rows=1849 width=6) (actual time=0.875..333.477 rows=1552 loops=1)
         Filter: ((vulnerability_statistics.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_statistics.traversal_ids < '{9971}'::bigint[]))
         Rows Removed by Filter: 384475
         Buffers: shared read=16831 dirtied=1468
         WAL: records=1956 fpi=1468 bytes=11200004
         I/O Timings: read=199.360 write=0.000
Settings: jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5'