Scope policy merge request rule deletion to the project

What does this MR do and why?

Security::Policy#delete_approval_merge_request_rules_for_project now reads only the project's approval_merge_request_rules instead of walking the whole policy configuration.

The old code ran each_batch(of: 5000) over all rules of the configuration and applied the project filter inside each DELETE. For the largest configuration (about 2.8M rows) that is 558 batches per call, no matter how many rows the project has. Each batch deleted at most a dozen rows. The method runs once per project per policy on every policy scope or status change.

The new code:

  1. Materializes the project's rows through index_approval_merge_request_rules_on_project_id in a CTE.
  2. Filters them by configuration, policy rules and unmerged merge requests.
  3. Plucks ids and deletes them in batches of 1000.

APPROVAL_MERGE_REQUEST_RULES_BATCH_SIZE goes from 5000 to 1000 to respect the pluck limit. The constant has no other users.

Why the CTE is MATERIALIZED

With the configuration and policy rule filters inline, the planner picks index_approval_mr_rules_on_project_id_policy_rule_id_and_id (configuration_id, approval_policy_rule_id, id) and filters on project_id. Measured on a production-size clone: 925 s cold, about 1.54M index entries removed by filter.

Before and after

Before, largest configuration, production-size clone:

  • per batch: about 35 ms warm, about 140 ms cold, about 26k buffers
  • one call: about 20 s warm, about 80 s cold, about 14.5M buffer touches

After, from the plans in the Database section:

Run Execution Buffers
Large project select (about 84k project rows, 1000 matched), cold 57.1 s hit 24,212, read 67,590
Large project select, warm 132.6 ms hit 91,697
Small project select (60 project rows), cold 51.3 ms hit 139, read 52
Small project select, warm 0.57 ms hit 191
Delete of 1000 ids, cold 9.77 s (5 FK triggers about 6.6 s) hit 5,377, read 2,574
Delete of 1000 ids, warm 89 ms hit 7,001

Known trade-off

The CTE re-reads all of the project's rows on every loop iteration (one batch per 1000 matches). For the large project that is about 62k pages per batch (about 90 ms warm). Cold cost is dominated by the first read of the project's rows.

A covering (project_id, security_orchestration_policy_configuration_id, approval_policy_rule_id, id) index would remove the CTE and the repeated reads. approval_merge_request_rules is over the table size limit, so a new index needs a database team exception. Database reviewers: do you prefer that route?

Not changed

Security::ScanResultPolicy#delete_merge_request_rules_for_project (used by the project transfer worker) has the same configuration-wide pattern. Out of scope here, possible follow-up.

References

  • !261734 (merged) skips this cleanup entirely for projects the policy was never linked to (the main source of calls). The two MRs are independent.
  • Longer term, only fan out to projects whose scope status changed: #607604

Changelog: performance (EE). No feature flag.

Database

Plans were taken on a production-size thin clone. Large project: about 84k rows. Small project: 60 rows. Large policy configuration.

One loop iteration (select ids):

WITH "project_approval_merge_request_rules" AS MATERIALIZED (SELECT "approval_merge_request_rules"."id", "approval_merge_request_rules"."merge_request_id", "approval_merge_request_rules"."security_orchestration_policy_configuration_id", "approval_merge_request_rules"."approval_policy_rule_id" FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."project_id" = <large_project_id>) SELECT "approval_merge_request_rules"."id" FROM "project_approval_merge_request_rules" AS "approval_merge_request_rules" INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id" WHERE "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id> AND "approval_merge_request_rules"."approval_policy_rule_id" IN (SELECT "approval_policy_rules"."id" FROM "approval_policy_rules" WHERE "approval_policy_rules"."security_policy_id" = <security_policy_id>) AND "merge_requests"."state_id" != 3 LIMIT 1000

Then delete them:

