Rethink how we detect redundant pipelines to remove partitioned-table scans

Problem to solve

Ci::CancelRedundantPipelinesWorker detects redundant pipelines by querying p_ci_pipelines (and p_ci_builds for interruptibility) for a ref, filtered by status, with a LIMIT. This is a scan/join against large partitioned tables on a hot path.

Epic &12419 (closed) improved these queries and was closed on the premise that the service no longer consumes notable resources. PG18 puts that premise at risk: the appendrel planner change in commit fbc0fe9a2e collapses row estimates for a filtered join through a partitioned parent on a unique key, drops the LIMIT early-exit, and prices the plan to completion. This is the query shape we use. See the GPRD CI analysis in gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker#755.

This issue is a discussion. The goal is to agree on whether it's time to change the approach, and if so, which direction. Please add your ideas in the comments.

What we've already done

  • #463623 (closed): removed the hierarchy CTE and restricted the query to cancellable statuses. Significant improvement.
  • #491743 (closed): shifted reads toward replicas.
  • Shortened the cancel window (now 24h) and reduced the initial candidate scope.
  • Rejected: doing all cancellations synchronously from one job (!173049 (closed)), after it flooded a self-managed instance (gitlab-com/ops-sub-department/section-ops-request-for-help#484).

What we've considered before (deferred, not rejected)

Raised in &12419 (closed) and shelved because the query rework made the pain go away at the time:

  • Each pipeline checks "am I still the latest?" during processing and on job-trace append (#438101 (closed), @hfyngvason). Also flagged a missing index for the interruptible = false check.
  • Cache the latest pipeline.id per ref, populated at creation (@allison.browne).

Blockers raised on the original spike !137900 (closed):

  1. A parent can complete while children still run, so we still need the hierarchy.
  2. Partitioning by creation date conflicts with retries writing to old partitions.
  3. It adds complexity to the state machine.

What we could explore now

Options to weigh, not a chosen design. Trade-offs and risks welcome in the comments:

  • Event-driven signal. Maintain "current head / candidates per ref" on pipeline events (Ci::PipelineCreatedEvent already exists) and read that instead of scanning. Open: where to store it (Redis? a narrow table?), how to represent child hierarchy, durability/fallback.
  • Bounded external store (e.g. Redis) with TTL. The 24h window means candidates expire on their own. Open: cold-cache correctness, memory on high-volume monorepos, self-managed parity.
  • PG18-safe query rewrite. Keep the query but rewrite it (e.g. LATERAL + inner LIMIT) to dodge the estimate collapse. Lower risk, but keeps us on the partitioned tables.
  • Better indexing for the interruptibility check.

Open questions

  • Is PG18 enough reason to reopen this now, or do we wait for observed regression?
  • If we go event-driven, how do we keep it correct and self-managed-friendly without a durable store?
  • How do we handle the child-pipeline hierarchy without the recursive CTE?

Links / references