Index p_ci_finished_pipeline_ch_sync_events on pipeline_id

What does this MR do and why?

This MR adds a new index on pipeline_id to the p_ci_finished_pipeline_ch_sync_events partitioned table to improve query performance for the ClickHouse pipeline sync worker.

Problem: The Ci::ClickHouse::FinishedPipelinesSyncWorker currently experiences timeout issues (ActiveRecord::QueryCanceled) due to complex queries generated by Gitlab::Pagination::Keyset::Iterator. The existing index on (pipeline_id % 100::bigint) is not optimal for single-worker scenarios.

Solution: This migration adds a new concurrent partitioned index on pipeline_id with a WHERE processed = FALSE condition. This index will enable simpler, more efficient queries when a single worker is processing unprocessed pipeline sync events, providing better performance and reducing timeout occurrences.

The index is added as a post-deployment migration to ensure zero downtime during deployment.

Changelog: changed

References

Relates to #580318 (closed)

Screenshots or screen recordings

N/A - Database migration only

How to set up and validate locally

1. Run the migration: bundle exec rails db:migrate RAILS_ENV=test
2. Verify the index was created:

   \d p_ci_finished_pipeline_ch_sync_events

3. Confirm the index exists with the correct definition:

   SELECT indexname, indexdef 
   FROM pg_indexes 
   WHERE tablename LIKE 'p_ci_finished_pipeline_ch_sync_events%' 
   AND indexname = 'index_p_ci_finished_pipeline_ch_sync_events_on_pipeline_id';

Database query plans

See !212289 (merged)

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.