If you're asking these questions, it may mean that you are going down a path that you might not need to. I understand the desire for perfectly maintained and groomed DB schemas. But at the end of the day, you've got to take into account business requirements while solving the problems in software.
Does the business have an SLA on downtime? No? Great throw up a maintenance page, run a migration, and move on with your life.
Yes? Maybe think about how you can migrate data and deploy new features in 2 or more deploys/migrations.
Do you have slow traffic hours? Maybe it is acceptable to have downtime at 3am, when your usage is low to none.
The downtime plans have an advantage of not engineering a flawless plan, which takes time, and frees you to continue developing. But these are only viable if your application would suffer during any sort of outrage.
Lastly, the option I don't hear people advocating for, is taking on technical debt. Mind you, debt is bad and I would recommend seeking the above alternatives first. In a pinch this is a solution.
Tech debt the process of making an informed concession on architecture/design/schema I'm the interest of time, and all parties of the business agree that you will be given time later to do it the right way. It's called debt because almost always is more total work to do later then now, but is less work to short cut now than the right way.
So for you, you might just deal with this in your software: a column that isn't mapped to the right table, or make a new table, or whatever outrage free solution you come up with. Then later run a migration and code deploy. And things could have changed at this time to your benefit, like more coders, different SLAs, different patterns, different technology, or a rewrite even. There is no hard and fast rule on how you deploy, only that some solutions fit your particular case better.
Adam, thanks for taking the time.
We sell a product. We have hundreds of installations worldwide and support updates from 15 year old versions as well as 15 h old versions. So, tech. debt approach is not feasible because it does not solve the problem fundamentally.
"Updates without downtimes" would definitely add value we could sell or use as USPs. Or fulfill stricter SLAs and what not. But there seams no silver bullet to add this into our existing legacy code base with a centralized database.
Yikes, 15 year old software? That sounds rough. If the service can't be interrupted, I've seen the staged release work well:
Step 2 is the hardest because there's a lot of options of how you can handle this. I've personally had my software write to 2 different places, ran a migration to move the old data to the new place, and then updated the service again to only read/write from the new place.
Hopefully you're sourcing see good ideas from the communuty suggestions, these have all been good ideas if they solve your problem effectively.
I also can't stress enough the importance of DB backups, testing that your theory of deploys works at every step in a non production environment, and having your plan written down and reviewed by your peers. DB alters in prod are hard, and carry a lot of risk if something unexpected happens.
We’re a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.