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

[SIC '16] Zero-Downtime Database Schema Evolution

[SIC '16] Zero-Downtime Database Schema Evolution

Talk given at the Dutch Software Industry Conference (SIC) 2016, describing the challenges and potential solutions for dealing with schema changes in an ever changing production environment without affecting dependent services.

4c65fc25a7ba3ba7689ce3a040f200c5?s=128

Michael de Jong

March 17, 2016
Tweet

More Decks by Michael de Jong

Other Decks in Programming

Transcript

  1. Zero-Downtime Database Schema Evolution Michael de Jong

  2. Michael de Jong @java_devver

  3. Continuous Delivery Continuous Integration Continuous Deployment

  4. Continuous Deployment Code deploys

  5. BUT WHAT ABOUT State?

  6. Continuous Deployment Database deploys

  7. • Fast release cycles • Quick bug-fixes • A/B testing

    • Rigid schema • ACID transactions • Constraints Continuous Deployment SQL Databases
  8. Change Rigid VS.

  9. How do you deal with this? Version 1 SQL Database

    Version 2
  10. • Make only small changes to the database schema (pref.

    just one). • Ensure that both versions of the database schema are compatible with both the current and the next version of the application. How do you deal with this?
  11. • Make only small changes to the database schema (pref.

    just one). • Ensure that both versions of the database schema are compatible with both the current and the next version of the application. That’s not always possible!
  12. • Make only small changes to the database schema (pref.

    just one). • Ensure that both versions of the database schema are compatible with both the current and the next version of the application. This is error-prone, and 
 can require multiple deploys
  13. • Depending on the database you’re using, various schema operations

    may block read/write operations on a table while the schema operation is being executed. An additional challenge
  14. http:/ /blog.minicom.nl/blog/2015/04/03/revisiting-profiling-ddl-statements-mysqls-return/

  15. 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
  16. • Large Hadron Migrator • Online Schema Change But what

    about these tools? • TableMigrator • Percona/OAK toolkit
  17. • Support zero-downtime changes. • No support for FK constraints.

    But what about these tools? • Can only do one operation, on one table at a time.
  18. • Complex schema changes • Avoid blocking reads/writes What do

    we need? • Expose two versions of the schema • Support FK constraints
  19. QuantumDB

  20. movies rentals customers Netflix 2.0

  21. 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
  22. ORIGINAL STATE INTERMEDIATE STATE FUTURE STATE ADD COLUMN ADD FOREIGN

    KEY Defining changes
  23. movies rentals customers Evolving the schema

  24. movies rentals customers customers* Evolving the schema Forking the table

    under change
  25. movies rentals rentals* customers customers* Evolving the schema Forking dependent

    tables
  26. movies rentals* customers* rentals customers Evolving the schema A structurally

    forked database schema
  27. movies rentals* customers* rentals customers Evolving the schema Creating forward

    database triggers
  28. movies rentals* customers* rentals customers Evolving the schema Mirror data

  29. movies customers* rentals customers rentals* Evolving the schema Creating backward

    database triggers
  30. movies rentals* customers* rentals customers Original Accessing the database

  31. Future Original movies rentals* customers* rentals customers Accessing the database

    Two co-existing database schemas
  32. Application Driver Accessing the database

  33. Application Wrapper Driver Accessing the database Intercepting database interactions

  34. 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 Intercepting database interactions
  35. 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 Rewriting intercepted queries
  36. movies customers* rentals customers rentals* Dropping a version When it’s

    no longer needed
  37. movies rentals* customers* rentals customers Dropping a version Drop the

    database triggers
  38. movies rentals* customers* Dropping a version Drop tables only used

    in the obsolete version
  39. Future movies rentals* customers* Dropping a version Leaving you with

    the resulting schema
  40. Demo

  41. None
  42. None
  43. None
  44. None
  45. None
  46. None
  47. None
  48. None
  49. None
  50. None
  51. None
  52. None
  53. Complex schema changes? • With QuantumDB you can apply multiple

    operations to multiple tables at once. QuantumDB automatically determines the “future state” of the schema, and how to get there without changing the existing tables.
  54. Two versions of the schema? • QuantumDB creates and exposes

    two or more versions of the database schema through a custom JDBC driver. • Allows you to run two or more versions of your service in parallel, each version connected its own schema.
  55. Blocking read/writes? • QuantumDB only applies schema operations to new

    tables. Thus the client is never blocked by operations executed by QuantumDB.
  56. Support FK constraints? • QuantumDB does the entire migration without

    disabling or removing FK constraints.
  57. Supported databases

  58. 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 CREATE VIEW DROP VIEW DECOMPOSE TABLE * JOIN TABLE * MERGE TABLE * PARTITION TABLE * * Update Rewriting and Integrity Constraint Maintenance in a Schema Evolution Support System: PRISM++ Supported operations
  59. QuantumDB quantumdb.io - @java_devver