Feature request: Database migrations without downtime

[glerchundi]

Is your feature request related to a problem? Please describe.

We would like make ORY Hydra upgrades without incurring in service downtimes.

Describe the solution you'd like

An expand-and-contract pattern to be implemented

Describe alternatives you've considered

Deal with downtimes.

[aeneasr]

Without more context and without technical details how to implement this, and given the very real and complex nature of SQL migrations, this is not something that can be considered by this project. Feel free to provide more details and concrete proposals despite closure of this as a nofix.

[glerchundi]

The expand and contract pattern is a way to handle with breaking changes to persistent data without downtime. Take a look to this document.

But basically it would require ORY Hydra to follow these steps:

1. Introduce new structure

   1. Create the new structure.
   2. Change any writing code to write to both places (old and the new structures). Under normal circumstances such duplication is bad, but it's only a temporary measure. Everything will be properly normalised when finished.

2. Migrate Data
   Once that code is successfully live everywhere, you can then run a migration on all the existing data.

3. Operate on new structure
   Once you're sure that both tables are perfectly in sync, and are staying that way, you can start to migrate all code that reads to use the new table.

4. Stop writing on old structure
   When you're sure that no code reads from the old table you can remove the old code that writes to it, leaving, of course, the code that writes to the new table.

5. Delete old structure
   Once that's live everywhere, you can delete the old structure.

Do you think this is something that you wouldn't consider?

[aeneasr]

That's an interesting concept! However, it looks like it would be very challenging to set up a proper test plan and make sure that everything runs smoothly with every release. I fear that we don't have the resources / incentives to support and maintain this. Sorry!

[glerchundi]

As we've talked, pinging @aeneasr & @lopezator for bringing this to the top of your inboxes.

[aeneasr]

I am closing this as we will probably not be able to support this. Zero-downtime migrations heavily depend on the database backend (type, engine, and version) as every DB has subtle difference that can range from full on table, to range, to row locks depending on the migration.

Working around these is just too challenging. For most systems, a 99.99% uptime should be possible (~5 minutes per month) when planned carefully.

For higher SLAs it might make sense to consume this from our cloud service instead. Although we do not have 99.99% availability SLOs as of yet.