Continuous Deployment and Schema Evolution in SQL Databases

Michael de Jong @java_devver

Arie van Deursen @avandeursen

But besides being a student… … I’m also a practitioner

My long-time interest: How do you deploy a new version of a web service without downtime?

SQL Databases!

Relevance Continuous Deployment rapidly delivers business value. It is important to remove any impediments to its adoption.

Why is it so difficult?

Reason 1: DDL statements Statements which change the structure of the database (tables, columns, indices, foreign keys, etc)

They prevent other queries from being executed!

Reason 2: Mixed-State During deploying, two different versions might be accessing the same database

Reason 3: Referential Integrity None of the surveyed tools and approaches fully support foreign keys.

Our proposal

changelog.addChangeSet(“Add referral column to customers table”, addColumn(“customers”, “referred_by”, int()), addForeignKey(“customers”, “referred_by”) .referencing(“customers”, “id”)); Defining changes

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

Evolving the database schema movies rentals customers

Evolving the database schema movies rentals customers customers*

movies rentals customers customers* rentals* Evolving the database schema

movies rentals customers customers* rentals* Evolving the database schema

movies rentals customers customers* rentals* Evolving the database schema

movies rentals customers customers* rentals* Evolving the database schema

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

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

Application Driver Accessing the database

Application Wrapper Driver Accessing the database

DriverManager.getConnection( “jdbc:postgresql://localhost:5432/database”, “username”, “password”); 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

Dropping a schema movies rentals customers customers* rentals*

movies rentals customers customers* rentals* Dropping a schema

movies customers* rentals* Dropping a schema

movies customers* rentals* Schema: 80bfa11 Dropping a schema

Early evaluation

#1 Blocking DDL statements Avoids using blocking DDL statements, since all affected tables are recreated from scratch.

#2 Mixed-State Tables from other database schemas are hidden from the web service.

#3 Referential Integrity Our approach maintains Referential Integrity during schema evolution.

Limitations

Supported Databases

Supported Operations SUPPORTED ADD COLUMN ALTER COLUMN DROP COLUMN CREATE TABLE COPY TABLE RENAME TABLE DROP TABLE ADD FOREIGN KEY DROP FOREIGN KEY NOT YET SUPPORTED CREATE INDEX DROP INDEX 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++, C. A. Curino et al. (2010)

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

Novelty • Support complex schema changes on multiple tables. • Maintain referential integrity. • Multiple database schemas can be used in parallel.

QuantumDB blog.minicom.nl - @java_devver