Zero-Downtime SQL Database Schema Evolution for Continuous Deployment Michael de Jong 

Deployment Continuous 

Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production 

Continuous Integration Write code Push to master Run test suite Package deliverable Deploy into production 

Write code Push to master Run test suite Package deliverable Deploy into production Continuous Delivery 

Write code Push to master Run test suite Package deliverable Deploy into production Continuous Deployment 

SO WHAT’S THE Problem? 

SQL Databases 

Continuous Deployment Faster release cycles Quick bug-fixes A/B testing ACID transactions Rigid schema Constraints for consistency SQL Databases 

Continuous Deployment Continuous change VS. Change? No thank you… SQL Databases 

Deploying a new version of a web service into production is well understood. 

How to deal with a SQL database when deploying a new version of a web service into production… 

How to deal with a SQL database when deploying a new version of a web service into production… … not so much 

“I deploying database schema changes into production!” — said no software engineer ever 

Reason 1: DDL statements 

Nemesis: 

MySQL 5.5 MySQL 5.6 PostgreSQL 9.3 PostgreSQL 9.4 S1 Read-only Non-Blocking Blocking Blocking S2 Read-only Non-Blocking Non-Blocking Non-Blocking S3 Read-only Non-Blocking Non-Blocking Non-Blocking S4 Read-only Non-Blocking Non-Blocking Non-Blocking S5 Read-only Non-Blocking Non-Blocking Non-Blocking S6 Read-only Non-Blocking Non-Blocking Non-Blocking S7 N/A N/A Non-Blocking Non-Blocking S8 N/A N/A Non-Blocking Non-Blocking S9 N/A N/A Blocking Blocking S10 Read-only Read-only Non-Blocking Non-Blocking S11 Read-only Read-only Blocking Blocking S12 Non-Blocking Non-Blocking Non-Blocking Non-Blocking S13 Non-Blocking Non-Blocking Non-Blocking Non-Blocking S14 Read-only Read-only Blocking Blocking S15 Read-only Read-only Blocking Blocking S16 Read-only Non-Blocking Non-Blocking Non-Blocking S17 N/A N/A Non-Blocking Blocking S18 Blocking Non-Blocking Non-Blocking Non-Blocking S19 Non-Blocking Non-Blocking Non-Blocking Non-Blocking 

Reason 2: Mixed-State 

API 

API API NEW 

Reason 3: Referential Integrity 

QuantumDB’s Approach 

Imagine if you will… movies rentals customers 

changelog.addChangeSet(“Add referral column to customers table”, addColumn(“customers”, “referred_by”, integer()), addForeignKey(“customers”, “referred_by”) .named(“customer_referred_by_fk”) .onDelete(NO_ACTION) .referencing(“customers”, “id”)); Defining changes 

82fba53 - CURRENT STATE 2ef5c1a 80bfa11 - FINAL STATE ADD COLUMN ADD FOREIGN KEY Defining changes 

movies rentals customers Evolving the database schema 

movies rentals customers customers* Evolving the database schema 

movies rentals rentals* customers customers* Evolving the database schema 

movies rentals* customers* rentals customers Evolving the database schema 

movies rentals* customers* rentals customers Evolving the database schema 

movies rentals* customers* rentals customers Evolving the database schema 

movies customers* rentals customers rentals* Evolving the database schema 

Accessing the database movies rentals* customers* rentals customers 82fba53 

80bfa11 82fba53 movies rentals* customers* rentals customers Accessing the database 

Accessing the database Application Driver 

Application Wrapper Driver Accessing the database 

Connection connection = DriverManager.getConnection( “jdbc:postgresql://localhost:5432/database”, “username”, “password”); Connection connection = DriverManager.getConnection( “jdbc:quantumdb:postgresql://localhost:5432/database?version=80bfa11”, “username”, “password”); Accessing the database 

SELECT * FROM rentals WHERE customer_id = 2372 AND return_date < NOW() AND returned = false; SELECT * FROM rentals* WHERE customer_id = 2372 AND return_date < NOW() AND returned = false; Accessing the database 

movies customers* rentals customers rentals* Dropping a database schema 

movies rentals* customers* rentals customers Dropping a database schema 

movies rentals* customers* Dropping a database schema 

80bfa11 movies rentals* customers* Dropping a database schema 

Demo-Time! 

No content

No content

No content

No content

No content

No content

No content

No content

No content

No content

No content

No content

LET’S Evaluate 

#1 - DDL statements QuantumDB only applies DDL statements to tables which are not yet in use. Thus the client is never blocked by QuantumDB. 

#2 - Mixed-State Database clients can only access the tables in their chosen version of the database schema. 

#3 - Referential Integrity Foreign key constraints are always adhered to, and mirrored when needed. 

Too good to be true? 

Supported databases 

SUPPORTED ADD COLUMN ALTER COLUMN DROP COLUMN CREATE TABLE COPY TABLE RENAME TABLE DROP TABLE ADD FOREIGN KEY DROP FOREIGN KEY CREATE INDEX DROP INDEX NOT (YET) SUPPORTED DML STATEMENTS ADD CHECK CONSTRAINT DROP CHECK CONSTRAINT DECOMPOSE TABLE * JOIN TABLE * MERGE TABLE * PARTITION TABLE * * Update Rewriting and Integrity Constraint Maintenance in a Schema Evolution Support System: PRISM++ Supported operations 

Copying data is limited by disk I/O. We could actively throttle this based on some metric. Hardware limitations 

• QuantumDB, which supports complex schema changes on multiple tables. • Maintains referential integrity. • Can use up to two database schemas in parallel. Contributions 

• Do it in a non-destructive, non performance-degrading fashion. • Nemesis, which is able to benchmark various schema evolution scenarios. Contributions 

QuantumDB quantumdb.io - @java_devver