Database schema migrations with zero downtime (Continuous Lifecycle London 2019)

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.

Transcript

1. DATABASE SCHEMA MIGRATIONS
   WITHOUT DOWNTIME
   MICHIEL ROOK
   @michieltcs
   

   View Slide

2. @michieltcs
   

   View Slide

3. @michieltcs
   CONTINUOUS DEPLOYMENT
   

   View Slide

4. @michieltcs
   50+ DEPLOYS PER DAY
   

   View Slide

5. @michieltcs
   HIGH AVAILABILITY
   

   View Slide

6. @michieltcs
   DATABASE MIGRATIONS
   

   View Slide

7. @michieltcs
   WITHOUT DOWNTIME?
   

   View Slide

8. @michieltcs
   DATABASE SCHEMA
   MIGRATIONS
   

   View Slide

9. @michieltcs
   DATABASE SCHEMA
   

   View Slide

10. @michieltcs
    

    View Slide

11. @michieltcs
    

    View Slide

12. @michieltcs
    DATABASE SCHEMA
    MIGRATIONS
    

    View Slide

13. @michieltcs
    SQL
    

    View Slide

14. @michieltcs
    UP
    

    View Slide

15. @michieltcs
    UP
    CREATE TABLE addresses(id INT NOT NULL,

    street VARCHAR(255) NOT NULL, PRIMARY KEY('id'));
    

    View Slide

16. @michieltcs
    UP DOWN
    

    View Slide

17. @michieltcs
    UP DOWN
    DROP TABLE addresses;
    

    View Slide

18. @michieltcs
    TRANSACTIONS
    

    View Slide

19. @michieltcs
    

    View Slide

20. @michieltcs
    FLYWAY
    

    View Slide

21. @michieltcs
    V20170228150619__create_addresses_table.sql

    

    CREATE TABLE addresses (id INT NOT NULL, street VARCHAR(255)
    NOT NULL, PRIMARY KEY(id));
    

    View Slide

22. @michieltcs
    $ flyway migrate

    Flyway Community Edition 5.0.7 by Boxfuse

    

    Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)

    Successfully validated 1 migration (execution time 00:00.018s)

    Creating Schema History table: `migrate`.`flyway_schema_history`

    Current version of schema `migrate`: << Empty Schema >>

    Migrating schema `migrate` to version 20170228150619 - create addresses table

    Successfully applied 1 migration to schema `migrate` (execution time 00:00.086s)
    

    View Slide

23. @michieltcs
    mysql> select * from flyway_schema_history;

    +----------------+----------------+------------------------+------+----------------------
    | installed_rank | version | description | type | script
    +----------------+----------------+------------------------+------+----------------------
    | 1 | 20170228150619 | create addresses table | SQL | V20170228150619__crea
    +----------------+----------------+------------------------+------+----------------------
    1 row in set (0.00 sec)
    --------------+-----------+--------------+---------------------+----------------+---------+

    | checksum | installed_by | installed_on | execution_time | success |

    --------------+-----------+--------------+---------------------+----------------+---------+

    ses_table.sql | 671780326 | root | 2018-04-10 17:37:50 | 10 | 1 |

    --------------+-----------+--------------+---------------------+----------------+---------+

    

    View Slide

24. @michieltcs
    LEGACY APPLICATIONS
    

    View Slide

25. @michieltcs
    V1__base_version.sql

    

    < SNAPSHOT OF EXISTING DATABASE SCHEMA >
    

    View Slide

26. @michieltcs
    $ flyway baseline

    Flyway Community Edition 5.0.7 by Boxfuse

    

    Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)

    Creating Schema History table: `migrate`.`flyway_schema_history`

    Successfully baselined schema with version: 1
    

    View Slide

27. @michieltcs
    --------------+----------+--------------+---------------------+----------------+---------+

    | checksum | installed_by | installed_on | execution_time | success |

    --------------+----------+--------------+---------------------+----------------+---------+

    y Baseline >> | NULL | root | 2018-04-11 08:43:14 | 0 | 1 |

    --------------+----------+--------------+---------------------+----------------+---------+

    mysql> select * from flyway_schema_history;

    +----------------+---------+-----------------------+----------+-----------------------+-
    | installed_rank | version | description | type | script |
    +----------------+---------+-----------------------+----------+-----------------------+-
    | 1 | 1 | << Flyway Baseline >> | BASELINE | << Flyway Baseline >> |
    +----------------+---------+-----------------------+----------+-----------------------+-
    1 row in set (0.00 sec)
    

    View Slide

