Split ClickHouse sync workers into recent and backfill modes

Summary

Split ClickHouse sync workers into recent and backfill modes during backfill migration to prioritize processing of newer build records. Gated behind the ci_finished_builds_ch_sync_mode_split feature flag.

Problem

The BackfillCiFinishedBuildsToClickHouse migration inserts ~180 days of historical records into p_ci_finished_build_ch_sync_events. All 5 workers process pending events ordered by build_id ASC, meaning newer records (from ongoing builds) sit behind a large backlog of old records. This delays ClickHouse data freshness for recent builds.

Solution

When the feature flag is enabled and a backfill is in progress, the cron worker splits workers by time:

Workers	Mode	Processes
2	recent	Records with build_finished_at >= 7.days.ago
3	backfill	Records with build_finished_at < 7.days.ago

Each group has independent worker_index/total_workers ranges for build_id % 100 partitioning, so they don't contend with each other.

When mode is set, queries use ORDER BY build_finished_at ASC, build_id ASC instead of the default ORDER BY build_id ASC. This ensures the new index ((build_id % 100), build_finished_at, build_id) WHERE processed = false from !226067 (merged) is used by the planner, avoiding the Rows Removed by Filter problem where the old index scans all unprocessed records and post-filters by build_finished_at.

When the flag is disabled or no backfill is in progress, behavior is identical to current production.

Changes

• Model (finished_build_ch_sync_event.rb): Added order_by_build_finished_at scope using explicit Gitlab::Pagination::Keyset::Order.build with (build_finished_at, build_id) columns for correct keyset pagination
• Service (ci_finished_builds_sync_service.rb): Added mode parameter; when mode is set, uses order_by_build_finished_at + apply_mode_filter; when nil, uses original order_by_build_id
• Worker (ci_finished_builds_sync_worker.rb): Passes mode parameter through to the service
• Cron worker (ci_finished_builds_sync_cron_worker.rb): When flag enabled + backfill in progress, enqueues 2 recent + 3 backfill workers instead of 5 undifferentiated workers
• Feature flag: ci_finished_builds_ch_sync_mode_split (gitlab_com_derisk type)
• Specs: Model spec for order_by_build_finished_at scope, service specs for mode filtering, worker specs for mode passthrough

Rollout plan

1. Merge the index MR first: !226067 (merged)
2. Verify the index index_ci_finished_build_ch_sync_events_on_mode_filter exists in production
3. Enable feature flag ci_finished_builds_ch_sync_mode_split
4. Monitor ClickHouse sync lag and worker throughput

Related

• Index MR (should be merged first): !226067 (merged)
• Feature issue: #592720 (closed)
• Rollout issue: #592721 (closed)