DELETE FROM "approval_merge_request_rules" WHERE "approval_merge_request_rules"."id" IN (...)
Select, large project, cold
 Limit  (cost=88173.87..89963.04 rows=1 width=8) (actual time=52592.169..57119.720 rows=1000 loops=1)
   Buffers: shared hit=24212 read=67590 dirtied=2817
   WAL: records=3281 fpi=2817 bytes=22073833
   I/O Timings: read=56658.327 write=0.000
   CTE project_approval_merge_request_rules
     ->  Index Scan using index_approval_merge_request_rules_on_project_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.57..88172.89 rows=78880 width=28) (actual time=5.088..52473.592 rows=83751 loops=1)
           Index Cond: (approval_merge_request_rules_1.project_id = <large_project_id>)
           Buffers: shared hit=56 read=61996 dirtied=2802
           WAL: records=3266 fpi=2802 bytes=21983642
           I/O Timings: read=52155.137 write=0.000
   ->  Nested Loop  (cost=0.98..1790.15 rows=1 width=8) (actual time=52592.168..57119.422 rows=1000 loops=1)
         Buffers: shared hit=24212 read=67590 dirtied=2817
         WAL: records=3281 fpi=2817 bytes=22073833
         I/O Timings: read=56658.327 write=0.000
         ->  Nested Loop  (cost=0.42..1788.06 rows=1 width=12) (actual time=52574.721..52584.396 rows=7188 loops=1)
               Join Filter: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
               Rows Removed by Join Filter: 41578
               Buffers: shared hit=60 read=61999 dirtied=2802
               WAL: records=3266 fpi=2802 bytes=21983642
               I/O Timings: read=52160.382 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..3.44 rows=1 width=8) (actual time=5.284..5.286 rows=2 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=4 read=3
                     I/O Timings: read=5.244 write=0.000
               ->  CTE Scan on project_approval_merge_request_rules approval_merge_request_rules  (cost=0.00..1774.80 rows=786 width=20) (actual time=8.940..26283.172 rows=24383 loops=2)
                     Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>)
                     Rows Removed by Filter: 36292
                     Buffers: shared hit=56 read=61996 dirtied=2802
                     WAL: records=3266 fpi=2802 bytes=21983642
                     I/O Timings: read=52155.137 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.09 rows=1 width=8) (actual time=0.630..0.630 rows=0 loops=7188)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 17
               Buffers: shared hit=24152 read=5591 dirtied=13
               WAL: records=13 fpi=13 bytes=82013
               I/O Timings: read=4497.945 write=0.000
Select, large project, warm
 Limit  (cost=88173.87..89963.04 rows=1 width=8) (actual time=110.272..132.599 rows=1000 loops=1)
   Buffers: shared hit=91697
   I/O Timings: read=0.000 write=0.000
   CTE project_approval_merge_request_rules
     ->  Index Scan using index_approval_merge_request_rules_on_project_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.57..88172.89 rows=78880 width=28) (actual time=0.018..88.497 rows=83751 loops=1)
           Index Cond: (approval_merge_request_rules_1.project_id = <large_project_id>)
           Buffers: shared hit=61951
           I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.98..1790.15 rows=1 width=8) (actual time=110.271..132.511 rows=1000 loops=1)
         Buffers: shared hit=91697
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.42..1788.06 rows=1 width=12) (actual time=110.235..114.250 rows=7188 loops=1)
               Join Filter: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
               Rows Removed by Join Filter: 41578
               Buffers: shared hit=61958
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..3.44 rows=1 width=8) (actual time=0.018..0.019 rows=2 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=7
                     I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on project_approval_merge_request_rules approval_merge_request_rules  (cost=0.00..1774.80 rows=786 width=20) (actual time=0.019..55.308 rows=24383 loops=2)
                     Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>)
                     Rows Removed by Filter: 36292
                     Buffers: shared hit=61951
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=7188)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 15
               Buffers: shared hit=29739
               I/O Timings: read=0.000 write=0.000
Select, small project, warm
 Limit  (cost=1242.57..1272.22 rows=1 width=8) (actual time=0.367..0.570 rows=3 loops=1)
   Buffers: shared hit=191
   I/O Timings: read=0.000 write=0.000
   CTE project_approval_merge_request_rules
     ->  Index Scan using index_approval_merge_request_rules_on_project_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.57..1241.59 rows=1104 width=28) (actual time=0.031..0.251 rows=60 loops=1)
           Index Cond: (approval_merge_request_rules_1.project_id = <small_project_id>)
           Buffers: shared hit=61
           I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.98..30.63 rows=1 width=8) (actual time=0.366..0.568 rows=3 loops=1)
         Buffers: shared hit=191
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.42..28.41 rows=1 width=12) (actual time=0.323..0.336 rows=30 loops=1)
               Join Filter: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
               Rows Removed by Join Filter: 30
               Buffers: shared hit=68
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..3.44 rows=1 width=8) (actual time=0.040..0.041 rows=2 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=7
                     I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on project_approval_merge_request_rules approval_merge_request_rules  (cost=0.00..24.84 rows=11 width=20) (actual time=0.019..0.142 rows=30 loops=2)
                     Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>)
                     Rows Removed by Filter: 30
                     Buffers: shared hit=61
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.22 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=30)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 0
               Buffers: shared hit=123
               I/O Timings: read=0.000 write=0.000
