Helper to include all necessary indexes on bigint conversion swapping
Why?
Recently on swapping indexes for design_user_mentions.note_id bigint converssion, a corresponding bigint index for design_user_mentions_on_design_id_and_note_id_unique_index was missed to create which resulted in an issue #428881 (closed).
To avoid such cases going forward, we can have helpers to fetch all indexes with the given integer column and automatically create and swap the bigint indexes as required.
Approach
-
initialize_conversion_of_integer_to_bigint creates necessary bigint columns and supporting triggers, we can also create the index on bigint at this point.
- Downside: The bigint indexes will sit idle till we remove them while swapping, which can contribute to LWLock contention issue.
- Write a new migration helper which will take table_name and int_column as inputs and create (and swap) required bigint indexes.
- Gotchas: Creates the indexes concurrently (similar to most of the previous swap migrations), on dealing with time taking indexes (eg: index_ci_pipelines_on_auto_canceled_by_id_bigint) we should not be using this.