DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Because the DevOps movement has redefined engineering responsibilities, SREs now have to become stewards of observability strategy.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Related

  • Schema Change Management Tools: A Practical Overview
  • Your NoSQL Database Has an Implicit Schema
  • Useful System Table Queries in Relational Databases
  • Automatic Versioning in Mobile Apps

Trending

  • Apache Spark 4.0: Transforming Big Data Analytics to the Next Level
  • Caching 101: Theory, Algorithms, Tools, and Best Practices
  • How to Format Articles for DZone
  • Infrastructure as Code (IaC) Beyond the Basics
  1. DZone
  2. Data Engineering
  3. Databases
  4. Exploring a Paradigm Shift for Relational Database Schema Changes

Exploring a Paradigm Shift for Relational Database Schema Changes

With these outlined principles in place, developers will be confident knowing that their schema migrations will not put a substantial load on production servers.

By 
Shlomi Noach user avatar
Shlomi Noach
·
Aug. 08, 22 · Analysis
Likes (2)
Comment
Save
Tweet
Share
5.7K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database Relational database Schema

Opinions expressed by DZone contributors are their own.

Related

  • Schema Change Management Tools: A Practical Overview
  • Your NoSQL Database Has an Implicit Schema
  • Useful System Table Queries in Relational Databases
  • Automatic Versioning in Mobile Apps

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!