Draft: Add BatchedPreDrain helper for CASCADE FK children

What this does

Adds a new helper class Gitlab::Database::BatchedPreDrain.

When we delete a record that has many CASCADE foreign-key children (for example a large project or group), PostgreSQL deletes every child row inside the single transaction that removes the parent. On big records that transaction becomes huge and runs for a long time, which can cause problems on the database.

This helper deletes those children first, in small batches, before the parent is removed. Each batch is its own DELETE statement, so no single transaction grows large.

How it works

1. Finds the children from the schema. It reads the CASCADE foreign keys that point at the parent table, then follows them down to grandchildren and deeper.
2. Deletes leaves first. It uses TablesSortedByForeignKeys so grandchildren are removed before the children they belong to.
3. Deletes in small batches. Each batch is a DELETE ... LIMIT n scoped to the one record being drained.
4. Respects a budget. It stops after a time or row limit (reusing LooseForeignKeys::ModificationTracker's 30s / 100k defaults) and returns :over_budget so the caller can run it again later. Otherwise it returns :completed.
5. Supports per-table hooks. Some tables need extra work on delete (for example object storage or search index cleanup). Callers can register a hook for those tables instead of a plain SQL delete.
6. Returns per-table counts. The result includes how many rows were deleted from each table.
7. Skips cycles. If a group of tables all reference each other through CASCADE, the helper leaves them alone and lets the parent DELETE handle them.

This is a building block only. No existing code calls it yet; that wiring comes in follow-up work.

Usage

result = Gitlab::Database::BatchedPreDrain.new(project).execute
result.status           # => :completed or :over_budget
result.deleted_by_table # => { "issues" => 1200, "notes" => 4300 }

Callers must run this outside a transaction so each batch commits on its own.

Testing

New spec builds a small throwaway schema (parent → child → grandchild, plus a cycle) and covers:

• children deleted in dependency order (leaves first)
• budget exhaustion returns :over_budget
• per-table hook is used for registered tables
• all-CASCADE cycles are skipped
• result includes per-table counts
• rows for other parents are left untouched

Closes #619116

Data deletion and recoverability

This helper deletes CASCADE foreign-key children of a parent record. It does not delete anything that would survive the parent's own destruction: these are exactly the rows PostgreSQL would delete anyway when the parent DELETE cascades. The helper only moves that deletion earlier and splits it into small committed batches, so the end state is identical to a normal cascading delete.

Deleted rows are not independently recoverable. Recovery follows the same path as any other row loss — a point-in-time restore from database backups. Because this is a building block with no callers yet, nothing triggers these deletions in this MR; the actual deletion behaviour and its safeguards will be reviewed when a caller wires it up.

Database review

The helper generates one query shape per child table: a batched DELETE ... WHERE (pk) IN (SELECT pk ... WHERE fk IN (<ancestor scope>) LIMIT n). For a direct child the ancestor scope is the parent id; for a grandchild it is a nested subselect back up to the root. No new indexes are added — every FK column these queries filter on is already indexed.

Plans below were captured on gitlab-production-main (Database Lab) using the gitlab-org namespace (id = 9970) and the default batch size of 1000.

Direct child: members

DELETE FROM members
WHERE (members.id) IN (
  SELECT members.id
  FROM members
  WHERE members.member_namespace_id IN (9970)
  LIMIT 1000
);

Index Only Scan on index_members_on_member_namespace_id_compound, then members_pkey for the delete. No sequential scans. Runtime 2296ms, almost entirely cold-cache I/O (I/O Timings: read=2265ms); the plan work itself is ~30ms. Plan

EXPLAIN (ANALYZE, BUFFERS)

Delete on public.members  (cost=45.23..3636.67 rows=0 width=0) (actual time=2296.384..2296.387 rows=0 loops=1)
   Buffers: shared hit=7074 read=1770 dirtied=996
   WAL: records=1016 fpi=963 bytes=7882879
   I/O Timings: read=2265.129 write=0.000
   ->  Nested Loop  (cost=45.23..3636.67 rows=1000 width=34) (actual time=129.588..2283.336 rows=1000 loops=1)
         Buffers: shared hit=4141 read=1767 dirtied=16
         ->  HashAggregate  (cost=44.67..54.67 rows=1000 width=32) (actual time=124.314..125.267 rows=1000 loops=1)
               Group Key: "ANY_subquery".id
               ->  Subquery Scan on "ANY_subquery"  (cost=0.57..42.17 rows=1000 width=32) (actual time=7.123..123.770 rows=1000 loops=1)
                     ->  Limit  (cost=0.57..32.17 rows=1000 width=4) (actual time=7.104..123.422 rows=1000 loops=1)
                           ->  Index Only Scan using index_members_on_member_namespace_id_compound on public.members members_1  (cost=0.57..101.05 rows=3180 width=4) (actual time=7.103..123.277 rows=1000 loops=1)
                                 Index Cond: (members_1.member_namespace_id = 9970)
                                 Heap Fetches: 31
         ->  Index Scan using members_pkey on public.members  (cost=0.57..3.58 rows=1 width=10) (actual time=2.156..2.156 rows=1 loops=1000)
               Index Cond: (members.id = "ANY_subquery".id)
Trigger RI_ConstraintTrigger_a_39064 for constraint fk_b2e4a4b68a: time=13.123 calls=1000

Direct child: namespace_settings

DELETE FROM namespace_settings
WHERE (namespace_settings.namespace_id) IN (
  SELECT namespace_settings.namespace_id
  FROM namespace_settings
  WHERE namespace_settings.namespace_id IN (9970)
  LIMIT 1000
);

namespace_settings is keyed by namespace_id, so both the scope and the delete use namespace_settings_pkey (Index Only Scan). Runtime 5.6ms. Plan

EXPLAIN (ANALYZE, BUFFERS)

Delete on public.namespace_settings  (cost=4.16..7.19 rows=0 width=0) (actual time=5.619..5.621 rows=0 loops=1)
   Buffers: shared hit=9 read=5 dirtied=3
   ->  Nested Loop  (cost=4.16..7.19 rows=1 width=34) (actual time=5.580..5.583 rows=1 loops=1)
         ->  HashAggregate  (cost=3.59..3.60 rows=1 width=32) (actual time=5.560..5.562 rows=1 loops=1)
               Group Key: "ANY_subquery".namespace_id
               ->  Subquery Scan on "ANY_subquery"  (cost=0.56..3.59 rows=1 width=32) (actual time=5.544..5.555 rows=1 loops=1)
                     ->  Limit  (cost=0.56..3.58 rows=1 width=4) (actual time=5.507..5.517 rows=1 loops=1)
                           ->  Index Only Scan using namespace_settings_pkey on public.namespace_settings namespace_settings_1  (cost=0.56..3.58 rows=1 width=4) (actual time=5.505..5.515 rows=1 loops=1)
                                 Index Cond: (namespace_settings_1.namespace_id = 9970)
                                 Heap Fetches: 2
         ->  Index Scan using namespace_settings_pkey on public.namespace_settings  (cost=0.56..3.58 rows=1 width=10) (actual time=0.016..0.017 rows=1 loops=1)
               Index Cond: (namespace_settings.namespace_id = "ANY_subquery".namespace_id)

Grandchild: member_approvals (namespaces ← members ← member_approvals)

DELETE FROM member_approvals
WHERE (member_approvals.id) IN (
  SELECT member_approvals.id
  FROM member_approvals
  WHERE member_approvals.member_id IN (
    SELECT members.id
    FROM members
    WHERE members.member_namespace_id IN (9970)
  )
  LIMIT 1000
);

Demonstrates the nested-subselect ancestor scope for a grandchild. member_approvals holds no rows for this namespace, so the outer scan returns 0 rows and the members subquery is never executed. When rows exist the planner uses index_member_approval_on_member_id and members_pkey (both indexed). Runtime 0.005ms. Plan

EXPLAIN (ANALYZE, BUFFERS)

Delete on public.member_approvals  (cost=0.57..3.61 rows=0 width=0) (actual time=0.004..0.005 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=0.57..3.61 rows=1 width=38) (actual time=0.003..0.004 rows=0 loops=1)
         Join Filter: ("ANY_subquery".id = member_approvals.id)
         ->  Seq Scan on public.member_approvals  (cost=0.00..0.00 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=1)
         ->  Subquery Scan on "ANY_subquery"  (cost=0.57..3.60 rows=1 width=40) (never executed)
               ->  Limit  (cost=0.57..3.59 rows=1 width=8) (never executed)
                     ->  Nested Loop  (cost=0.57..3.59 rows=1 width=8) (never executed)
                           ->  Seq Scan on public.member_approvals member_approvals_1  (cost=0.00..0.00 rows=1 width=16) (never executed)
                           ->  Index Scan using members_pkey on public.members  (cost=0.57..3.59 rows=1 width=4) (never executed)
                                 Index Cond: (members.id = member_approvals_1.member_id)
                                 Filter: (members.member_namespace_id = 9970)