How Changes to Database Schemas Slow Down Application Delivery

What Makes Database Changes Different?

Database schema changes are inherently more complex than application code changes. In the world of software development, the ability to rapidly deliver new features and updates is crucial. However, one often overlooked bottleneck in this process is the management of database schema changes. While application code can be swiftly modified and deployed, Rapid changes to stateful applications are more complex because database schema changes introduce additional complexity that slows down the entire delivery pipeline.

Data Integrity and Consistency

Unlike application code, which can be versioned and rolled back relatively easily, database schema changes must maintain data integrity and consistency. Any alteration to the schema, such as adding or modifying tables, columns, or indexes, can have far-reaching implications on the existing data. If a rollback needs to occur, it is imperative that data is not lost.

An example schema change

Imagine a database change that splits one column into two based on a delimiter in the column data. Before the migration runs, data is present. During the migration, in addition to creating the two new columns, the data must be copied from the old column to the two new columns. At any point that both sets of columns exist, the question remains: which is the source of truth?

This situation can be safely handled, but what initially looks like a simple change may require multiple carefully considered changes to both the application and the database to be done safely. 

1. You start by setting up the database to have both sets of columns AND to maintain consistency between them:

a. Start by introducing the new columns

b. Now, set up a trigger to copy any change to the old column’s data into the new columns and vice versa. To avoid introducing a trigger cycle, these triggers must check whether the data matches before each update and only perform the update if there is a mismatch.

c. Copy all the data from the old to the new columns.

2. Now, you can deploy the new application version that uses the new columns. 

‍

‍

Because the database is internally syncing changes bidirectionally via triggers, even if the application runs multiple concurrent pods, and some are on the old version while others are on the new, all pods will always see the same data. Similarly, if an application bug requires a rollback, doing so will not cause data loss. After all environments have been updated, a new data migration can occur to delete the old columns and remove the triggers.

Performance Issues

Poorly optimized schema changes can degrade database performance, affecting overall application performance. Identifying and resolving these performance issues can further delay the delivery process. Database performance problems are often dependent on both the data and the database sizing, making detection of performance issues before they reach production more difficult.

As shown above, ensuring uptime and consistent data during a migration often requires using database triggers to synchronize data if it changes. Since the database is doing twice as many writes, this will also slow down the database.

Additionally, database migrations frequently require locking part or all of the database in order to run. If a lock is held for a significant amount of time, this can result in a database migration, causing a missed SLA when the database cannot process a request because of an in-progress migration. Because of this, you may need to copy rows in batches to minimize locking on large databases.

What Slows Down Database Changes?

Database changes are often slow because of manual bottlenecks in the change process.

Approval Bottlenecks

Database changes often require close coordination between development and database administration (DBA) teams. Developers may write the initial SQL scripts, but DBAs must review and approve these changes to ensure they do not negatively impact database performance, violate data governance policies, or cause data inconsistencies.

The need for DBA approval can create bottlenecks, especially if the DBA team is small or overburdened. This approval process can delay the deployment of new features that depend on the schema changes.

Manual Processes

Many organizations rely on manual processes for deploying database changes. This can involve creating and running SQL scripts manually, which is not only time-consuming but also prone to human error. While Continuous Integration/Continuous Deployment (CI/CD) pipelines have accelerated application delivery, database changes often lag behind in terms of automation. Integrating database changes into CI/CD pipelines has not been as widely adopted as delivery, for example, the delivery of application docker images.

Manual deployment processes increase the risk of errors, such as running scripts in the wrong order or missing critical steps. These errors can lead to deployment failures, requiring time-consuming troubleshooting and rollback procedures.

Without proper tools to track and manage database changes, it can be challenging to understand the state of the database across different environments. This lack of visibility can lead to inconsistencies and unexpected issues, where two environments behave differently because a database change was accidentally not applied to one of them. 

Strategies for Mitigating the Slowdown

To address these challenges and accelerate application delivery, organizations can adopt several strategies:

Automate Database Deployments

Integrate database schema changes into your CI/CD pipeline using tools like Liquibase or Flyway. These tools can help automate the deployment process, reducing the risk of errors and speeding up the approval process. Integrating them into your deployment pipeline can also ensure consistency between the rollout and rollback of your database and application change. 

Many CI/CD tools have features that enforce configuration policies during deployment. Use these tools to replace manual DBA team approvals while ensuring that database changes adhere to governance and compliance requirements. 

Implement Version Control for Database Changes

Like application code, database schema changes should be versioned and stored in a version control system. This ensures that all changes are tracked. It also makes it easy to inspect the code, and the comment history can help you understand the intent of a database change alongside who made it. 

Adopt a DevOps Approach

Encourage collaboration between development and DBA teams by adopting a DevOps approach. This can help streamline the approval process and ensure that database changes are reviewed and tested early in the development cycle.

Final Thoughts

Database schema changes are a critical but often challenging aspect of application delivery. By understanding the complexities involved and adopting strategies to automate and streamline the process, organizations can mitigate the slowdown caused by these changes. Embracing a DevOps culture, leveraging automation tools, and ensuring proper version control and monitoring can help accelerate application delivery and maintain the integrity and performance of the database

‍

Contact harness to discuss how you can apply database migrations as part of your CI/CD Pipeline.