<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=600628) </details> <!--IssueSummary end--> > [!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](https://handbook.gitlab.com/handbook/legal/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](https://docs.gitlab.com/development/database/loose_foreign_keys/#partitionmanager-bug), 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: ```sql 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`](https://gitlab.com/gitlab-com/request-for-help/-/issues/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](https://gitlab.com/groups/gitlab-org/-/work_items/21682) for instructions on adding a new signal topic. **This issue should have at least 2 references before it is considered active.**