Exploring a Paradigm Shift for Relational Database Schema Changes

The relational database model was first proposed by English computer scientist Edgar Frank Codd in 1070 while working at IBM and has been commercially deployed for over forty years. It’s a rare accomplishment in software development that continues to evolve today. Relational databases generally serve as backends for the smallest to the largest apps and products in the world today. While relational databases have optimized for speed, concurrency, latency, and overall performance, they have not adapted to manage metadata changes at scale. Specifically, many organizations struggle to keep development velocity, agility, and confidence when deploying schema changes.

History

In the past, developers would plan a schema change months in advance and collaborate with database administrators to approve and coordinate the transition to the new model. To apply the changes, systems would be taken down for maintenance for hours or days. Given the complexity and time it took, teams would only deliver a handful of changes per year.

Current Landscape

Today, those maintenance windows are simply unacceptable, as users expect services to be continually available with zero downtime. Additionally, today’s developers are used to accelerated deployment flows and want to deploy schema changes continuously, sometimes multiple times per day.

But relational databases have not stepped up to meet developers’ needs. Schema changes pose an operational barrier to continuous deployment and remain alien to developers’ workflows. They evolve patterns to try and minimize schema migrations or avoid them altogether by modifying their code in suboptimal ways. As a result, schema deployments for large tables frequently remain a manual endeavor and are considered risky operations.

I believe relational databases can and should meet modern development practices for schema deployments, thus allowing for more automation, control, and velocity and, as a result, instilling confidence in the process.

Suggested Paradigm

I believe the following core tenets to be essential to schema migrations.

Non-blocking

Some relational databases, and for some types of schema migrations, place a write lock on the migrated table, effectively rendering it inaccessible to the app. In turn, this commonly manifests as an outage scenario. An ALTER TABLE migration for large tables can be measured in hours or even days. These blocking migrations are unacceptable to modern development flows and apps, and databases must offer non-blocking migrations that allow full access to the migrated table throughout the operation. 

Lightweight

Schema changes should be able to yield to the app’s needs. Even when available, non-blocking schema changes are typically aggressive in resource consumption and will attempt to utilize as much disk IO operations, memory, and CPU to run to completion. This competes with the resources needed by the apps and often leads to degraded app performance.

Asynchronous

Databases should be able to receive a schema change request and move to run it asynchronously. Atomic or transactional migrations are appreciated, but they imply a connection to be held active for the migration's duration, measured by hours or days. Deployment tools or scripts should not be required to hold on to those connections for long periods. The behavior upon connection loss is typically not what the developer wants. 

Scheduled

Migrations may conflict with each other due to running on the same tables or simply because of the excessive resource consumption incurred. Databases should provide a mechanism for scheduling migrations. The database should determine which migrations are safe to run concurrently and which are not.

Interruptible

Even if lightweight, a migration still impacts disk space and disk I/O operations. It should be possible to interrupt a running migration at no immediate cost. A rollback or flushing of pages are examples of undesired expenses at a time when resources are needed the most. 

Trackable

The database should be able to provide an estimate of a long migration’s progress or ETA.

Failure Agnostic

A database should be able to resume a migration interrupted due to database failure. For example, it should be possible for an operator to reboot the database server without compromising a days-long migration. Operators should not postpone maintenance work due to developer’s deployments, and developers should not withhold deployments.

If a database offers a multi-node design, then migrations should be agnostic to cross-node failovers and should not be bound to the specific node where they started.

Revertible

Schema migrations should be treated as first-class deployments. As such, the database system should be able to undeploy a migration, thus restoring the pre-migration schema. Developers should be confident that if a schema deployment goes wrong, they can revert it and return to a known good state. 

Redeployable

Much like code deployments, schema deployments should be idempotent. The developer or the deployment system should be able to submit the same migration request twice (or more) in a row, and the database should resolve the excessive requests to ensure the migration runs once, as the developer would expect.

Databases should potentially support declarative schema deployments, where a developer submits the desired state rather than an imperative command. Declarative schema deployments are idempotent by nature.

Resulting Flow

With these outlined principles in place, developers will be confident knowing that their schema migrations will not put a substantial load on production servers. It will also assure them that their deployment tools will not have to block the database for hours while running their changes. They will rest easy knowing the database will seamlessly schedule their migrations while other deployments are in place, and they can track the progress of the migrations at any time and interrupt them if the need arises.

Developers will be free from operational considerations. They will not need to be concerned about planned maintenance or unplanned failovers.

They will feel confident in their deployments, knowing they can redeploy their changes again and again or revert them all together and go back to the last known state in case of unforeseen issues.

These all suggest an efficient development flow that will give developers ownership of their schema changes and the confidence to deploy with velocity reliably. Ultimately, it will empower developers to focus on successfully delivering superior products and services to their customers.