Add index on p_ci_finished_build_ch_sync_events for mode-filtered sync queries

Summary

Add a composite partitioned index on p_ci_finished_build_ch_sync_events to support upcoming time-based mode filtering in the ClickHouse sync workers.

Problem

The backfill migration (BackfillCiFinishedBuildsToClickHouse) inserts ~180 days of historical build records into the p_ci_finished_build_ch_sync_events table. A follow-up MR will introduce mode-based worker splitting (recent vs backfill) to prioritize processing of newer records. That mode filtering adds a build_finished_at range condition to queries, which needs index support for efficient seeks.

Index definition

CREATE INDEX index_ci_finished_build_ch_sync_events_on_mode_filter
ON p_ci_finished_build_ch_sync_events
USING btree (((build_id % 100)), build_finished_at, build_id)
WHERE processed = false;

Column rationale

• (build_id % 100) — matches the in_operator_optimization array mapping used by the keyset iterator to distribute work across workers
• build_finished_at — enables efficient range seeks for both :recent (>= 7.days.ago) and :backfill (< 7.days.ago) modes
• build_id — provides the ordering column for keyset pagination
• WHERE processed = false — partial index matching the pending scope, keeps the index small

Migration type

Post-deployment migration using add_concurrent_partitioned_index which creates indexes on each partition concurrently (no write locks).

Related

• Follow-up MR for mode split logic: (to be linked)