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.
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. :)
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.
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.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.