Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

[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.

4c65fc25a7ba3ba7689ce3a040f200c5?s=128

Michael de Jong

May 19, 2015
Tweet

More Decks by Michael de Jong

Other Decks in Research

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