Delete of 1000 ids, warm
 Delete on public.approval_merge_request_rules  (cost=0.57..2370.66 rows=0 width=0) (actual time=18.674..18.675 rows=0 loops=1)
   Buffers: shared hit=7001 dirtied=992
   WAL: records=1992 fpi=992 bytes=8131300
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using approval_merge_request_rules_pkey on public.approval_merge_request_rules  (cost=0.57..2370.66 rows=1000 width=6) (actual time=0.073..17.581 rows=1000 loops=1)
         Index Cond: (approval_merge_request_rules.id = ANY ('{1000 ids}'::bigint[]))
         Buffers: shared hit=4985 dirtied=992
         WAL: records=992 fpi=992 bytes=8077300
         I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_a_1163139608 for constraint fk_7aaf7f1cba: time=5.949 calls=1000
Trigger RI_ConstraintTrigger_a_43666 for constraint fk_rails_5b2ecf6139: time=7.324 calls=1000
Trigger RI_ConstraintTrigger_a_44201 for constraint fk_rails_6577725edb: time=8.206 calls=1000
Trigger RI_ConstraintTrigger_a_45064 for constraint fk_rails_80e6801803: time=26.604 calls=1000
Trigger RI_ConstraintTrigger_a_47302 for constraint fk_rails_e605a04f76: time=18.984 calls=1000

Covering index evaluation

Measured on a production-size thin clone (table about 272M rows, 51 GB, 15 indexes). Worst case: the largest policy configuration (about 2.8M rows) and a project with about 100k rows (about 59k of them in that configuration), policy with 2 rules. One loop iteration, 1000 ids selected.

Candidate, created on the clone only, followed by ANALYZE:

CREATE INDEX idx_test_amr_cover ON approval_merge_request_rules (project_id, security_orchestration_policy_configuration_id, approval_policy_rule_id, id);

With it the planner no longer needs the CTE, so the query becomes:

SELECT "approval_merge_request_rules"."id" FROM "approval_merge_request_rules" INNER JOIN "merge_requests" ON "merge_requests"."id" = "approval_merge_request_rules"."merge_request_id" WHERE "approval_merge_request_rules"."project_id" = <large_project_id> AND "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = <configuration_id> AND "approval_merge_request_rules"."approval_policy_rule_id" IN (SELECT "approval_policy_rules"."id" FROM "approval_policy_rules" WHERE "approval_policy_rules"."security_policy_id" = <security_policy_id>) AND "merge_requests"."state_id" != 3 LIMIT 1000
Variant Execution Buffers
This MR (CTE), fresh clone, cold 28.2 s hit 8,397, read 35,812
This MR (CTE), warm 68.2 ms hit 44,209
Covering index, no CTE, first run after the build 71.6 ms hit 12,216, read 96
Covering index, no CTE, warm 10.8 ms hit 12,312
  • The CTE plan reads about 48k heap rows of the project (33k pages) to return 1000 ids. The indexed plan reads 2.3k index entries (about 2.2k buffers); most remaining buffers are the merge_requests probes, which are the same in both plans.
  • The index is not index-only: merge_request_id is not in it, so the matched rows still touch the heap. A fully covering variant (INCLUDE (merge_request_id)) was not measured.
  • A true cold run of the candidate was not measured: the clone cache was warm from the build and earlier runs. The cold gain above is the avoided heap scan, not a measured number.
  • Size on the clone: 11 GB, the same as the existing (security_orchestration_policy_configuration_id, approval_policy_rule_id, id) index. Build time: 15.5 min.
Plan: this MR (CTE), cold
 Limit  (cost=91210.28..93061.60 rows=1 width=8) (actual time=5341.379..28239.747 rows=1000 loops=1)
   Buffers: shared hit=8397 read=35812 dirtied=60
   WAL: records=91 fpi=60 bytes=483556
   I/O Timings: read=28060.890 write=0.000
   CTE project_approval_merge_request_rules
     ->  Index Scan using index_approval_merge_request_rules_on_project_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.57..91209.30 rows=81695 width=28) (actual time=4.710..25924.843 rows=47875 loops=1)
           Index Cond: (approval_merge_request_rules_1.project_id = <large_project_id>)
           Buffers: shared read=33103 dirtied=60
           WAL: records=91 fpi=60 bytes=483556
           I/O Timings: read=25804.855 write=0.000
   ->  Nested Loop  (cost=0.98..1852.29 rows=1 width=8) (actual time=5341.378..28239.492 rows=1000 loops=1)
         Buffers: shared hit=8397 read=35812 dirtied=60
         WAL: records=91 fpi=60 bytes=483556
         I/O Timings: read=28060.890 write=0.000
         ->  Nested Loop  (cost=0.42..1850.20 rows=1 width=12) (actual time=103.977..25970.810 rows=2568 loops=1)
               Join Filter: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
               Rows Removed by Join Filter: 12035
               Buffers: shared hit=4 read=33106 dirtied=60
               WAL: records=91 fpi=60 bytes=483556
               I/O Timings: read=25810.614 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..1.94 rows=1 width=8) (actual time=5.812..5.812 rows=1 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=4 read=3
                     I/O Timings: read=5.759 write=0.000
               ->  CTE Scan on project_approval_merge_request_rules approval_merge_request_rules  (cost=0.00..1838.14 rows=810 width=20) (actual time=91.158..25962.027 rows=14603 loops=1)
                     Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>)
                     Rows Removed by Filter: 33272
                     Buffers: shared read=33103 dirtied=60
                     WAL: records=91 fpi=60 bytes=483556
                     I/O Timings: read=25804.855 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.10 rows=1 width=8) (actual time=0.883..0.883 rows=0 loops=2568)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 7
               Buffers: shared hit=8393 read=2706
               I/O Timings: read=2250.276 write=0.000