28. @michieltcs
    REPEATABLE
    MIGRATIONS
    1
    

    View Slide

29. @michieltcs
    REPEATABLE
    MIGRATIONS
    JAVA
    MIGRATIONS
    1 2
    

    View Slide

30. @michieltcs
    REPEATABLE
    MIGRATIONS
    JAVA
    MIGRATIONS
    CALLBACKS
    1 2 3
    

    View Slide

31. @michieltcs
    REPEATABLE
    MIGRATIONS
    JAVA
    MIGRATIONS
    CALLBACKS ... AND THE PRO
    VERSION
    1 2 3 4
    

    View Slide

32. @michieltcs
    UNDOING MIGRATIONS
    

    View Slide

33. @michieltcs
    ROLL BACK

    OR

    ROLL FORWARD?
    

    View Slide

34. @michieltcs
    DESTRUCTIVE
    CHANGES
    1
    

    View Slide

35. @michieltcs
    DESTRUCTIVE
    CHANGES
    1 LONG-LIVED LOCKS
    

    View Slide

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

    View Slide

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

    View Slide

38. @michieltcs
    DESTRUCTIVE
    CHANGES
    IMPLICIT
    COMMITS
    1 2
    

    View Slide

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

    View Slide

40. @michieltcs
    DESTRUCTIVE
    CHANGES
    IMPLICIT
    COMMITS
    SHARDING MULTIPLE
    DATABASES
    1 2 3 4
    

    View Slide

41. @michieltcs
    RESTORING FROM BACKUPS?
    

    View Slide

42. @michieltcs
    ROLLBACKS
    

    View Slide

43. @michieltcs
    DOWNTIME?
    

    View Slide

44. @michieltcs
    SIMPLE DEPLOYMENT FLOW
    

    View Slide

45. @michieltcs
    BUILD
    IMAGE
    

    View Slide

46. @michieltcs
    BUILD
    IMAGE
    STOP

    CONTAINER
    

    View Slide

47. @michieltcs
    BUILD
    IMAGE
    STOP

    CONTAINER
    RUN

    MIGRATIONS
    

    View Slide

48. @michieltcs
    BUILD
    IMAGE
    STOP

    CONTAINER
    RUN

    MIGRATIONS
    START

    CONTAINER
    

    View Slide

49. @michieltcs
    DOWNTIME?
    

    View Slide

50. @michieltcs
    CONTINUOUS DELIVERY
    

    View Slide

51. @michieltcs
    CONTINUOUS DEPLOYMENT
    

    View Slide

52. @michieltcs
    ZERO DOWNTIME DEPLOYS
    

    View Slide

53. @michieltcs
    ZERO DOWNTIME DEPLOYMENT
    LOAD BALANCER
    APP 1.0
    

    View Slide

54. @michieltcs
    LOAD BALANCER
    APP 1.0 APP 2.0
    ZERO DOWNTIME DEPLOYMENT
    

    View Slide

55. @michieltcs
    LOAD BALANCER
    APP 1.0 APP 2.0
    ZERO DOWNTIME DEPLOYMENT
    

    View Slide

56. @michieltcs
    LOAD BALANCER
    APP 1.0 APP 2.0
    ZERO DOWNTIME DEPLOYMENT
    

    View Slide

57. @michieltcs
    LOAD BALANCER
    APP 2.0
    ZERO DOWNTIME DEPLOYMENT
    

    View Slide

58. @michieltcs
    DOWNTIME
    

    View Slide

59. @michieltcs
    NO MIGRATIONS!
    

    View Slide

60. @michieltcs
    DOWNTIME
    

    View Slide

61. @michieltcs
    DATABASE STATE?
    

    View Slide

62. @michieltcs
    DATABASE VERSION
    LOAD BALANCER
    APP 1.0
    DATABASE
    1.0
    expects 1.0
    

    View Slide

63. @michieltcs
    DATABASE VERSION
    LOAD BALANCER
    APP 1.0
    DATABASE
    1.0
    expects 1.0
    Migration
    

    View Slide

64. @michieltcs
    LOAD BALANCER
    APP 1.0
    DATABASE
    2.0
    expects 1.0
    DATABASE VERSION
    

    View Slide

