Database schema migrations with zero downtime (Confoo Montreal 2018)

Database schema migrations with zero downtime (Confoo Montreal 2018)

Does your application use a database, and have you ever changed the database schema? Then you probably know these database migrations can lead to downtime and can be an obstacle to implementing continuous delivery.

In this talk we’ll discuss non-destructive changes, rollbacks, large data sets, useful tools and a few strategies to migrate our data safely, with minimum disruption to production.

2f4800411154a8c66dde489448a044d2?s=128

Michiel Rook

March 07, 2018
Tweet

Transcript

  1. DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

  2. ▸ Freelance Developer & Coach ▸ @michieltcs

  3. None
  4. CONTINUOUS DEPLOYMENT

  5. 50+ DEPLOYS PER DAY

  6. HIGH AVAILABILITY

  7. DATABASE MIGRATIONS

  8. WITHOUT DOWNTIME?

  9. DATABASE SCHEMA MIGRATIONS

  10. DATABASE SCHEMA

  11. None
  12. DATABASE SCHEMA MIGRATIONS

  13. SQL

  14. UP @michieltcs

  15. UP DOWN @michieltcs

  16. TRANSACTIONS

  17. @michieltcs

  18. FLYWAY

  19. V20170228150619-create_addresses_table.sql
 
 CREATE TABLE addresses (id INT NOT NULL, street

    VARCHAR(255) NOT NULL, PRIMARY KEY(id))'); @michieltcs
  20. mysql> select * from schema_version;
 +----------------+----------------+------------------------+----------+------------------------------------ | installed_rank | version

    | description | type | script +----------------+----------------+------------------------+----------+------------------------------------ | 1 | 1 | << Flyway Baseline >> | BASELINE | << Flyway Baseline >> | 2 | 20170228150619 | create addresses table | SQL | V20170228150619__create-addresses-t +----------------+----------------+------------------------+----------+------------------------------------ 2 rows in set (0,00 sec) +------------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 +------------+--------------+---------------------+----------------+---------+
 | NULL | migrations_1 | 2017-02-28 16:27:21 | 0 | 1 |
 | 1789629568 | migrations_1 | 2017-02-28 16:27:21 | 16 | 1 |
 +------------+--------------+---------------------+----------------+---------+ @michieltcs
  21. UNDOING MIGRATIONS

  22. ROLL BACK
 OR
 ROLL FORWARD?

  23. DESTRUCTIVE CHANGES 1 @michieltcs

  24. DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS @michieltcs

  25. DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES @michieltcs

  26. DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES DELETES @michieltcs

  27. DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2 @michieltcs

  28. DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING 1 2 3 @michieltcs

  29. DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING MULTIPLE DATABASES 1 2 3

    4 @michieltcs
  30. RESTORING FROM BACKUPS?

  31. ROLLBACKS

  32. DOWNTIME?

  33. CREATE PACKAGE @michieltcs

  34. CREATE PACKAGE SHUT DOWN APPLICATION @michieltcs

  35. CREATE PACKAGE SHUT DOWN APPLICATION RUN MIGRATION @michieltcs

  36. CREATE PACKAGE SHUT DOWN APPLICATION RUN MIGRATION DEPLOY PACKAGE @michieltcs

  37. CREATE PACKAGE SHUT DOWN APPLICATION RUN MIGRATION DEPLOY PACKAGE RUN

    APPLICATION @michieltcs
  38. DOWNTIME?

  39. CONTINUOUS DELIVERY

  40. DEV BUILD / TEST ACCEPTANCE PRODUCTION CONTINUOUS DELIVERY @michieltcs

  41. CONTINUOUS DEPLOYMENT

  42. DEV BUILD / TEST STAGING / ACCEPTANCE PRODUCTION CONTINUOUS DEPLOYMENT

    @michieltcs
  43. ZERO DOWNTIME DEPLOYS

  44. ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP V1 @michieltcs

  45. LOAD BALANCER APP V1 APP V2 ZERO DOWNTIME DEPLOYMENT @michieltcs

  46. LOAD BALANCER APP V1 APP V2 ZERO DOWNTIME DEPLOYMENT @michieltcs

  47. LOAD BALANCER APP V1 APP V2 ZERO DOWNTIME DEPLOYMENT @michieltcs

  48. LOAD BALANCER APP V2 ZERO DOWNTIME DEPLOYMENT @michieltcs

  49. DOWNTIME

  50. NO MIGRATIONS!

  51. DOWNTIME

  52. DATABASE STATE?

  53. DATABASE VERSION LOAD BALANCER APP V1 DATABASE V1 expects v1

    @michieltcs
  54. LOAD BALANCER APP V1 DATABASE V2 expects v1 DATABASE VERSION

    @michieltcs
  55. LOAD BALANCER APP V1 APP V2 DATABASE V2 expects v1

    expects v2 DATABASE VERSION @michieltcs
  56. OLD APP
 SHOULD WORK WITH
 NEW STATE

  57. DECOUPLE

  58. MIGRATION DECOUPLE DEPLOYMENT

  59. EXPAND - CONTRACT

  60. BACKWARDS COMPATIBILITY

  61. CLIENT EXISTING API CLIENT CLIENT @michieltcs

  62. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  63. DEPRECATION

  64. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  65. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  66. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  67. CLIENT CLIENT CLIENT NEW
 API @michieltcs

  68. NON-DESTRUCTIVE CHANGES

  69. ADDING TABLES @michieltcs

  70. ADDING TABLES ADDING COLUMNS @michieltcs

  71. ADDING TABLES ADDING COLUMNS CREATING INDEXES @michieltcs

  72. ADDING TABLES ADDING COLUMNS CREATING INDEXES sometimes ... @michieltcs

  73. ONE DIFF BACK

  74. EXAMPLE:
 RENAMING A COLUMN

  75. surname last_name

  76. CREATE COLUMN WITH NEW NAME 1 @michieltcs

  77. CREATE COLUMN WITH NEW NAME 1 @michieltcs ALTER TABLE person


    ADD last_name VARCHAR(255);
  78. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS 1 2 @michieltcs
  79. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS 1 2 @michieltcs public void setLastName(String lastName) {
 this.lastName = lastName;
 this.surname = lastName;
 }
  80. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS 1 2 @michieltcs public String getLastName() {
 return (lastName ? lastName : surname);
 }
  81. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS 1 2 3 @michieltcs
  82. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS 1 2 3 @michieltcs UPDATE person SET person.last_name = person.surname
 WHERE person.last_name IS NULL;
  83. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 @michieltcs
  84. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 @michieltcs public String getLastName() {
 return lastName;
 }
  85. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 remove old column and code @michieltcs
  86. None
  87. TESTING MIGRATIONS

  88. CI/CD PIPELINE

  89. STAGING / ACCEPTANCE

  90. FEATURE TOGGLES

  91. ENABLE/DISABLE FEATURES @michieltcs

  92. ALTERNATIVE CODE PATHS ENABLE/DISABLE FEATURES @michieltcs

  93. ALTERNATIVE CODE PATHS ENABLE/DISABLE FEATURES AT RUNTIME @michieltcs

  94. None
  95. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 feature toggle feature toggle @michieltcs
  96. NOSQL

  97. NO SCHEMA

  98. MIGRATE DURING READ

  99. BACKGROUND MIGRATION

  100. NO DOWNTIME

  101. CHALLENGES

  102. LONG MIGRATIONS

  103. LONG MIGRATIONS LEGACY APPLICATIONS

  104. LONG MIGRATIONS MEMORY USAGE LEGACY APPLICATIONS

  105. LONG MIGRATIONS MEMORY USAGE LEGACY APPLICATIONS FAILURES & RESTARTS

  106. LONG MIGRATIONS FAILURES & RESTARTS MEMORY USAGE LOGGING & PROGRESS

    LEGACY APPLICATIONS
  107. LONG MIGRATIONS FAILURES & RESTARTS MEMORY USAGE (NON-)NULLABLE TYPES LEGACY

    APPLICATIONS LOGGING & PROGRESS
  108. STRATEGIES

  109. REPLICATION

  110. RUN MIGRATION ON FAILOVER

  111. MASTER SLAVE 1.0 1.0

  112. MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

  113. MASTER SLAVE 1.0 2.0 ALTER TABLE person
 DROP COLUMN surname;

  114. MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

  115. MASTER SLAVE 1.0 2.0 CHANGE MASTER TO ...;

  116. SLAVE MASTER 1.0 2.0

  117. SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

  118. SLAVE MASTER 2.0 2.0 ALTER TABLE person
 DROP COLUMN surname;

  119. SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

  120. SLAVE MASTER 2.0 2.0

  121. MESSAGING BUS / QUEUE

  122. UI @michieltcs

  123. UI messages Queue / Messaging Bus @michieltcs

  124. UI messages Queue / Messaging Bus Backend messages @michieltcs

  125. UI Database messages Queue / Messaging Bus Backend messages @michieltcs

  126. UI Data Layer Database messages queries DTOs Queue / Messaging

    Bus Backend messages @michieltcs
  127. UI Data Layer Database messages queries DTOs Queue / Messaging

    Bus Backend messages @michieltcs
  128. UI Data Layer Database messages queries DTOs Queue / Messaging

    Bus Backend messages @michieltcs
  129. UI Data Layer Database messages queries DTOs Queue / Messaging

    Bus Backend messages @michieltcs
  130. CQRS

  131. COMMAND QUERY RESPONSIBILITY SEGREGATION

  132. SEPARATE
 WRITES FROM READS

  133. KAFKA

  134. STORE UPDATES IN LOG

  135. UI Data Layer Database messages queries DTOs Kafka Backend messages

    @michieltcs Storage
  136. REPLAY LOG TO (RE)BUILD DATABASE

  137. UI Data Layer Database messages queries DTOs Kafka Backend messages

    @michieltcs Storage Database
  138. UI Data Layer Database messages queries DTOs Kafka Backend messages

    @michieltcs Storage Database
  139. PT ONLINE SCHEMA CHANGE

  140. CREATE SHADOW COPY (TARGET TABLE) 1 @michieltcs

  141. CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES

    1 2 @michieltcs
  142. CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES

    COPY SOURCE DATA IN SMALL CHUNKS 1 2 3 @michieltcs
  143. CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES

    COPY SOURCE DATA IN SMALL CHUNKS RENAME TARGET TABLE 1 2 3 4 @michieltcs
  144. RECAP

  145. PLANNING

  146. MULTIPLE STEPS

  147. NO BREAKING CHANGES

  148. LITERATURE databaserefactoring.com github.com/barsoom/devbook/

  149. THANK YOU! @michieltcs / michiel@michielrook.nl www.michielrook.nl