2

I have a SQLAlchemy/Flask application that is being accessed publicly by millions of people.

Every 2 weeks, I release a new version of this application and I run database migrations using python manage.py db upgrade. This runs any new migration files that were created since the last migration file was run.

The problem with this approach is that since my database tables are so large, simple migration operations like adding a column can take 15-20 minutes each. So stopping the application, running the migrations, updating the application code then restarting the application can cause a lengthy downtime. I'm trying to minimize this downtime.

My strategy to minimize the downtime is to run as many migration files (or as many fractional portions of migration files) before the deploy, without stopping the service. Clearly there are some operations that can be done this way.

For example op.create_table() and op.add_column() can safely be run before updating the code without interfering with the operation of the application. op.delete_column cannot.

Is there any automated way to separate out the migration commands that can be run without affecting the application's operation vs those that do affect the application's operation? Currently I have to manually go through these migrations by hand and perform brain surgery on them. It's painful. But I can't be the only one to ever experience this problem. So what's the solution that other people use?

Saqib Ali
  • 11,931
  • 41
  • 133
  • 272

1 Answers1

0

Use two application servers and two databases. First is the stable current version, second is for newly deploying version. When newly deploying version updated (scheme migrations and data migrations applied), route website traffic to this newly deploying version. (and reuse old database and app instance / or just kill it and recreate new one).

Vladimir Ignatev
  • 2,054
  • 1
  • 20
  • 34
  • That doesn't work because the changes that occur in the first DB between the copy and the re-routing will be lost when we switch to the second DB. – Saqib Ali Feb 16 '17 at 23:28