65. @michieltcs
    LOAD BALANCER
    APP 1.0
    DATABASE
    2.0
    expects 1.0
    DATABASE VERSION
    

    View Slide

66. @michieltcs
    LOAD BALANCER
    APP 1.0 APP 2.0
    DATABASE
    2.0
    expects 1.0 expects 2.0
    DATABASE VERSION
    

    View Slide

67. @michieltcs
    OLD APP

    SHOULD WORK WITH

    NEW STATE
    

    View Slide

68. @michieltcs
    ONE DIFF BACK
    

    View Slide

69. @michieltcs
    ONE BUILD BACK
    

    View Slide

70. @michieltcs
    ONE BUILD BACK
    (git revert HEAD)
    

    View Slide

71. @michieltcs
    DECOUPLE
    

    View Slide

72. @michieltcs
    MIGRATION
    DECOUPLE
    DEPLOYMENT
    

    View Slide

73. @michieltcs
    NON-DESTRUCTIVE CHANGES
    

    View Slide

74. @michieltcs
    ADDING
    TABLES
    

    View Slide

75. @michieltcs
    ADDING
    TABLES
    ADDING
    COLUMNS
    

    View Slide

76. @michieltcs
    ADDING
    TABLES
    ADDING
    COLUMNS
    CREATING
    INDEXES
    

    View Slide

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

    View Slide

78. @michieltcs
    EXAMPLE:

    RENAMING A COLUMN
    

    View Slide

79. @michieltcs
    surname last_name
    

    View Slide

80. @michieltcs
    EXPAND - CONTRACT
    

    View Slide

81. @michieltcs
    CREATE
    COLUMN WITH
    NEW NAME
    1
    

    View Slide

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

    ADD last_name VARCHAR(255);
    

    View Slide

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

    View Slide

84. @michieltcs
    CREATE
    COLUMN WITH
    NEW NAME
    WRITE TO OLD &
    NEW COLUMNS
    1 2
    public void setLastName(String lastName) {

    this.lastName = lastName;

    this.surname = lastName;

    }
    

    View Slide

85. @michieltcs
    CREATE
    COLUMN WITH
    NEW NAME
    WRITE TO OLD &
    NEW COLUMNS
    1 2
    public String getLastName() {

    return (lastName ? lastName : surname);

    }
    

    View Slide

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

    View Slide

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

    WHERE person.last_name IS NULL;
    

    View Slide

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

    View Slide

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

    return lastName;

    }
    

    View Slide

90. @michieltcs
    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
    

    View Slide

91. @michieltcs
    

    View Slide

92. @michieltcs
    NOSQL
    

    View Slide

93. @michieltcs
    NO SCHEMA
    

    View Slide

94. @michieltcs
    NO SCHEMA?
    

    View Slide

95. @michieltcs
    {
    "firstName": "foo",
    "surName": "bar"

    }
    

    View Slide

96. @michieltcs
    {
    "firstName": "foo",
    "surName": "bar",
    "version": 1
    }
    

    View Slide

97. @michieltcs
    {
    "firstName": "foo",
    "surName": "bar",
    "version": 1
    }
    {
    "firstName": "foo",
    "lastName": "bar",
    "version": 2
    }
    

    View Slide

98. @michieltcs
    MIGRATE DURING READ
    

    View Slide

99. @michieltcs
    UPGRADE SCRIPT
    https://github.com/emirotin/mongodb-migrations
    

    View Slide

100. @michieltcs
     BACKGROUND MIGRATION
     

     View Slide

101. @michieltcs
     NO DOWNTIME
     

     View Slide

102. @michieltcs
     TESTING MIGRATIONS
     

     View Slide

103. @michieltcs
     CI/CD PIPELINE
     

     View Slide

104. @michieltcs
     1.spin up (empty) db instance
     

     View Slide

105. @michieltcs
     1.spin up (empty) db instance
     2.run migrations
     

     View Slide

106. @michieltcs
     1.spin up (empty) db instance
     2.run migrations
     3.assert
     

     View Slide

107. @michieltcs
     STAGING / ACCEPTANCE
     

     View Slide

108. @michieltcs
     CHALLENGES
     

     View Slide

109. @michieltcs
     LONG MIGRATIONS
     

     View Slide

110. @michieltcs
     LONG MIGRATIONS
     FAILURES & RESTARTS
     

     View Slide

