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

Zero-Downtime SQL Database Schema Evolution for Continuous Deployment

Zero-Downtime SQL Database Schema Evolution for Continuous Deployment

MSc thesis defense presentation on QuantumDB.

4c65fc25a7ba3ba7689ce3a040f200c5?s=128

Michael de Jong

August 26, 2015
Tweet

More Decks by Michael de Jong

Other Decks in Technology

Transcript

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

    Jong
  2. Deployment Continuous

  3. Write code Push to master Run test suite Package deliverable

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

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

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

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

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

    Deploy into production
  9. Continuous Integration Write code Push to master Run test suite

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

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

    Deploy into production Continuous Deployment
  12. SO WHAT’S THE Problem?

  13. SQL Databases

  14. Continuous Deployment Faster release cycles Quick bug-fixes A/B testing ACID

    transactions Rigid schema Constraints for consistency SQL Databases
  15. Continuous Deployment Continuous change VS. Change? No thank you… SQL

    Databases
  16. Deploying a new version of a web service into production

    is well understood.
  17. How to deal with a SQL database when deploying a

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

    new version of a web service into production… … not so much
  19. “I deploying database schema changes into production!” — said no

    software engineer ever
  20. Reason 1: DDL statements

  21. Nemesis:

  22. 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
  23. Reason 2: Mixed-State

  24. API

  25. API API NEW

  26. Reason 3: Referential Integrity

  27. QuantumDB’s Approach

  28. Imagine if you will… movies rentals customers

  29. 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
  30. 82fba53 - CURRENT STATE 2ef5c1a 80bfa11 - FINAL STATE ADD

    COLUMN ADD FOREIGN KEY Defining changes
  31. movies rentals customers Evolving the database schema

  32. movies rentals customers customers* Evolving the database schema

  33. movies rentals rentals* customers customers* Evolving the database schema

  34. movies rentals* customers* rentals customers Evolving the database schema

  35. movies rentals* customers* rentals customers Evolving the database schema

  36. movies rentals* customers* rentals customers Evolving the database schema

  37. movies customers* rentals customers rentals* Evolving the database schema

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

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

  40. Accessing the database Application Driver

  41. Application Wrapper Driver Accessing the database

  42. 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
  43. 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
  44. movies customers* rentals customers rentals* Dropping a database schema

  45. movies rentals* customers* rentals customers Dropping a database schema

  46. movies rentals* customers* Dropping a database schema

  47. 80bfa11 movies rentals* customers* Dropping a database schema

  48. Demo-Time!

  49. None
  50. None
  51. None
  52. None
  53. None
  54. None
  55. None
  56. None
  57. None
  58. None
  59. None
  60. None
  61. LET’S Evaluate

  62. #1 - DDL statements QuantumDB only applies DDL statements to

    tables which are not yet in use. Thus the client is never blocked by QuantumDB.
  63. #2 - Mixed-State Database clients can only access the tables

    in their chosen version of the database schema.
  64. #3 - Referential Integrity Foreign key constraints are always adhered

    to, and mirrored when needed.
  65. Too good to be true?

  66. Supported databases

  67. 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
  68. Copying data is limited by disk I/O. We could actively

    throttle this based on some metric. Hardware limitations
  69. • QuantumDB, which supports complex schema changes on multiple tables.

    • Maintains referential integrity. • Can use up to two database schemas in parallel. Contributions
  70. • Do it in a non-destructive, non performance-degrading fashion. •

    Nemesis, which is able to benchmark various schema evolution scenarios. Contributions
  71. QuantumDB quantumdb.io - @java_devver