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
- 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). - 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_indexcreates the per-partition indexes but doesn't attach them, so this call attaches them and is cheap. - In the same migration, drop
index_duo_wf_checkpoint_blobs_on_workflow_idon(workflow_id). The new index makes it redundant, becauseworkflow_idis the new index's leading column, and thefk_duo_wf_checkpoint_blobs_workflow_idcascade delete still gets an index scan. - Update the two comments in
ee/app/models/ai/duo_workflows/workflow.rbthat 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 🤖