[CHISEL] Introduction to zero-downtime schema evolution in SQL databases.

Presentation on zero-downtime schema evolution in SQL databases. A short introduction, current tools, research, and approach to deal with this problem.

1. Zero-downtime schema evolution in SQL databases
2. Michael de Jong @java_devver
3. Zero-downtime schema evolution in SQL databases You remember these, right?
4. Zero-downtime schema evolution in SQL databases Modifying tables, columns, indices, constraints, yada, yada, yada…
5. Zero-downtime schema evolution in SQL databases Without disruption, or performance degradation!
6. Relevance • Web applications • Healthcare systems • Banking / financial systems • Air travel / booking systems • Anything which is too expensive to go offline • Continuous Deployment
7. Current state It’s not that pretty…
8. • Blue-Green 
 deployments • Expand-Contract 
 deployments Approaches
9. • OpenArk kit • Percona toolkit • Large Hadron Migrator • Table Migrator • Online Schema Change Solutions Tools from industry All work more or less 
 the same way
10. 1. Current situation
11. 1. Current situation
12. 2. Create clone of table
13. 3. Copy data to new table
14. 3. Copy data to new table
15. 4. Atomically rename table
16. • PRISM++ • IMAGO • Column-oriented databases (noSQL) • Google’s Spanner & F1 database (newSQL) Solutions Academic research
17. Challenges • Referential integrity support. • Versioning schema changes. • Rolling back failures. • Complex and mostly manual task. • Mostly tools only support MySQL. • Switching to alternative database can be costly. What could go wrong?
18. What do we do now?
19. Approach 1. Create tool to test databases for blocking behaviour. 2. Profile commonly used SQL databases. 3. Prototype solution to deal with schema changes. 4. Verify prototype shows no blocking behaviour with said profiling tool. 5. Test in production environments / case studies. Run away if that fails
20. Nemesis 1. Create a table and insert 100m records. 2. “Simulate load” by barraging the database with SELECT, UPDATE, INSERT, and DELETE queries. 3. Execute a DDL query to modify the table’s structure. 4. Record all start and end times for every query. 5. Plot the results.
21. Time (100ms periods) Duration of queries (1px = 1ms) Period where DDL statement is executing Colors represent different 
 types of DML queries
22. The results I have no idea what I’m doing…
23. Add nullable column Add non-nullable column Rename nullable column Rename non-nullable column Drop nullable column Drop non-nullable column Create index Rename index Drop index ! Make column nullable Make column non-nullable Set default on nullable column Set default on non-nullable column Modify type on nullable column Modify type on non-nullable column Modify type from int to text Add non-nullable foreign key Add nullable foreign key ! Non-blocking Semi-blocking Blocking Too fast to profile
24. Add nullable column Add non-nullable column Rename nullable column Rename non-nullable column Drop nullable column Drop non-nullable column Create index Rename index Drop index ! Make column nullable Make column non-nullable Set default on nullable column Set default on non-nullable column Modify type on nullable column Modify type on non-nullable column Modify type from int to text Add non-nullable foreign key Add nullable foreign key ! Non-blocking Semi-blocking Blocking Too fast to profile
25. Take away • Don’t ever trust DDL statements 
 that operate on a “live” table. • Unless you have a very intimate 
 understanding of your own SQL database. • And then still don’t trust DDL statements.
26. What’s next? Been there, done that…
27. Next up • Discuss results on mailing lists 
 of profiled SQL databases. • I really should start blogging about this… • Lock myself up in a room, 
 and start working on a prototype.