Build a generic batched pre-drain helper for CASCADE FK children

Summary

Build a generic helper (e.g. Gitlab::Database::BatchedPreDrain) that deletes CASCADE FK children in small committed batches before the parent record is destroyed. This is the core building block for fixing long-running transactions in project and group deletion.

The helper takes any ActiveRecord parent, finds its CASCADE children from the database schema, and deletes them in dependency order. Each batch is committed independently, so no single transaction grows large.

Requirements

  1. Find children from the schema. Query CASCADE FKs referencing the parent table using PostgresForeignKey.by_referenced_table_name(...).not_inherited.where(on_delete_action: :cascade).
  2. Delete leaves first. Use TablesSortedByForeignKeys to order tables so grandchildren are deleted before their parents.
  3. Delete in batches. Run DELETE ... WHERE fk_column = parent.id LIMIT n and commit each batch separately.
  4. Respect a budget. Stop after a time or row limit (e.g. 30s / 100k deletes, like LooseForeignKeys::ModificationTracker). Return :completed or :over_budget so the caller can re-enqueue.
  5. Support per-table hooks. Some tables need side effects on delete (object storage cleanup, search index removal). Provide a hook so callers can register a custom strategy for those tables instead of raw SQL DELETE.
  6. Return structured results. Include per-table delete counts and overall status.
  7. Handle cycles. Skip all-CASCADE cycles. PostgreSQL resolves them during the parent DELETE.

Out of scope

  • RESTRICT FK handling or retry loops. FKs stay CASCADE.
  • PG_DIAG_CONSTRAINT_NAME extraction.
  • MAX_ROUNDS_PER_TABLE or abort logic.
  • Write-blocking.

Existing code to reuse

  • Gitlab::Database::PostgresForeignKey for FK discovery
  • Gitlab::Database::TablesSortedByForeignKeys for ordering
  • LooseForeignKeys::CleanerService Arel builder for batched deletes
  • EachBatch / BatchDeleteDependentAssociations patterns

Specs

Use a throwaway schema (pattern: spec/lib/gitlab/database/...):

  • Children deleted in dependency order
  • Budget exhaustion returns :over_budget
  • Per-table hook called for registered tables
  • All-CASCADE cycles skipped
  • Result includes per-table counts

References

Edited by Aakriti Gupta