Finalize pending detach partitions more often overnight

Leaminn Marequested to merge
ci-finalize-pending-detach-partitions-overnight into master

What does this MR do and why?

Context

On GitLab.com, Database::FinalizePendingDetachPartitionsWorker keeps failing to finalize ci_build_trace_metadata_102. Each run gets about 35 s of lock attempts at :17 past 00:00–03:00, and what blocks it lasts longer than that. On weekends, automatic reindexing starts at :12 and holds SHARE UPDATE EXCLUSIVE on p_ci_builds and p_ci_job_artifacts partitions for minutes, and on 2026-10-17 for about six hours. On weekday nights, transactions open for several minutes are common.

Reindex actions on the ci database, 2026-10-17 and 2026-10-18

From a Database Lab ci clone:

SELECT index_identifier, action_start, action_end, state
FROM postgres_reindex_actions
WHERE action_start >= '2026-10-16 20:00' AND action_start < '2026-10-18 04:00'
ORDER BY action_start;
index_identifier action_start action_end state
ci_builds_116_updated_at_idx 10-17 00:15:41 10-17 00:21:11 1
unique_ci_builds_token_encrypted_and_partition_id 10-17 00:21:12 0
idx_project_type_ci_runners_on_contacted_at_and_id_desc 10-18 01:13:31 10-18 01:13:34 1
idx_project_type_ci_runners_on_contacted_at_desc_and_id_desc 10-18 01:13:34 10-18 01:13:38 1
ci_builds_116_runner_id_id_idx 10-18 02:13:56 10-18 02:22:26 1
ci_job_artifacts_116_project_id_id_idx1 10-18 02:22:26 10-18 02:35:54 1
ci_builds_116_pkey 10-18 03:13:17 10-18 03:17:09 1
ci_job_artifacts_116_project_id_created_at_id_idx 10-18 03:17:10 10-18 03:23:02 1

This MR

  • Runs the finalizer at :05 and :45 from 23:05 to 04:45, which is 12 runs a night instead of 4, centered on the 02:00 traffic low. Neither minute overlaps the weekend reindex start, and ten of the runs come before the 03:20 dropper.
  • Keeps every run inside the low-traffic window, because each failed attempt can still stall builds and artifacts writes for up to about 2 s.

This gives the finalizer more chances to land between blockers without adding attempts during daytime traffic.

Feature Flag: ci_finalize_pending_detach_partitions_daily

References

How to set up and validate locally

  1. Print the finalizer's fire times: 
    bundle exec ruby -ryaml -rfugit -e 'c = Fugit.parse_cron(YAML.load_file("config/schedule.yml")["postgres_dynamic_partitions_detach_finalizer"]["cron"]); t = Time.utc(2026, 10, 24, 22); puts 12.times.map { t = c.next_time(t).to_t; t.strftime("%H:%M") }.join(" ")'
    Expect 23:05 23:45 00:05 00:45 01:05 01:45 02:05 02:45 03:05 03:45 04:05 04:45.

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.

Merge request reports