How to achieve zero downtime with 8 common database changes
Every change to a database can be split up into a series of so small chunks then when applied one-by-one together with coordinated changes to the application everything keeps running if nothing has happened. It works because the application always stays compatible with the database.
In the next table I show you the most common 8 changes to databases and how you can immediately apply this principle. No tools or frameworks required! These changes are sorted from safe to not safe and the required actions you need to take to make any of them without ever taking your application offline. Database changes are always scary. With this principle a lot less though!
| # | Desired DB change | Safety | Action to make change safe |
|---|---|---|---|
| 1 | Make not-nullable column nullable (optional) | always safe | - |
| 2 | Add nullable (optional) column(s) to a table | always safe | - |
| 3 | Remove nullable (optional) columns from a table | not safe | remove all writes from and reads to the column from the app before applying the DB change |
| 4 | Make nullable column not-null | not safe | change app to always write to this column before applying the DB change |
| 5 | Add not-null (required) column to a table | not safe | do #2, then do #4 |
| 6 | Remove not-null (required) column from a table | not safe | do #1, then do #3 |
| 7* | Rename column | not safe | do #2, change app to write to both columns, copy all existing data from old to new column, change app to only read from new column, then do #1, do #3 |
| 8* | Rename table | not safe | create new table, change app to write to both tables, copy all existing data from old to new table, change app to only read from new table, then drop table |
*RDBMS often support renaming of columns or tables as a transactional change. Thus this steps are only needed if it isn't the case like for example in SQLite.
A hint for the steps where copying of data is required:
For tables that are very big and copying the data would block transactions for an unacceptable long time then we need to split up the copying into chunks.
Explanation
The core premise to safe, zero-downtime database changes is:
The application must stay compatible with the database schema at all times.
Because if it's compatible then it doesn't break (at least not because of schema changes)!
The part "at all times" of the above statement could still be a source of misunderstanding. It does not mean that your application needs to support all of the - possibly - many database schema versions that you had somewhen in the past.
Therefore we can formulate the first premise more precisely:
The current version of the running application must be compatible with the database that it currently talks to but also compatible with one upcoming change to its schema and data.
Assumption
We assume that you need to apply changes to a production database where the database holds data that you need to preserve and while the application accesses it.
If you can afford to lose the data, for example with a test database, where you are able to recreate the database from another datasource or scripts then you don't need to follow these steps. It's because you don't need to maintain the compatibility between the database and application.
All the presented steps above take you more time than just wiping the database and recreating it. Therefore don't bother doing them if not necessary.