Database management for a zero-downtime deployment

3 février 2021 Laurent Weyand

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: – Insert in Person: insert data in Address – Update in Person: Insert (if no data) or Update (if data exists) Address – Delete in Person: If data in Address, delete in Address first	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: – Insert in Address: insert data in Person – Update in Address: Update in Person – Delete in Address: If data in Person, delete in Person first	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

Auteur/Autrice

Laurent Weyand

Voir toutes les publications

Blog Zenika

Database management for a zero-downtime deployment

Flyway, Liquibase usage