Detach and drop archived partitions of p_ci_job_definitions/p_ci_job_definition_instances

Problem

To further reclaim space on an ongoing basis, with the pipeline archival period enabled on Gitlab.com we could align the creation of CI partitions to a more time based so that archiving old partitions is more straight forward.

Once all pipelines in a partition are archived we can truncate the old partitions, reclaiming space from unused data.

Dependencies

⚠️ Removing archived pipelines is subordinate to Enable pipeline archival on Gitlab.com (&19547) ⚠️

Proposal

Extend the partitionable annotation with a detach_archived: option, defaulting to false:

partitionable scope: ->(_) { Ci::Pipeline.current_partition_value },
  partitioned: true,
  detach_archived: true

Apply it to Ci::JobDefinition and Ci::JobDefinitionInstance. With detach_archived: false, detach_partition_if keeps returning proc { false }, so every other partitionable model is unaffected.

Estimated reclaim across both tables: 2.3TB.

Implementation

The annotation on its own is a no-op. Three layers are needed.

  1. Ci::Partitionable (partitionable.rb#L96). Replace the hardcoded detach_partition_if: proc { false } with a proc that returns true only when every value in the MultipleNumericListPartition belongs to an archived Ci::Partition. One database partition can hold several values, for example [100, 101, 102]. Gate the proc behind an ops feature flag (ci_detach_archived_partitions, default off) so the code can ship and the extra_partitions metric can be observed before any DETACH runs.

  2. CiSlidingListStrategy#extra_partitions (ci_sliding_list_strategy.rb#L33). It hard-returns [] and ignores detach_partition_if entirely; the spec asserts empty for both true and false. It needs a real implementation mirroring SlidingListStrategy#extra_partitions, operating on values rather than a single value, and keeping the guards that never detach the active partition or the partition matching the column default.

  3. Reading the archived state. Ci::Partition's archived status is set by Ci::Partitions::ArchiveService but nothing reads it today. This would be the first production consumer.

Nothing downstream changes. A detached partition is recorded in Postgresql::DetachedPartition and dropped by DetachedPartitionDropper, which is strategy-agnostic and already runs on the ci connection.

Ordering

p_ci_job_definition_instances partitions must detach before the matching p_ci_job_definitions partitions, otherwise instance rows reference a detached parent.

Open question: how to detach p_ci_job_definitions

Needs Database team input before implementation starts.

p_ci_job_definition_instances has no inbound foreign keys, so PartitionManager can detach its partitions once the two layers above land.

p_ci_job_definitions cannot. fk_rails_0f67af8ad0_p references it from p_ci_job_definition_instances (partition_id, job_definition_id), and assert_partition_detachable! raises UnsafeToDetachPartitionError for any inbound foreign key on the parent table. This foreign key must be kept, so removing it in a post-migration is not an option.

Context for that discussion:

  • The guard is operational rather than a correctness rule. Its message reads "it would block while checking foreign key". It was added in 2021 (c8b84ced3234) as a safety rail for an unattended background worker, before DETACH PARTITION CONCURRENTLY was available.

The Ruby work in layers 1 and 2 can ship independently behind the feature flag while this is settled.

Implementation Plan

See #552078 (comment 3706986153).

Edited by Leaminn Ma