## Summary Roll out detaching and dropping archived partitions of `p_ci_job_definitions` and `p_ci_job_definition_instances`, currently behind the `ci_detach_archived_partitions` feature flag. - DRI: @lma-git - Team Slack channel: `#g_ci-platform` > [!note] > This rollout is a single flip. The actor is > `:instance`. ## The flag - `ci_detach_archived_partitions`, type `gitlab_com_derisk`, default off, actor `:instance` - The guard calls `Feature.enabled?(:ci_detach_archived_partitions, :instance)` - Group: `group::ci platform` - Short-lived: expected to last a few weeks, and the `gitlab_com_derisk` type caps it at 2 months after merge ## What enabling does `Database::PartitionManagementWorker`, a Sidekiq cron worker, starts detaching archived partitions of `p_ci_job_definitions` (the referenced side) and `p_ci_job_definition_instances` (the referencing side). `Database::DropDetachedPartitionsWorker` drops each detached partition after `RETAIN_DETACHED_PARTITIONS_FOR` (1 week), except that a partition whose `pg_total_relation_size` exceeds `MAX_PARTITION_SIZE` (150 GB) defers its drop to the first Saturday after that week. No code change ships in this issue — the flag is the only gate. Partition values 100-105 are archived on GitLab.com because https://gitlab.com/gitlab-org/gitlab/-/work_items/600946 enabled `ci_pipeline_archival_setting`. Values 106-114 are active and 115 is current (as of `2026-09-03`). ## Sizes Measured on a postgres.ai `ci` clone on 2026-09-03. Each partition value is its own physical partition (`FOR VALUES IN ('100')` and so on); sizes are `pg_total_relation_size`, so heap plus indexes plus TOAST. | Value | `ci_job_definition_instances_*` | `ci_job_definitions_*` | |---|---|---| | 100 | 495 GB | 551 GB | | 101 | 132 GB | 179 GB | | 102 | 248 GB | 432 GB | | 103 | 40 GB | 80 GB | | 104 | 49 GB | 92 GB | | 105 | 42 GB | 93 GB | Across 100-105 the instances side totals 1006 GB and the definitions side 1427 GB, so 2.38 TB combined. Instances 100 and 102 and definitions 100, 101 and 102 exceed the 150 GB drop threshold and defer to a Saturday; the rest drop a week after their detach. ## Sequencing The guard sequences the two tables itself: it refuses to detach a definitions partition until its instances counterpart is dropped and no instances partition anywhere is sitting detached. So the whole definitions side waits for the last instances drop, which is the Saturday one. | Phase | What happens | |---|---| | Day 0 | manager detaches all six instances partitions (100-105); every definitions partition is deferred by the guard — a catalog check, no scan, no lock | | Day 7 | dropper drops instances 101, 103, 104, 105 | | First Saturday after day 7 | dropper drops instances 100 and 102, the two over 150 GB. The definitions side stays deferred until this completes | | Next run after that | manager detaches all six definitions partitions | | +7 days | dropper drops definitions 103, 104, 105 | | Following Saturday | dropper drops definitions 100, 101, 102 — the full 2.38 TB reclaimed | While the definitions side waits, expect `Deferred detaching partition` logged at info level with `deferral_reason: counterpart_partition_present` or `deferral_reason: detached_referencing_partition`, once per partition per run. That is the guard working, not an alarm. ## What could go wrong? - **No undo.** Disabling the flag stops further detaches but does not reverse one already made. A detached partition still drops on schedule unless someone deletes its `detached_partitions` bookkeeping row and reattaches it by hand before `drop_after` — a window of a week, or until the Saturday for the large ones. - **The whole backlog detaches at once.** All six instances partitions detach on the first run after enabling, not one at a time. - **A stalled dropper is silent.** `sync_partitions` logs and swallows `StandardError`, so if the dropper stops making progress, detached instances partitions accumulate, the guard defers the definitions side indefinitely, and nothing reclaims space. - **Cost if the guard is bypassed or wrong.** Detaching a definitions partition while another instances partition sits fully detached measured at 6 min 21.8 s cold on the clone, against ~179 ms for `ci_job_definitions_103` when every other instances partition was still attached. The detach runs `CONCURRENTLY`, holding `SHARE UPDATE EXCLUSIVE` on `p_ci_job_definitions` for that duration: DML continues, but `ALTER TABLE`, `VACUUM` and `ANALYZE` block, and the next partition-management run queues behind it. ## What to watch - Gauge `db_partitions_extra` per `table_name`, for both tables, on https://dashboards.gitlab.net. Pinned non-zero is the stall signal. - The overdue-drop gauge from https://gitlab.com/gitlab-org/gitlab/-/work_items/617931 - Sidekiq: `Database::PartitionManagementWorker` and `Database::DropDetachedPartitionsWorker` - Kibana, structured `AppLogger` lines carrying `table_name`, `connection_name` and `partition_name`: `Deferred detaching partition` (also carries `deferral_reason`), `Detached Partition` (also carries `concurrent`), `Dropped previously detached partition`, `Failed to create / detach partition(s)` ## Before production - [ ] The MRs behind this deployed to production and canary, confirmed with `/chatops gitlab run auto_deploy status <merge-commit>` - [ ] Confirm values 100-105 are still `archived` and that the current partition value is well clear of 105 - [ ] DRI available for at least 2 hours after the production flip No change management issue is required: this is an application code change that applies to all instances, not an operational change to GitLab.com. ## Commands Production `/chatops` runs in [`#production`](https://gitlab.slack.com/archives/C101F3796), cross-posted with results to `#g_ci-platform`. **Non-production** ``` /chatops gitlab run feature set ci_detach_archived_partitions true --dev --pre --staging --staging-ref ``` **Production** ``` /chatops gitlab run feature set ci_detach_archived_partitions true ``` **Rollback** ``` /chatops gitlab run feature set ci_detach_archived_partitions false /chatops gitlab run feature set ci_detach_archived_partitions false --dev --pre --staging --staging-ref ``` ## Cleanup Once the drain has completed and the flag is deemed stable, open a cleanup MR removing the flag and its YAML definition, then: ``` /chatops gitlab run release check <merge-request-url> <milestone> /chatops gitlab run feature delete ci_detach_archived_partitions --dev --pre --staging --staging-ref --production ``` After cleanup, roughly one partition per table becomes eligible per month, since partition rollover has settled at 30-31 days.