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:

  1. « Forward only »: Never rollback the database changes, but you can rollback your API or application.
  2. “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

ActionV0deploy V+1
Adding a tableDatabase:
– 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

ActionV0deploy V+1
Adding a column with no constraintsDatabase:
– no column foo

App:
– no column foo usage
Database:
– add column foo

App:
– use column foo

Remove constraint

ActionV0deploy V+1
Remove a constraintDatabase:
– 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

ActionV0deploy V+1deploy V+2
Adding a column with constraintsDatabase:
– 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

ActionV0deploy V+1deploy V+2deploy 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 appDatabase:
– 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

ActionV0deploy V+1deploy V+2
Removing a columnDatabase:
– 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:

ActionV0deploy V+1deploy V+2deploy V+3deploy V+4
Splitting table PersonDatabase:
• 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

Links:

Blue/Green deployment

Rolling update deployment

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

%d blogueurs aiment cette page :