idx_duo_wf_checkpoint_blobs_on_workflow_channel_id synchronous database index addition

Summary

This issue is to add a migration to create the idx_duo_wf_checkpoint_blobs_on_workflow_channel_id database index synchronously after it has been created on GitLab.com, and to drop the index it replaces.

The asynchronous index was introduced in !251705 (merged).

Steps

  1. Confirm the async index exists and is valid on every partition of p_duo_workflows_checkpoint_blobs, using Database Lab (\d idx_duo_wf_checkpoint_blobs_on_workflow_channel_id).
  2. Add a post-deployment migration that calls add_concurrent_partitioned_index :p_duo_workflows_checkpoint_blobs, %i[workflow_id channel id], name: 'idx_duo_wf_checkpoint_blobs_on_workflow_channel_id'. prepare_partitioned_async_index creates the per-partition indexes but doesn't attach them, so this call attaches them and is cheap.
  3. In the same migration, drop index_duo_wf_checkpoint_blobs_on_workflow_id on (workflow_id). The new index makes it redundant, because workflow_id is the new index's leading column, and the fk_duo_wf_checkpoint_blobs_workflow_id cascade delete still gets an index scan.
  4. Update the two comments in ee/app/models/ai/duo_workflows/workflow.rb that name the index serving #history_blobs_for.

Why

Measured on Database Lab, the new index takes #latest_channel_message from 205 buffers and a top-N sort to 8 buffers and no sort. Details and query plans are in !251705 (merged) and #621934 (closed).

Edited by 🤖 GitLab Bot 🤖