How to achieve zero downtime with 8 common database changes
Every database change can be split into a series of sufficiently small chunks. When applied one-by-one with coordinated application changes, the system continues running without interruption. This works because the application remains compatible with the database at every step.
The following table presents the eight most common database changes and how to apply this principle immediately. No tools or frameworks required! These changes are sorted from safe to unsafe, along with the required actions to perform without taking the application offline. Database changes are always scary. This principle makes them significantly less so.
| # | 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 to and reads from the column in the app before applying the DB change |
| 4 | Make nullable column not-null | not safe | change the 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 columns or tables as transactional changes. These steps are only necessary when such support is unavailable, as in SQLite.
For steps requiring data copying:
For large tables where copying data would block transactions for an unacceptably long time, split the copying into chunks.
Explanation
The core premise of safe, zero-downtime database changes is:
The application must remain compatible with the database schema at all times.
Compatibility prevents breaking (at least due to schema changes).
The phrase "at all times" might cause misunderstanding. It does not require the application to support all past database schema versions.
This clarifies the premise:
The current application version must be compatible with the current database schema and with one upcoming change to that schema and data.
Assumption
We assume you need to apply changes to a production database that holds data you must preserve and that the application accesses concurrently.
If you can afford data loss—for example, in a test database where you can recreate the database from another source or scripts—these steps are unnecessary. Without the need to maintain database-application compatibility, skip them.
The steps above take more time than simply wiping and recreating the database. Therefore, only use them when necessary.