111. @michieltcs
     LONG MIGRATIONS
     MEMORY USAGE
     FAILURES & RESTARTS
     

     View Slide

112. @michieltcs
     LONG MIGRATIONS
     MEMORY USAGE
     CLEANING UP
     FAILURES & RESTARTS
     

     View Slide

113. @michieltcs
     LONG MIGRATIONS
     MEMORY USAGE
     LOGGING & PROGRESS
     FAILURES & RESTARTS
     CLEANING UP
     

     View Slide

114. @michieltcs
     LONG MIGRATIONS
     MEMORY USAGE
     (NON-)NULLABLE TYPES
     LOGGING & PROGRESS
     FAILURES & RESTARTS
     CLEANING UP
     

     View Slide

115. @michieltcs
     ALTERNATIVE

     STRATEGIES
     

     View Slide

116. @michieltcs
     REPLICATION
     

     View Slide

117. @michieltcs
     RUN MIGRATION ON FAILOVER
     

     View Slide

118. @michieltcs
     MASTER SLAVE
     1.0 1.0
     

     View Slide

119. @michieltcs
     MASTER SLAVE
     1.0 1.0
     STOP SLAVE SQL_THREAD;
     

     View Slide

120. @michieltcs
     MASTER SLAVE
     1.0 2.0
     ALTER TABLE person

     DROP COLUMN surname;
     

     View Slide

121. @michieltcs
     MASTER SLAVE
     1.0 2.0
     START SLAVE SQL_THREAD;
     

     View Slide

122. @michieltcs
     MASTER SLAVE
     1.0 2.0
     CHANGE MASTER TO ...;
     

     View Slide

123. @michieltcs
     SLAVE MASTER
     1.0 2.0
     

     View Slide

124. @michieltcs
     SLAVE MASTER
     1.0 2.0
     STOP SLAVE SQL_THREAD;
     

     View Slide

125. @michieltcs
     SLAVE MASTER
     2.0 2.0
     ALTER TABLE person

     DROP COLUMN surname;
     

     View Slide

126. @michieltcs
     SLAVE MASTER
     2.0 2.0
     START SLAVE SQL_THREAD;
     

     View Slide

127. @michieltcs
     SLAVE MASTER
     2.0 2.0
     

     View Slide

128. @michieltcs
     MESSAGE QUEUE
     

     View Slide

129. @michieltcs
     UI
     @michieltcs
     

     View Slide

130. @michieltcs
     UI
     messages
     Queue /
     Messaging Bus
     @michieltcs
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

137. @michieltcs
     CQRS
     

     View Slide

138. @michieltcs
     COMMAND QUERY
     RESPONSIBILITY SEGREGATION
     

     View Slide

139. @michieltcs
     SEPARATE

     WRITES FROM READS
     

     View Slide

140. @michieltcs
     STORE UPDATES IN LOG
     

     View Slide

141. @michieltcs
     COMMIT LOG
     Write
     

     View Slide

142. @michieltcs
     @michieltcs
     UI
     Data Layer
     Database
     messages
     queries DTOs
     Message

     queue
     Backend
     messages
     Commit Log
     

     View Slide

143. @michieltcs
     REPLAY LOG TO
     (RE)BUILD DATABASE
     

     View Slide

144. @michieltcs
     @michieltcs
     UI
     Data Layer
     Database
     messages
     queries DTOs
     Backend
     messages
     Database
     Commit Log
     Message

     queue
     

     View Slide

145. @michieltcs
     @michieltcs
     UI
     Data Layer
     Database
     messages
     queries DTOs
     Backend
     messages
     Database
     Commit Log
     Message

     queue
     

     View Slide

146. @michieltcs
     PT ONLINE SCHEMA CHANGE
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

151. @michieltcs
     pt-online-schema-change --execute

     --alter "add index name_idx (name)" D=test,t=test1,h=localhost
     

     View Slide

152. @michieltcs
     RECAP
     

     View Slide

153. @michieltcs
     PLANNING
     

     View Slide

154. @michieltcs
     MULTIPLE STEPS
     

     View Slide

155. @michieltcs
     NO BREAKING CHANGES
     

     View Slide

156. THANK YOU

     FOR LISTENING!
     @michieltcs / michiel@michielrook.nl
     www.michielrook.nl
     

     View Slide