I'd be interested to here what people do for production DB migrations in a containerised environment. i.e, you have an AWS codepipeline which does a build -> blue/green deployment, where there are migrations that need to be run. As I see it, you could • trigger the migration from the container itself when the application starts : however that can create a race condition if you have multiple containers • As above, but set a database flag that a migration has started, so the other containers don't try to run it • As above but scale back the fargate service to 1 container before the deployment and then scale back up after the migration has completed • trigger the migration earlier in the build process, where you might do it after the build process but before deployment: however that means you need to have a database connection in your build environment, and you're going to have containers running in production against a database that has been migrated but the application hasn't been deployed yet • Add a new stage before deployment to solely spin up a container, run the DB migrations and exit. There's still a window where the application is running against a migrated database but the new application hasn't been deployed yet. • Do a blue/green deployment on the database as well, so you can run the migrations on the new database before the application is deployed - I have no idea how you'd do this with RDS / and what would happen to the existing in use data. • Just take stuff offline and deal with the downtime. OR, is the answer simply "ensure your migration is always backwards compatible with the current application code so it doesn't matter when the migration happens". Is there an avenue I'm missing?

We "ensure [the] migration is always backwards compatible with the current application code" and then "trigger the migration earlier in the build process" effectively. We have an automated deployment for database migrations and we run it to completion before we deploy any application upgrades that depend on the DB changes.

It's always "it depends", but if you can, I think the best practice is to do the DB too. It's more complicated with blue/green than just dev/qa/prod, but that's the price you pay for choosing blue/green I guess.

For us, the complexity of replication and the sheer volume of data makes blue/green pretty much impossible for our DB -- we have plenty of tables with over 100M rows and some have over 250M rows.

RDS is fantastic for this though! You can clone a massive DB super fast now, and there's lots of tools for moving data around. If you're doing rolling stuff you probably have a datalake or whatnot, so are dealing with consolidating data slurped in from all over

Thanks both - all very interesting. I think the takeaway thus far is to "just write the migrations better" (i.e more defensively) and make sure the codebase reflects that, and also definitely split the migration layer as it's own stage in the deployment process. We're already doing tracked migrations in the DB etc, so it's just a matter of getting the order right, and being more strict when a migration comes along about possible side effects

More often than not schema syncing is tied to application initialization (with locking and whatnot to prevent race conditions) for good reason, but so much depends on your deployment pipeline, and what all is using what, and who does QA on what. We had great luck with [insert the name of the sql migration tool that won a contest for being most useful cfml lib here] which basically does what Sean said above: a table to know what migrations have been run, and a directory full of numbered migrations to run if needed (and locking to prevent race conditions) This is basically how every tool I've worked with does it, be it Liquibase (the 800lb gorilla) or that CFC I'm drawing a blank on. The "caching" problem is one of "the 2 hard things in programming" because dependency management is difficult. I don't know how you're doing your tests, but testing the migration should be one of them (on a copy of the prod DB) prior to deployment. This is super easy to do on AWS RDS, even with hundreds of gigs of data (takes like 10 minutes to clone the prod db and run the tests even with just oodles of records) and thousands of migration files (at one place over half our dev time was spent just doing data migration stuff but everything was clockwork). Do you also have a dev/QA pipeline, or is it just always rolling deployments?

We have a separate dev -> beta -> prod environments/pipelines, so all the migrations would go through at least two iterations before hitting production. However the dev / beta environments just have single application containers. All the pipelines are triggered by pushing to the relevant branches. I think we just either need to a) put additional locking in when a migration is running on a container or b) just move the migration to a step before deployment, which would ensure only one process is acting on it at any given time.

Ah, right on. You could probably also do something with load-balancing, depending on how you're managing that, to control what instance is getting hit when.