Automatic temporary index creation and cleanup for background migrations
Summary
It's quite common that background data migrations require temporary indexes to perform SQL operations efficiently. Currently we handle it like that:
- Create tmp index and commit it to structure.sql
- Schedule background migration
- Cleanup tmp index on next major release.
Which means tmp indexes stay around until next major release adding overhead to write operations and increasing DB size. For example this MR adds 2 indexes with total size of 5GiB and those indexes will exist for around half a year (a year in worst case).
Desired approach
Instead of creating and committing tmp index to structure.sql we can link the index to particular background migration and ensure index is present while executing background migration. After last batch of migration is finished migration can drop linked temporary indexes.
That way indexes exist only while they are needed.
Edited by Pavel Shutsin