Update Your Database Schema with Zero Downtime Migrations

You joined the DevOps movement and want to release software even faster and safer. And how do you achieve this multiple times per day without disrupting your users in production? With Zero Downtime! But what about my data? What about my relational database schema? Join us on this session and we’ll show that with code might be “easy”, and data may be “hard”, but it’s definitely possible and practical.

Transcript

1. Update your Database Schema with Zero Downtime Migrations Edson Yanaga Director of Developer Experience @yanaga
2. Edson Yanaga @yanaga
3. http://developers.redhat.com/promotions/ migrating-to-microservice-databases
4. Zero Downtime
5. Join developers.redhat.com 5 Blue Proxy Green
6. Code is easy, state is hard
7. What about my relational database?
8. Join developers.redhat.com 8
9. Zero Downtime Migrations
10. Back and Forward Compatibility
11. Baby Steps = Smallest Possible Batch Size
12. Too many rows = Long Locks
13. Shard your updates
14. Join developers.redhat.com ALTER TABLE customers RENAME COLUMN wrong TO correct; 14
15. Join developers.redhat.com ALTER TABLE customers ADD COLUMN correct VARCHAR(20); UPDATE customers SET correct = wrong WHERE id BETWEEN 1 AND 100; UPDATE customers SET correct = wrong WHERE id BETWEEN 101 AND 200; ALTER TABLE customers DELETE COLUMN wrong; 15
16. Join developers.redhat.com 16 Scenarios Add a Column Rename a Column Change Type/Format of a Column Delete a Column
17. Join developers.redhat.com 17 Add a Column 1 ADD COLUMN 2 Code computes the read value and writes to new column 3 Update data using shards 4 Code reads and writes from the new column
18. Join developers.redhat.com 18 Rename a Column 1 ADD COLUMN 2 Code reads from the old column and writes to both 3 Copy data using small shards 4 Code reads from the new column and writes to both 5 Code reads and writes from the new column 6 Delete the old column (later)
19. Join developers.redhat.com 19 Change Type/Format of a Column 1 ADD COLUMN 2 Code reads from the old column and writes to both 3 Copy data using small shards 4 Code reads from the new column and writes to both 5 Code reads and writes from the new column 6 Delete the old column (later)
20. Join developers.redhat.com 20 Delete a Column 1 DON’T 2 Stop using the read value but keep writing to the column 3 Stop writing to the column 3 Delete the column
21. What about referential integrity and not null constraints?
22. Drop them and recreate when migration is done
23. https://github.com/yanaga/zero- downtime-migrations
24. Thank You! @yanaga