4 ms·

I'm more interested in hearing about what the workflow is like for developers on larger teams. Do they each work on their own features, write separate migration

I'm more interested in hearing about what the workflow is like for developers on larger teams. Do they each work on their own features, write separate migrations, and have a DBA approve and merge them.

evanelias 7y ago
For a "very large company dedicated to moving fast" example, here's what the process looked like at Facebook a few years ago. AFAIK same process today, with one improvement noted below. Background: * Almost everything is self-service by necessity. Except for some high-blast-radius cases, dev teams are able to manage their own schemas without needing MySQL team intervention. This is made possible by having automation that has appropriate safeties built in. * There's a repository (git, hg, whatever) storing schemas. It has a couple levels of subdirectories to organize different database tiers and individual databases. In each of the bottom-level subdirs, there are text files containing CREATE TABLE statements, one file per table. In other words, this is a declarative repo, modeling the ideal state of tables in each database. Process to add or change a table: 1. Just add or change a CREATE TABLE statement, and commit in SCM. 2. Submit a diff (pull request). Someone on your team reviews it, same as a code review. 3. Once merged, the schema change can be kicked off. (A few years ago, a dev would need to run a simple CLI command to tell the automation "please begin working on this table", but I believe this has been automated away since then.) The tooling automatically manages running the correct DDL safely, on the correct machine(s), even in the case of a large sharded table. Devs never need to write ALTER TABLE statements; everything is just based on CREATE TABLE. There was a separate flow (with extra steps, on purpose) for destructive actions like dropping tables or columns.
- erobbins 7y ago
  The one weakness of this system is that it doesn't understand or handle foreign key constraints. If you have those you have to manage it the old fashioned way (whatever that is for you)
  - evanelias 7y ago
    That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db purists, but there are a number of solid reasons: Foreign keys aren't shard-aware, greatly reducing their utility. They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this really matters. They don't play nice with online schema change tools in general -- not just fb-osc. These tools all involve creating shadow tables and propagating changes to them, which is problematic with foreign keys.
    - sokoloff 7y ago
      We were a large MS-SQL shop and we had the same. No FKeys in test or prod and we were "only" a billion and change e-commerce, nowhere near a social media site level of traffic. To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)
notJim 7y ago
When I worked at Etsy (a couple years ago now, so this is out of date), devs wrote `ALTER` statements and included them in a ticket. Once a week, the DBAs would run all the migrations. The only real things I remember worrying about was making sure to set a default if you were adding a column, and if you were changing a large table, it'd take a long time. My current company uses mongodb, so migrations aren't a thing. It's pretty nice, TBH.

evanelias 7y ago

For a "very large company dedicated to moving fast" example, here's what the process looked like at Facebook a few years ago. AFAIK same process today, with one improvement noted below. Background: * Almost everything is self-service by necessity. Except for some high-blast-radius cases, dev teams are able to manage their own schemas without needing MySQL team intervention. This is made possible by having automation that has appropriate safeties built in. * There's a repository (git, hg, whatever) storing schemas. It has a couple levels of subdirectories to organize different database tiers and individual databases. In each of the bottom-level subdirs, there are text files containing CREATE TABLE statements, one file per table. In other words, this is a declarative repo, modeling the ideal state of tables in each database. Process to add or change a table: 1. Just add or change a CREATE TABLE statement, and commit in SCM. 2. Submit a diff (pull request). Someone on your team reviews it, same as a code review. 3. Once merged, the schema change can be kicked off. (A few years ago, a dev would need to run a simple CLI command to tell the automation "please begin working on this table", but I believe this has been automated away since then.) The tooling automatically manages running the correct DDL safely, on the correct machine(s), even in the case of a large sharded table. Devs never need to write ALTER TABLE statements; everything is just based on CREATE TABLE. There was a separate flow (with extra steps, on purpose) for destructive actions like dropping tables or columns.

erobbins 7y ago
The one weakness of this system is that it doesn't understand or handle foreign key constraints. If you have those you have to manage it the old fashioned way (whatever that is for you)
- evanelias 7y ago
  That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db purists, but there are a number of solid reasons: Foreign keys aren't shard-aware, greatly reducing their utility. They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this really matters. They don't play nice with online schema change tools in general -- not just fb-osc. These tools all involve creating shadow tables and propagating changes to them, which is problematic with foreign keys.
  - sokoloff 7y ago
    We were a large MS-SQL shop and we had the same. No FKeys in test or prod and we were "only" a billion and change e-commerce, nowhere near a social media site level of traffic. To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)

notJim 7y ago

When I worked at Etsy (a couple years ago now, so this is out of date), devs wrote `ALTER` statements and included them in a ticket. Once a week, the DBAs would run all the migrations. The only real things I remember worrying about was making sure to set a default if you were adding a column, and if you were changing a large table, it'd take a long time. My current company uses mongodb, so migrations aren't a thing. It's pretty nice, TBH.