Speaker

• Julien RIOU
• Open Source DBA
• https://julien.riou.xyz
• @jriou@hachyderm.io

Summary

1. Basics of schema migrations
2. Who are we?
3. Chaos
4. Schema as code, CI/CD, code reviews
5. Inventory, automation
6. Heaven
7. Known issues
8. Takeaways

Basics of schema migrations

Schema

Schema changes over time

• New tables
• New columns
• New constraints
• New everything

Schema migration

ALTER TABLE customer
    ADD COLUMN archived_at TIMESTAMP WITH TIME ZONE;

Who are we?

Internal databases

Statistics

• 7 autonomous infrastructures worldwide
• 100+ clusters
• 500+ servers
• 2000+ databases
• MySQL and PostgreSQL
• Highly secure environments

Cluster example

Chaos

Who can ALTER my database?

Startup mindset

• All employees work in the same openspace
• A developer comes to the DBA desk
• Can you do X on my database, please?
• DBA SSH to the production database host
• psql database
• Translate X to queries and run them
• Job done

The DBA forgot on which host the database is

The DBA misunderstood what developer really wanted

The DBA executed the query in Europe but forgot Canada

The DBA forgot to create a transaction and one query failed in the middle

The DBA started a transaction but forgot to commit

The DBA created a query that runs for too long and locks production

The DBA forgot to use SET ROLE and permissions are now broken

Blame the DBA!

Ticketing

Let’s write migrations in a ticketing system

But

Schema as code

Git

• One repository for one or more databases
  • 534 repositories
• Well known by developers

sql-migrate

SQL schema migration tool for Go

https://github.com/rubenv/sql-migrate

Developers create code:

v0001.sql

-- +migrate Up
create table x (...);

-- +migrate Down
drop table x;