[RELENG '15] Continuous Deployment & Schema Evolution in SQL Databases

Slides from my RelEng '15 presentation on early progress made with the QuantumDB proof of concept to manage and orchestrate schema evolution in a zero-downtime fashion.

Transcript

1. Continuous Deployment and Schema Evolution in SQL Databases
2. Michael de Jong @java_devver
3. Arie van Deursen @avandeursen
4. But besides being a student… … I’m also a practitioner
5. My long-time interest: How do you deploy a new version of a web service without downtime?
6. SQL Databases!
7. Relevance Continuous Deployment rapidly delivers business value. It is important to remove any impediments to its adoption.
8. Why is it so difficult?
9. Reason 1: DDL statements Statements which change the structure of the database (tables, columns, indices, foreign keys, etc)
10. They prevent other queries from being executed!
11. Reason 2: Mixed-State During deploying, two different versions might be accessing the same database
12. Reason 3: Referential Integrity None of the surveyed tools and approaches fully support foreign keys.
13. Our proposal
14. changelog.addChangeSet(“Add referral column to customers table”, addColumn(“customers”, “referred_by”, int()), addForeignKey(“customers”, “referred_by”) .referencing(“customers”, “id”)); Defining changes
15. Defining changes 82fba53 - CURRENT STATE 2ef5c1a 80bfa11 - FINAL STATE ADD COLUMN ADD FOREIGN KEY
16. Evolving the database schema movies rentals customers
17. Evolving the database schema movies rentals customers customers*
18. movies rentals customers customers* rentals* Evolving the database schema
19. movies rentals customers customers* rentals* Evolving the database schema
20. movies rentals customers customers* rentals* Evolving the database schema
21. movies rentals customers customers* rentals* Evolving the database schema
22. Accessing the database movies rentals customers customers* rentals* Schema: 82fba53
23. movies rentals customers customers* rentals* Schema: 82fba53 Schema: 80bfa11 Accessing the database
24. Application Driver Accessing the database
25. Application Wrapper Driver Accessing the database
26. DriverManager.getConnection( “jdbc:postgresql://localhost:5432/database”, “username”, “password”); DriverManager.getConnection( “jdbc:quantumdb:postgresql://localhost:5432/database?version=80bfa11”, “username”, “password”); Accessing the database
27. 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
28. Dropping a schema movies rentals customers customers* rentals*
29. movies rentals customers customers* rentals* Dropping a schema
30. movies customers* rentals* Dropping a schema
31. movies customers* rentals* Schema: 80bfa11 Dropping a schema
32. Early evaluation
33. #1 Blocking DDL statements Avoids using blocking DDL statements, since all affected tables are recreated from scratch.
34. #2 Mixed-State Tables from other database schemas are hidden from the web service.
35. #3 Referential Integrity Our approach maintains Referential Integrity during schema evolution.
36. Limitations
37. Supported Databases
38. 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)
39. I/O Limitations Copying data is limited by disk I/O. We could actively throttle this based on some metric.
40. Novelty • Support complex schema changes on multiple tables. • Maintain referential integrity. • Multiple database schemas can be used in parallel.
41. QuantumDB blog.minicom.nl - @java_devver