3

Although I believe my question is language and DB agnostic, I will provide the specifics technologies in this case study because it might help understanding my question (sorry for my English).

It's a Java-Spring application to be hosted by several Tomcats with one central postgresql DB whose schema is versioned by migration scripts. A tool like FlywayDB or LiquidBase is used to manage the versioning.

The database migration tool can be used in a few ways :

  1. Fully integrated in the (Spring) application : when the application is starting, the DB migration tool checks its versioning table and its local scripts directory. If new scripts are found, the tool runs them and thus update the database. It also check for hash equality, etc.
  2. The database migration tool is called upon at a specific step in the continuous integration process. It updates the DB and then the applications are deployed and started.

What's the best way and why ?

I though about a few arguments :

  • option 1 pro : 100% the schema and the code match.
  • option 1 pro : FlywayDB documentation says it's best

source : https://flywaydb.org/documentation/api/

Flyway brings the largest benefits when integrated within an application. By integrating Flyway you can ensure that the application and its database will always be compatible, with no manual intervention required. Flyway checks the version of the database and applies new migrations automatically before the rest of the application starts. This is important, because the database must first be migrated to a state the rest of the code can work with.

  • option 1 con : if something happens, the application has been updated to the last version but the database has not. This server is out of service. Unless a rollback system for the application deployment is built, with the ability to catch the db migration tool error. Does it even exist or have we to implement it ourselves ?
  • option 2 pro : easy to catch a db migration issue, to roll back and cancel the deployement. The production servers keep running as before, no time out.
  • option 2 con : if deployement fails on one server, old code will mismatch with new DB schema but the application still runs => data corruption might silently occur.

Maybe we could keep checking the DB schema / code version matching without applying the scripts from the application runtime and apply the DB migration scripts from the continuous integration process ?

Thank you for sharing your experience on this subject.

Edit : I checked & these tools used a lock on the database to manage multiple instances trying to update the schema.

  • Concerning option 1 con: The application startup should fail if the database migration fails. (this works with spring/hibernate/liquibase), so if that mechanism is in place you could strike this con. – traneHead Dec 18 '19 at 11:07
  • If the application does not start you have one less server running. – Poutrathor Dec 18 '19 at 11:10
  • One less, but not one not matching your db. So then you roll back and fix the migration issue – traneHead Dec 18 '19 at 11:13
  • 1
    Additionally: Suggested read martinfowler.com/articles/evodb.html – Thomas Junk Dec 20 '19 at 17:00
3

There's one really huge reason not to include migrations in your application that I neglected to mention: flyway needs high-level permissions that you should not be giving to an application. This violates an important security practice.

I really don't see an upside to having the application update the DB on startup. Let's consider the two main contexts of this: you have automated deployments or you have some sort of manual deployment. In the former, you can simply make the flyway update routine part of the deployment process preceding the application startup. In the second case, the manual process means there's a good possibility that something goes wrong like deploying the wrong version of the application and then you might have much bigger issues if a schema migration was bundled into that.

Database schema changes are (almost always) much more impactful, risky, and difficult to correct than application deployments. If you set up your application deployment right, when you deploy the wrong version of an application, it's simple to correct: you deploy the correct version. This is especially the case if you use containers for your solution. Changes to a database schema are not usually that simple. They are often accompanied by data transformations which may not always be reversible. The more risky a change is, the more deliberate you should be about it. Having schema changes occur as a side effect of something that is usually less risky is asking for trouble.

if deployement fails on one server, old code will mismatch with new DB schema but the application still runs => data corruption might silently occur.

I would recommend that your application check the schema on startup and verify that it matches the version the application expects. If the schema is not aligned with the application version, the application should fail to start.

If you keep the schema migration separate, it gives you much more freedom to manage the deployment process. If you have a clustered app with rolling deployments, you may need to stop everything to make a major schema change. Consider what happens if that schema change is part of a rolling deployment i.e. the old version is still up and running in some nodes. Having the application upgrade the schema might seem to simplify things but it actually complicates things, especially in the case you accidentally start the wrong version of the application that then attempts to 'fix' the database.

| improve this answer | |
  • I have no definitive scenario in my mind. I need advice about the whole issue before going for one way or another. It was crystal clear when we start coding but now... :D – Poutrathor Dec 18 '19 at 19:27
  • If you want zero downtime and want to safely use a database migration tool, with all the benefits they bring, you have to have a strategy based on that your db migrations should always be backwards compatible one version. – traneHead Dec 18 '19 at 19:29
  • @traneHead That's an interesting point. I'm not sure it matters whether you are using a database migration tool or a not, though. I don't think this is a going against the recommendation I am giving, is it? – JimmyJames Dec 18 '19 at 19:47
  • @Poutrathor You lose nothing by keeping it separate. The only reason to embed this in the application is for convenience. There's no reason doing so would be required. I really strongly recommend that you fail-fast on schema mismatch. If you run into that scenario, you either have the wrong schema version or the wrong application version. Doing anything other than stopping is basically leaving things to chance. – JimmyJames Dec 18 '19 at 19:53
  • @JimmyJames sticking with such strategy, you wouldn't need to keep the migration separate, which was what I interpreted being your recommendation (I might be reading it wrong though). Rather, if such strategy is maintainable, you could benefit from the migration tool doing the job at startup while still having zero downtime – traneHead Dec 18 '19 at 23:19
3

This question boils down to how to handle failures in db and application upgrades, and which one should be done first, in a situation where there are lots of application instances and one shared DB.

If it is critical to keep the downtimes as small as possible, I would try to split the deployment into the following steps:

  1. Set the applications into "read-only" mode for a short time to prevent data loss (you may have to implement such a feature).

  2. Upgrade the DB (schema and data) to an intermediate version, whery only changes to the DB are applied like adding columns and tables which are "backwards compatible". So the old application version can work with them at least in read-only-mode. Even an operation like "split a column into two" with some data migration is possible here, as long as the original column is not deleted right now. If this step fails, roll back the DB.

  3. Then, upgrade the applications. Any application which was updated successfully can be switched to read-write-mode again. If one application server upgrade fails, fix the issue (or take the server offline). Don't proceed until all running applications are upgraded.

  4. Upgrade the DB to a final version, applying any non-backwards compatible changes to the database, like deleting tables or columns which are not required any more by the new application version.

That way, you won't need a rollback system for the applications, and you don't have the risk of silent data corruption.

| improve this answer | |
  • From your answer, one should not use such DB migrations tools at all but rather have a feature-full deployment process, for maximal uptime. However, it seems to me that this way will mathematically lead to more human errors from the many more non-obvious steps, right ? What about trading uptime for reliability : how would the DB Migration Tool be used ? – Poutrathor Dec 18 '19 at 16:17
  • 1
    @Poutrathor: don't jump to conclusions. Why not use your favorite DB migrations tool for implementing exactly what I suggested? – Doc Brown Dec 18 '19 at 18:47
  • Oh, so you advice to use the database migration tool as a stand-alone utility ? These tools tend to run all the new scripts at once. Here we need to break it in 2 parts. If one wants to run scripts one by one, there is almost no benefit to use a FlywayDB rather than having each SQL script insert a record in a versioning table, right ? – Poutrathor Dec 18 '19 at 19:22
  • 1
    @Poutrathor: any of these tools can update your schema to a specific version, right? So if you need non-backwards compatible changes, make two new schema versions, lets say V9 and V9.1 Version V9 is the schema with the backwards-compatible changes (compared to its precedecessor V8), so update first to this. Then update the applications, and finally update the DB to V9.1, where the unneeded columns or tables are deleted. – Doc Brown Dec 18 '19 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.