Pro Yearly is on sale from $80 to $50! »

Database Schema Migrations with Zero Downtime (PHPUK 2018)

2f4800411154a8c66dde489448a044d2?s=47 Michiel Rook
February 15, 2018

Database Schema Migrations with Zero Downtime (PHPUK 2018)

Does your application or service use a database? When that application changes because of new business requirements, you may need to make changes to the database schema. These database migrations could lead to downtime and can be an obstacle to implementing continuous delivery/deployment. How can we deal with database migrations when we don’t want our end-users to experience downtime, and want to keep releasing? 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

February 15, 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. DOCTRINE

  18. class Version20170228150619 extends AbstractMigration
 {
 public function up(Schema $schema)
 {


    $this->addSql('CREATE TABLE addresses (id INT NOT NULL, street VARCHAR(255) NOT NULL, PRIMARY KEY(id))');
 }
 
 public function down(Schema $schema)
 {
 $this->addSql('DROP TABLE addresses');
 }
 } @michieltcs
  19. mysql> select * from migration_versions;
 +----------------+
 | version |
 +----------------+


    | 20170228150619 |
 +----------------+
 1 row in set (0,00 sec) @michieltcs
  20. FLYWAY

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

    VARCHAR(255) NOT NULL, PRIMARY KEY(id))'); @michieltcs
  22. 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
  23. UNDOING MIGRATIONS

  24. ROLL BACK
 OR
 ROLL FORWARD?

  25. DESTRUCTIVE CHANGES 1 @michieltcs

  26. DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS @michieltcs

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

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

  29. DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2 @michieltcs

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

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

    4 @michieltcs
  32. RESTORING FROM BACKUPS?

  33. ROLLBACKS

  34. DOWNTIME?

  35. CREATE PACKAGE @michieltcs

  36. CREATE PACKAGE SHUT DOWN APPLICATION @michieltcs

  37. CREATE PACKAGE SHUT DOWN APPLICATION RUN MIGRATION @michieltcs

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

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

    APPLICATION @michieltcs
  40. DOWNTIME?

  41. CONTINUOUS DELIVERY

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

  43. CONTINUOUS DEPLOYMENT

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

    @michieltcs
  45. ZERO DOWNTIME DEPLOYS

  46. ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP V1 @michieltcs

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

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

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

  50. LOAD BALANCER APP V2 ZERO DOWNTIME DEPLOYMENT @michieltcs

  51. DOWNTIME

  52. NO MIGRATIONS!

  53. DOWNTIME

  54. DATABASE STATE?

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

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

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

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

  59. DECOUPLE

  60. MIGRATION DECOUPLE DEPLOYMENT

  61. EXPAND - CONTRACT

  62. BACKWARDS COMPATIBILITY

  63. CLIENT EXISTING API CLIENT CLIENT @michieltcs

  64. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  65. DEPRECATION

  66. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  67. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  68. CLIENT EXISTING API CLIENT CLIENT NEW
 API @michieltcs

  69. CLIENT CLIENT CLIENT NEW
 API @michieltcs

  70. NON-DESTRUCTIVE CHANGES

  71. ADDING TABLES @michieltcs

  72. ADDING TABLES ADDING COLUMNS @michieltcs

  73. ADDING TABLES ADDING COLUMNS CREATING INDEXES @michieltcs

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

  75. ONE DIFF BACK

  76. EXAMPLE:
 RENAMING A COLUMN

  77. surname last_name

  78. CREATE COLUMN WITH NEW NAME 1 @michieltcs

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


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

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

    COLUMNS 1 2 @michieltcs public setLastName(string $lastName): void
 {
 $this->lastName = $lastName;
 $this->surname = $lastName;
 }
  82. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS 1 2 @michieltcs public getLastName(): string
 {
 return $this->surname;
 }
  83. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

    COLUMNS MIGRATE OLD RECORDS 1 2 3 @michieltcs
  84. 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;
  85. CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW

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

    COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 @michieltcs public getLastName(): string
 {
 return $this->lastName;
 }
  87. 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
  88. None
  89. CHALLENGES

  90. DROP COLUMN (ON LARGE TABLES)

  91. (NON-)NULLABLE TYPES

  92. FEATURE TOGGLES

  93. ENABLE/DISABLE FEATURES @michieltcs

  94. ALTERNATIVE CODE PATHS ENABLE/DISABLE FEATURES @michieltcs

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

  96. None
  97. 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
  98. TESTING MIGRATIONS

  99. CI/CD PIPELINE

  100. STAGING / ACCEPTANCE

  101. NOSQL

  102. NO SCHEMA

  103. MIGRATE DURING READ

  104. BACKGROUND MIGRATION

  105. NO DOWNTIME

  106. LARGE DATASETS

  107. LONG MIGRATIONS

  108. LONG MIGRATIONS LONG RUNNING QUERIES

  109. LONG MIGRATIONS MEMORY USAGE LONG RUNNING QUERIES

  110. LONG MIGRATIONS FAILURES MEMORY USAGE LONG RUNNING QUERIES

  111. LONG MIGRATIONS FAILURES MEMORY USAGE RESTARTING, RESUMING LONG RUNNING QUERIES

  112. LONG MIGRATIONS FAILURES MEMORY USAGE RESTARTING, RESUMING LOGGING & PROGRESS

    LONG RUNNING QUERIES
  113. RUN MIGRATION ON FAILOVER

  114. OTHER STRATEGIES

  115. REPLICATION

  116. MASTER SLAVE 1.0 1.0

  117. MASTER SLAVE 1.0 1.0

  118. MASTER SLAVE 1.0 2.0

  119. SLAVE MASTER 1.0 2.0

  120. SLAVE MASTER 2.0 2.0

  121. SLAVE MASTER 2.0 2.0

  122. MESSAGING BUS / QUEUE

  123. KAFKA

  124. UI @michieltcs

  125. UI messages Queue / Messaging Bus @michieltcs

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

  127. UI Database messages 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
 READS FROM WRITES

  133. STORE UPDATES IN LOG

  134. ALSO: EVENT SOURCING

  135. UI Handlers Commands Data Layer Database Database updates events queries

    DTOs Repository Database @michieltcs
  136. NEW QUERY MODEL @michieltcs Get next update Apply to new

    query model Last update? Use new query model yes no
  137. REPLAY LOG TO REBUILD STAGING

  138. TOOLS

  139. @michieltcs

  140. PT ONLINE SCHEMA CHANGE

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

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

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

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

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

  146. MULTIPLE STEPS

  147. NO BREAKING CHANGES

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

  149. THANK YOU! https://joind.in/talk/157ea @michieltcs / michiel@michielrook.nl www.michielrook.nl