Plan: this MR (CTE), warm
 Limit  (cost=91210.28..93061.60 rows=1 width=8) (actual time=11.674..68.156 rows=1000 loops=1)
   Buffers: shared hit=44209
   I/O Timings: read=0.000 write=0.000
   CTE project_approval_merge_request_rules
     ->  Index Scan using index_approval_merge_request_rules_on_project_id on public.approval_merge_request_rules approval_merge_request_rules_1  (cost=0.57..91209.30 rows=81695 width=28) (actual time=0.027..47.494 rows=47875 loops=1)
           Index Cond: (approval_merge_request_rules_1.project_id = <large_project_id>)
           Buffers: shared hit=33103
           I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=0.98..1852.29 rows=1 width=8) (actual time=11.673..68.064 rows=1000 loops=1)
         Buffers: shared hit=44209
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.42..1850.20 rows=1 width=12) (actual time=0.199..59.645 rows=2568 loops=1)
               Join Filter: (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id)
               Rows Removed by Join Filter: 12035
               Buffers: shared hit=33110
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..1.94 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=7
                     I/O Timings: read=0.000 write=0.000
               ->  CTE Scan on project_approval_merge_request_rules approval_merge_request_rules  (cost=0.00..1838.14 rows=810 width=20) (actual time=0.162..58.508 rows=14603 loops=1)
                     Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>)
                     Rows Removed by Filter: 33272
                     Buffers: shared hit=33103
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.10 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2568)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 7
               Buffers: shared hit=11099
               I/O Timings: read=0.000 write=0.000
Plan: covering index, no CTE, warm
 Limit  (cost=1.55..7.64 rows=1 width=8) (actual time=0.223..10.849 rows=1000 loops=1)
   Buffers: shared hit=12312
   I/O Timings: read=0.000 write=0.000
   ->  Nested Loop  (cost=1.55..7.64 rows=1 width=8) (actual time=0.222..10.770 rows=1000 loops=1)
         Buffers: shared hit=12312
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.99..5.54 rows=1 width=12) (actual time=0.113..4.283 rows=2341 loops=1)
               Buffers: shared hit=2161
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using idx_approval_policy_rules_security_policy_id_id on public.approval_policy_rules  (cost=0.42..1.94 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)
                     Index Cond: (approval_policy_rules.security_policy_id = <security_policy_id>)
                     Heap Fetches: 0
                     Buffers: shared hit=7
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using idx_test_amr_cover on public.approval_merge_request_rules  (cost=0.57..3.59 rows=1 width=20) (actual time=0.091..4.032 rows=2341 loops=1)
                     Index Cond: ((approval_merge_request_rules.project_id = <large_project_id>) AND (approval_merge_request_rules.security_orchestration_policy_configuration_id = <configuration_id>) AND (approval_merge_request_rules.approval_policy_rule_id = approval_policy_rules.id))
                     Buffers: shared hit=2154
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..2.10 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2341)
               Index Cond: (merge_requests.id = approval_merge_request_rules.merge_request_id)
               Heap Fetches: 7
               Buffers: shared hit=10151
               I/O Timings: read=0.000 write=0.000

Recommendation: do not add the index in this MR. It cuts one loop iteration from 68 ms to 11 ms warm and removes the repeated heap reads, but it is the 16th index on a 272M-row table that is already over the size limit, costs 11 GB, and needs a database team exception plus a post-deploy migration. This MR already removes the configuration-wide scan (558 batches to the project's own rows), and the two follow-ups cut the number of calls. Revisit if production shows the cold first-iteration cost still matters after those land.

How to set up and validate locally

New specs:

  • keeps rules of other projects and of merged merge requests
  • query-count spec (batch size stubbed to 1) that fails on master (23 queries vs 19) because the old code iterated other projects' rules
bundle exec rspec \
  ee/spec/models/security/policy_spec.rb \
  ee/spec/services/security/security_orchestration_policies/sync_project_service_spec.rb \
  ee/spec/services/security/scan_result_policies/approval_rules/delete_service_spec.rb

333 examples pass across these three files. RuboCop is clean.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Alan (Maciej) Paruszewski

Merge request reports

Loading
Loading