Long-running index creation is a problem for pgmigrate and anyone else doing “on-app-startup” or “before-app-deploys” migrations.
Even at moderate scale (normal webapp stuff, not megaco size) building indexes can take a long time — especially for the tables where it’s most important to have indexes.
But if you’re doing long-running index building in your migrations step, you can’t deploy a new version of your app until the migration step finishes. (Big problem for lots of reasons.)
The way I’ve dealt with this in the past is:
- the database connection used to perform migrations has a low statement timeout of 10seconds.
- a long-running index creation statement gets its own migration file and is written as: “CREATE INDEX … IF NOT EXISTS”. This definition does not include the “CONCURRENTLY” directive. When migrations run on a local dev server or during tests, the table being indexed is small so this happens quickly.
- Manually, before merging the migration in and deploying so that it’s applied in production, you open a psql terminal to prod and run “CREATE INDEX … CONCURRENTLY”. This may take a long time; it can even fail and need to be retried after hours of waiting. Eventually, it’s complete.
- Merge your migration and deploy your app. The “CREATE INDEX … IF NOT EXISTS” migration runs and immediately succeeds because the index exists.
I’m curious what you think about this answer. If you have any suggestions for how pgmigrate should handle this better, I’d seriously appreciate it!
I think that’s the safest approach, but it’s inconvenient for the common case of an index that’ll be quick enough in practice.
The approach I’ve seen flyway take is to allow detecting / opting out of transactions on specific migrations
As long as you always apply migrations before deploying and abort the deploy if they time out or fail, then this approach is perfectly safe.
On the whole I think flyway does a decent job of making the easy things easy and the harder things possible - it just unfortunately comes with a bunch JVM baggage - so a Go based tool seems like a good alternative
Long-running index creation is a problem for pgmigrate and anyone else doing “on-app-startup” or “before-app-deploys” migrations.
Even at moderate scale (normal webapp stuff, not megaco size) building indexes can take a long time — especially for the tables where it’s most important to have indexes.
But if you’re doing long-running index building in your migrations step, you can’t deploy a new version of your app until the migration step finishes. (Big problem for lots of reasons.)
The way I’ve dealt with this in the past is:
- the database connection used to perform migrations has a low statement timeout of 10seconds.
- a long-running index creation statement gets its own migration file and is written as: “CREATE INDEX … IF NOT EXISTS”. This definition does not include the “CONCURRENTLY” directive. When migrations run on a local dev server or during tests, the table being indexed is small so this happens quickly.
- Manually, before merging the migration in and deploying so that it’s applied in production, you open a psql terminal to prod and run “CREATE INDEX … CONCURRENTLY”. This may take a long time; it can even fail and need to be retried after hours of waiting. Eventually, it’s complete.
- Merge your migration and deploy your app. The “CREATE INDEX … IF NOT EXISTS” migration runs and immediately succeeds because the index exists.
I’m curious what you think about this answer. If you have any suggestions for how pgmigrate should handle this better, I’d seriously appreciate it!