Database management for a zero-downtime deployment
Regardless of the deployment solution you choose (blue/green, rolling update…), the database management remains the same.
You need strict rules to manage database breaking changes and application code.
Here are some examples of breaking changes between two versions:
- renaming a column
- deleting a column
- changing a column type
- changing the database structure (splitting a table into two tables)
A best practice is to manage a database without ever having a breaking change.
One can sum up these techniques in two Great Principles:
- “Forward only”: Never rollback the database changes, but you can rollback your API or application.
- “Baby steps”: If a change is backward-incompatible, split it into multiple backward-compatible steps.
Flyway, Liquibase usage
In the use cases below, I consider that your application or API uses Liquibase or FlywayDB (it’s mandatory).
About replication:
If the API is replicated, apply changes carefully with these tools: apply the changes by the first replicas. The changes must be applied only once.
Use cases
In the following use cases, consider:
- V0: is the actual version of your stack in production (app, api and database)
- V+X (where X = 1, 2, 3…) are the next versions of the same stack you will deploy successively in production.
Backward compatible changes
Adding a table or view
Action | V0 | deploy V+1 |
---|---|---|
Adding a table | Database: – no table foo App: – no table foo usage | Database: – add table foo App: – use table foo |
V0 of your application has no usage of the table or the view, so you can rollback to V0 easily.
Adding a column
Action | V0 | deploy V+1 |
---|---|---|
Adding a column with no constraints | Database: – no column foo App: – no column foo usage | Database: – add column foo App: – use column foo |
Remove constraint
Action | V0 | deploy V+1 |
---|---|---|
Remove a constraint | Database: – column foo with constraint App: – functionality with constraint (in database) | Database: – remove constraint on foo App: – yeah! no constraint! |
For a rollback, verify or validate whether it’s acceptable that the constraint is not managed in V0.
Backward incompatible changes
Adding a column with constraint not NULL
Action | V0 | deploy V+1 | deploy V+2 |
---|---|---|---|
Adding a column with constraints | Database: – no column foo App: – no column foo usage | Database: – add column foo with default value not NULL App: – use column foomanage constraint not NULL programmatically | Database: – column fooadd constraint not NULL to foo App: – use column foo – remove “programmatically” constraint |
You can’t apply directly the constraint on your column at V+1, because if you rollback to V0, your V0 application will insert rows that violate your constraint.
And rollbacking between from V+2 to V+1 is not a problem because the app will not insert NULL values.
Renaming a table, a view or a column
Action | V0 | deploy V+1 | deploy V+2 | deploy V+3 |
---|---|---|---|---|
Renaming a column, sync with trigger (if your database supports it) | Database: – column foo App: – use column foo | Database: – column foo – add column bar and copy data from foo. – add trigger to sync foo and bar App: – use column bar | Database: – column bar – delete column foo – delete trigger App: – use column bar | |
Renaming a column, sync programmatically with app | Database: – column foo App: – use column foo | Database: – column foo – add column bar – copy data from foo to bar. App: – read column bar – insert/update/delete data in foo and bar. | Database: – column bar – column foo App: – use column bar – remove programmatically sync. | Database: – column bar – delete column foo App: – use column bar |
In V+1, to synchronize the column foo and bar, you have two solutions:
- A trigger synchronizes the columns bar and foo
- Or your application inserts data in foo AND bar columns (programmatically synchronization).
You will drop your column in V+2, and stop the sync between the two columns.
Changing the datatype of a column
It’s the same process as renaming a column.
Removing a table, a view, or a column
Action | V0 | deploy V+1 | deploy V+2 |
---|---|---|---|
Removing a column | Database: – column foo exists App: – use column foo | Database: – column foo exists App: – don’t use column foo | Database: – delete column foo App: – don’t use column foo |
Split a table
In this case, your table Person contains address data (bad design or legacy lifestyle):
But you want to split it in two nice tables with the following relationship:
Action | V0 | deploy V+1 | deploy V+2 | deploy V+3 | deploy V+4 |
---|---|---|---|---|---|
Splitting table Person | Database: • table Person App: • use table Person | Database: • table Person • create table Address App: • use table Person as main reference • Synchronize data from Person to Address: ![]() ![]() ![]() | Database: • table Person • table Address • Create initialization Job to complete Address for records that have not changed in V+1 App: • use table Address as main reference • Synchronize data from Address to Person: ![]() ![]() ![]() | Database: • table Person • table Address App: • use table Address • remove synchronization | Database: • table Address • remove unused column from table Person App: – use table Address |
Conclusion
You can change your table or column in an atomic way, hence you can rollback to the previous revision with no problem.
If a change is a breaking one, do it in baby steps.
When refactoring your database, always have in mind the two Great Principles:
- Forward Only
- Baby Steps
Links:
Blue/Green deployment
- https://spring.io/blog/2016/05/31/zero-downtime-deployment-with-a-database
- https://octopus.com/blog/databases-with-blue-green-deployments
- https://martinfowler.com/bliki/BlueGreenDeployment.html
- https://techblog.bozho.net/blue-green-deployment/