<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/engineering/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Label this issue](https://contributors.gitlab.com/manage-issue?action=label&projectId=278964&issueIid=629095) - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=629095) </details> <!--IssueSummary end--> ### Context The `ci_builds` partition has accumulated substantial bloat and is now large enough that copy-based maintenance can affect production query latency. This discussion is based on the [Slack thread](https://gitlab.slack.com/archives/C01FR4QPNA2/p1788441929070009). The current `ci_builds` partition (`partition_id = 100`) was reported at approximately **5.36 TB total**: 2.88 TB heap and 1.98 TB indexes. A later reference reported approximately **1.99 TB of bloat** in `ci_builds` from a CI thin clone. Two production incidents occurred while using `pg_repack` on CI. The thread identifies the important mechanism: `pg_repack` runs the initial `INSERT ... SELECT` copy inside a serializable transaction, keeping a snapshot open for roughly 70 minutes in the discussed run and pinning the database-wide `xmin` horizon. This can prevent vacuum from removing dead tuples, degrading queue-like workloads and other queries that must scan through accumulating dead tuples. The issue is therefore not necessarily specific to one tool: large copy-based maintenance can reproduce the same failure mode. `pg_squeeze` is not considered a clean escape because it also performs a full data copy and can pin `xmin`; `pgcompacttable` is slow and requires an exclusive lock. The thread also calls out the need to identify the queries that degraded, reproduce the workload in benchmarking/DBLab, and measure the relationship between `xmin` horizon age and query degradation before committing to a large operation. ### Options under consideration #### Option 1: Repack on the PG18 upgrade target Run `VACUUM FULL` or `pg_repack` against the target database during the PG18 expand/contract upgrade, before the target is promoted and receives production read traffic. **Pros** - Avoids pinning `xmin` on the production source that is serving traffic. - Avoids competing directly with production user I/O on the source during the copy. - Uses an already-planned maintenance window and could reclaim storage as part of the upgrade work. - Provides a simpler operational model than building a new application-controlled migration path. **Cons / risks** - The target is not necessarily idle: logical replication continues applying CI writes, so the copy and index builds can still pin the target's `xmin` and allow dead tuples to accumulate there. - The target needs cleanup, `VACUUM (FREEZE)`, analysis, replica/logical-replication catch-up, and a healthy baseline before the read switchover. The repacked table is fully unfrozen and lacks visibility-map bits immediately after the copy. - The current rough estimate is highly uncertain. The thread mentions an August estimate of about 24 hours on the source and a possible 10–24 hour window for copying roughly 2.9 TB, building indexes, swapping/dropping, and follow-up maintenance; this has not yet been benchmarked on the actual PG17/PG18 hardware and workload. - Adding a multi-hour operation to the upgrade weekend may consume the buffer needed to validate read traffic, diagnose missed query-plan issues, and patch them safely. The upgrade path itself is not yet finalised. - This path requires an expand/contract migration; a physical standby alone cannot be repacked in place. - It may solve the immediate large-partition problem without establishing a durable size limit or recurring reclaim strategy. #### Option 2: Move live data to a new partition, then drop `ci_builds` Move the rows out of the existing `ci_builds` partition into smaller destination partitions, using the partition key (`partition_id`) and an application/background-operation pattern, then detach/drop the old partition after validation. **Pros** - Can avoid one long-running full-table copy transaction if implemented as throttled batches/loops rather than a single transaction. - Can reduce future blast radius by establishing smaller partitions; the thread references a 50 GiB soft / 100 GiB hard partition-size guideline that should be validated and enforced. - Once data is moved, the oversized source partition can be detached/dropped, reclaiming its storage without repacking the entire multi-terabyte partition in place. - The approach can be health-signal aware and paused/throttled when database or application health degrades. - The current partition layout already has `ci_builds` as the `partition_id = 100` partition of `p_ci_builds`; the proposed move updates `partition_id` while allowing the existing foreign-key cascade path to handle related records. - The previous blocker tracked in [dbo issue #569](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/569#note_2779376913) was reported as fixed, so this approach may be viable to revisit. **Cons / risks** - This is not free: moving rows still generates I/O and WAL, and the migration must be carefully throttled and observable. - A safe implementation needs to account for rows changing after they are copied. A generic background-operation/BBO implementation needs change capture or an equivalent application-controlled migration pattern; existing partition-migration patterns may provide the building blocks, but additional work is required. - The detach/drop step is operationally sensitive and must be designed so it cannot cause an incident. The thread explicitly identifies this as an unresolved concern. - Index creation/recreation on very large partitions may itself pin `xmin` and needs separate timing and workload analysis. - The move may require application or backend ownership and coordination with CI, including validation of foreign keys, indexes, query plans, replication, and rollback. - It does not eliminate the need to fix workload patterns that degrade when dead tuples accumulate; stateful queue queries that advance from the last processed ID were suggested as one mitigation. ### Decision criteria and proposed next steps 1. **Benchmark both paths before selecting one.** Use a representative `ci_builds` clone in db-benchmarking/DBLab, with timings for copy, index creation, swap/detach/drop, vacuum/freeze, replication catch-up, and rollback/cleanup. 2. **Reproduce the incident mechanism.** Identify the production queries that degraded during the prior repack attempts and measure degradation as the `xmin` horizon is held open for increasing durations. 3. **Validate the PG18 target assumptions.** Confirm whether the planned upgrade has enough time for repack plus analyze/freeze/catch-up/read-traffic validation, and whether the target's replication workload changes the risk profile. 4. **Prototype the partition move.** Document batching, throttling, change capture, foreign-key behavior, index handling, validation, rollback, and the exact detach/drop sequence. 5. **Agree and enforce a maximum partition size.** The thread references a 50 GiB soft / 100 GiB hard guideline; confirm whether those limits are appropriate for this workload and how enforcement will work. 6. **Decide whether the immediate fix and generic solution should be separate.** The PG18 target path may be the lowest-risk way to address this instance if it is benchmarked and fits the upgrade schedule, while the partition-move/BBO path may be the durable solution for recurring storage reclamation. ### Open Questions - Which option should be the near-term plan for `ci_builds`? - Is repacking on the PG18 target acceptable only if benchmarking demonstrates a bounded duration and a healthy pre-switchover baseline? - Can the partition move be implemented with existing background-operation/partition-migration patterns, and who owns the implementation: Database Architecture or CI? #### DB headroom priority Removing archived tables from https://gitlab.com/groups/gitlab-org/-/work_items/11231+ will free up more space than this (in main) and also it will set the base for dropping large partition/table which can be extended for _Option 2_ for `ci_builds`