Relational Database Schemata

Programmatic control

Database changes are one of the driving factors behind “planned downtime,” especially schema changes to relational databases. With some thought and preparation, we can eliminate the need for dramatic, discontinuous, downtime-inducing changes. We probably have a migration framework in place already. If not, that’s definitely the place to start. Instead of running raw SQL scripts against an admin CLI, we should have programmatic control to roll our schema version forward.

Remember: It’s good for testing to roll it backward as well as forward, too.

Safe schema changes

While a migrations framework like Liquibase helps apply changes to the schema, it doesn’t automatically make those changes forward-and back-ward-compatible. That’s when we have to break up the schema changes into expansion and cleanup phases. Some schema changes are totally safe to apply before rolling out the code:

• Add a table.
• Add views.
• Add a nullable column to a table.
• Add aliases or synonyms.
• Add new stored procedures.
• Add triggers.
• Copy existing data into new tables or columns.

All of these involve adding things, so we refer to this as the expansion phase of schema changes. (We’ll look at cleanup a bit later). The main criterion is that nothing here will be used by the current application. This is the reason for caution with database triggers. As long as those triggers are unconditional and cannot throw an error, then it’s safe to add them.

Combining old and new schema

We don’t see triggers very often in modern application architecture. The main reason we bring them up is because they allow us to create “shims.” In carpentry, a shim is a thin piece of wood that fills a gap where two structures meet. In deployments, a shim is a bit of code that helps join the old and new versions of the application.

For instance, suppose we have decided to split a table. As shown in the figure that follows, in the preparation phase, we add the new table. Once the rollout begins, some instances will be reading and writing the new table. Others will still be using the old table. This means it’s possible for an instance to write data into the old table just before it’s shut down. Whatever we copied into the new table during preparation won’t include that new entity, so it gets lost.