Zero-downtime schema evolution in SQL databases 

Michael de Jong @java_devver 

Zero-downtime schema evolution in SQL databases You remember these, right? 

Zero-downtime schema evolution in SQL databases Modifying tables, columns, indices, constraints, yada, yada, yada… 

Zero-downtime schema evolution in SQL databases Without disruption, or performance degradation! 

Relevance • Web applications • Healthcare systems • Banking / financial systems • Air travel / booking systems • Anything which is too expensive to go offline • Continuous Deployment 

Current state It’s not that pretty… 

• Blue-Green 
 deployments • Expand-Contract 
 deployments Approaches 

• OpenArk kit • Percona toolkit • Large Hadron Migrator • Table Migrator • Online Schema Change Solutions Tools from industry All work more or less 
 the same way 

1. Current situation 

1. Current situation 

2. Create clone of table 

3. Copy data to new table 

3. Copy data to new table 

4. Atomically rename table 

• PRISM++ • IMAGO • Column-oriented databases (noSQL) • Google’s Spanner & F1 database (newSQL) Solutions Academic research 

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? 

What do we do now? 

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 

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. 

Time (100ms periods) Duration of queries (1px = 1ms) Period where DDL statement is executing Colors represent different 
 types of DML queries 

The results I have no idea what I’m doing… 

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 

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 

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. 

What’s next? Been there, done that… 

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.