LooseForeignKeys::CleanupWorker silently falls behind; loose_foreign_keys_deleted_records grows unbounded

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Note

This is a collection issue. It is used to gather customer signals (RFH issues, Zendesk tickets) that will inform database state work. See the parent epic for instructions on adding a new signal topic.

Warning

Privacy: These issues are not confidential. Do not include any customer-identifying information — no customer names, company names, or details that could identify a specific customer. See the SAFE framework for guidance. Version numbers and general configuration details are fine.

Background

The Loose Foreign Keys (LFK) framework removes related records asynchronously: triggers append rows to loose_foreign_keys_deleted_records (a partitioned table) when a parent row is deleted, and LooseForeignKeys::CleanupWorker runs on a 1-minute cron to process those rows in batches.

On large or busy self-managed instances the worker silently falls behind, and the failure modes are not surfaced anywhere an operator looks. The pattern recurs in several recognizable shapes:

  • Worker runs but delete_count: 0. Scheduled invocations succeed but no records are processed. loose_foreign_keys_deleted_records continues to grow; old partitions never drop because they still contain unprocessed rows. The recurring diagnostic is the PartitionManager bug pattern documented in the LFK runbook, where records are stranded in a non-active partition that the worker no longer reads.
  • Statement-timeout on specific deletion queries. Worker invocations log PG::QueryCanceled: ERROR: canceling statement due to statement timeout on the LFK cleanup query. The hot queries are typically UPDATE "gitlab_partitions_dynamic"."ci_builds" SET "user_id" = NULL ... or UPDATE ... SET "project_id" = NULL ... for partitioned tables (p_ci_builds, p_ci_pipelines). The default batch size and per-query timeout are not tunable from operator-facing configuration.
  • Large-deletion backlogs the worker cannot drain. A single customer-initiated event (e.g. enabling pipeline expiration on a large project, mass project deletion) creates hundreds of millions of rows in loose_foreign_keys_deleted_records. The default batch size cannot drain it within the cron cadence, and the backlog grows monotonically.
  • Cleanup mid-backup produces restore-time failures. When the worker creates a new partition of loose_foreign_keys_deleted_records while gitlab-backup create is running, the resulting dump captures the partition table and its index in inconsistent states. Restore then fails with cannot attach index ... as a partition of index .... The documented mitigation (run the cleanup worker manually before backup) is unenforceable on a 1-minute cron.

Across all of these, the team's remediations are folkloric and not in customer-facing docs:

  • Enabling the loose_foreign_keys_turbo_mode_main feature flag for the cleanup worker.
  • Running a one-off Ruby snippet that overrides LooseForeignKeys::ProcessDeletedRecordsService::BATCH_SIZE and CUSTOM_STATEMENT_TIMEOUT at runtime — with the caveat that under transaction-pooled PgBouncer the per-session statement timeout is silently ignored.
  • Manually re-inserting unprocessed rows from inactive partitions into the active partition so the worker will pick them up.
  • A queryable per-partition status snapshot: 
    SELECT partition, fully_qualified_table_name, count(*)
FROM loose_foreign_keys_deleted_records
WHERE status = 1
GROUP BY 1, 2;

The signal is that the LFK framework has no operator-visible health or backlog metric, no enforceable invariant tying cleanup state to backup safety, and no documented escape hatch for the predictable cases (large-deletion backlog, stranded inactive-partition rows, statement-timeout on specific queries). Customers only learn the worker is stuck through secondary symptoms — DB growth, restore failures, or downstream FK-related performance issues.

This is a long-running pattern: an earlier occurrence was tracked in gitlab-com/request-for-help#2336 (2024), whose remediation recipe is still being linked verbatim across 2025–2026 RFHs — indicating the framework's operability has not improved despite repeat exposure.

Affected versions span 17.x and 18.x; affected installations are large self-managed deployments, frequently on managed PG, with high data churn (CI pipeline expiration, project deletion campaigns).

Contributing References

Add references as comments on this issue, not inline here. Each comment should include:

  1. A 1–3 sentence summary of the customer problem, written without any customer-identifying information.
  2. The link to the RFH issue or Zendesk ticket.

Links should be to an RFH issue, Zendesk ticket, or GitLab bug issue. If your reference doesn't fit the signal described here, check the parent epic for instructions on adding a new signal topic.

This issue should have at least 2 references before it is considered active.

Edited by 🤖 GitLab Bot 🤖