Not in the context of micro-services, but we ran our production DB for years in a “both N and N+1 work” by following a few simple rules which turn out to be not that restrictive in practice.
Short version: have DB1 hold the transactional data (data generated while running the system). Have DB2a have the release-bound data (data about and connected to the code itself-settings, prices, whatever).
Have DB2a have views onto DB1 tables. Version a code only “knows about” DB2a but any transactional CRUD ops hit the tables on DB1.
Now version b of the code just needs to ship/create a DB2b and both a and b can run in parallel.
If you need to change the shape of DB1 tables, those changes need to be backward compatible (can only add nullable columns, no use of "select *", etc).
There’s a few details about how to make it fully practical, but that’s the gist and we ran than for about 12 years on a moderately heavily trafficked e-commerce site.
Database versioning and backwards compatibility has been something of an upcoming problem where I'm at as we've been getting better at CI/CD. Do you have any recommended resources for different approaches to it, or even some keywords that'll help when searching for such resources?
Whoa I love this idea. I usually refer to it as "transactional" (always growing, represents the daily activity, read and write heavy) vs "lookup" data (almost exclusively read, not changed often). But still store them in the same database.
What ends up happening is we end up separating the two more with "cache this one, not that one" rather than two different databases.
I will explore this idea on my next greenfield project, whenever that happens.
I looked briefly (and I could have sworn I posted our "nine rules" on HN years ago, but I couldn't find it in a quick search).
I'll look again later tonight more thoroughly to see if I've posted the mechanisms and restrictions publicly anywhere before. If I haven't, I'll try to dig it out of our old dev doc system and post them here, but I can't make any promises as the docs I recall are now over a decade old, so I'm not fully sure they exist any more. :)
The internal docs for this are not on any of our documentation systems that we've moved to zero-trust (as they're 12 years old and unchanged for 5+ years). I will probably be able to retrieve them when we're back in the offices; shoot me an email (in my profile) and I'll find a way to get something over to you with some significant delay.
Not the OP, but the way I handle this to to ensure that all migrations are backwards compatible - the current and new versions of the app/API/service must be able to run with the old and new database.
This requires a little discipline, but if you follow a few simple rules it's not really that arduous:
- when adding a new column, it must have a default value set, or be nullable
- don't drop any columns
- don't rename any columns
Now, for those last 2, what I really mean is "don't do it in a single release" - if you want to make destructive changes, do it over the course of 2 releases.
- release 1: remove dependencies on the column from the app/API/service
- release 2: performs the database migration with destructive changes
It probably sounds more difficult than it actually is :) In reality, I don't make destructive changes that often though.
We always do it by not pushing breaking changes to the database. It’s extremely freeing. It does require some discipline to go back and cleanup things later, but not worrying about database “versions” is the way to go in my opinion.
Not gp but here's a possible answer: I usually require db migrations to have a "down" script as well but "down" is never applied automatically. I only auto-apply "up", and when a rollback is needed (which has been very infrequent in my case) I manually apply the "down" scripts using Flyway cli commands or by hand.
Same here. A forward only approach works best for us too. if you need to clean up a mess, it is a new migration script. It's too complex to try an work backwards. What if multiple scripts were ran? Then you have to roll back say script number 2 out of 5 and there were destructive operations. It becomes really hairy really quickly. So forward-only is the easiest to reason about.
Please do make sure that you have snapshots for restoring if you really mess up badly. I know its not always feasible to do snapshots before every deploy, but having a daily snapshot can bring you a lot of comfort.
If you built your own migration tool (highly encourage it, its not that hard to build a forward-only migration tool), then you can trigger selective snapshots/table dumps for only the tables that gets changed, and only for specific operations (updating schema, dropping columns, dropping table) before your migration scripts touches the db - that way you have a path to restore. You don't always need a full DB dump (say you have 500+ tables but only changing 2, 1 of which is destructive, thus the backup is tiny and quick). It also helps if different data sets live in isolation to help manage this kind of admin.
Short version: have DB1 hold the transactional data (data generated while running the system). Have DB2a have the release-bound data (data about and connected to the code itself-settings, prices, whatever).
Have DB2a have views onto DB1 tables. Version a code only “knows about” DB2a but any transactional CRUD ops hit the tables on DB1.
Now version b of the code just needs to ship/create a DB2b and both a and b can run in parallel.
If you need to change the shape of DB1 tables, those changes need to be backward compatible (can only add nullable columns, no use of "select *", etc).
There’s a few details about how to make it fully practical, but that’s the gist and we ran than for about 12 years on a moderately heavily trafficked e-commerce site.