Long-running data migration best practices

danielrearden

We are using Flyway to manage migrations for our Postgres database. We leverage migrations for both modifying the database schema, as well as occasionally migrating data. When doing data migrations with more complex queries and larger numbers of records, these migrations can take a long time to run. Since each migration runs inside a transaction, this results in us hitting locks and ends up affecting our production environment.

I'm aware that we can turn off the use of transactions for individual migration files, in which case we could break up our long-running queries into multiple, shorter ones to avoid excessive locks. However, we then also lose the ability to roll back the changes if anything does go wrong during the migration.

What is the best practice for dealing with long-running data migrations like these?

DanC

Hi @danielrearden

I don't think we have any articles for best practices around this particular topic, however, if you're a Flyway Teams user, you could leverage 
the use of Batch: https://documentation.red-gate.com/fd/batch-184127483.html