Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How does your development team handle database migrations?

news.ycombinator.com

Post reply on HN
Print view View original

101–110 of 150 posts

Re: Ask HN: How does your development team handle database migrations?

#101

Post by PaulMest »

evanelias wrote: post #83
PaulMest wrote: post #76

Earlier quoted context omitted.

I am guessing that you are probably not using Python/Django... but is this any different than what Django offers? Django allows you to define your models (schema) and then run a command that will generate the migrations. If you don't like the migration that was generated, you can modify it. You can customize up and down operations. There are also tools that will take an existing database and generate the Django model…

That's great initially, but problems definitely crop up at scale: * What happens when your company creates new systems that aren't Python/Django? You can either still shoehorn all migrations into Django models, or have multiple separate schema change processes/pipelines... both options are not good. * If someone makes an out-of-band schema change manually (either by accident or to do a rapid hotfix), you're no longer…

* I see your point on not standardizing on one framework. Generally when that has happened for me, it turns into a new service and it has its own database/tables/migration management. It does get quite annoying, for sure.

* I've seen enough things go wrong that on my teams I do not allow DDL to be executed outside of a controlled process that comes from code. But yeah, if that were to happen, it would annoying to figure out what was done and then try to re-model.

* With Django you can specify exact SQL to run. So you can break up operations into multiple smaller steps... canonical example is building a new column based on an old column. You first add the column with NULL. Then you populate in batches of ~10k records. Then you add on the constraints/indexes.

* I haven't used Django with sharding. It appears there are some posts about it, but it all appears to be community generated content and not part of the official docs.

All-in-all, I could see that at a large scale with very mature engineering organizations with lots of activity and complex operations that something like Django could fall short and a home-grown system like this may be beneficial, assuming it were reliable enough.

Re: Ask HN: How does your development team handle database migrations?

#102

Post by liyanchang »

barrkel wrote: post #88
liyanchang wrote: post #30

I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread. Database Schema as Code Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastr…

Data migrations? Denormalizing columns from one table to one or more child tables, possibly more than one relation away? Switching one set of fields in a single table to be in a different table via a relation, converting something from 1:1 to 1:n? The concept appeals to me, but it only seems to work for trivial migrations.

I’ll openly admit that we don’t have everything figured out. You’re absolutely right that currently, we constrain ourselves on what we migrate to admittedly simple migrations.

I think there’s a whole set of problems to be solved in this space and frankly, I’m as surprised as anyone that given how SQL is declarative, we use procedural code to do migrations so part of my post was hoping people would tell me what tool I should be using or how this approach fails over time. So your examples are really helpful for me as I think through if it’s possible to do automatically, workaround, or get by without.

It seems to me that we just lack the ability to express these transitions mathematically that can help us do them. And of those, there’s probably only a subset which are possible to do without taking downtime.

In particular, the class of migrations that you outlines are a combination of DDL and DML changes and also have quite a bit of code complexity to do without downtime. It’s definitely a current weakness.

Re: Ask HN: How does your development team handle database migrations?

#103

Post by liyanchang »

PaulMest wrote: post #96
nickserv wrote: post #81

Earlier quoted context omitted.

Django migrations can be problematic because they're meant to be sequential and have interdependencies. I've had problems merging multiple feature branches because of this, even though there are no code conflicts. A system like Saltstack or Puppet for databases would not have checked in migrations, these would be generated on the fly at deploy time. So you could very well have multiple state changes in a single run,…

This was helpful to think about, thanks. I've rarely encountered logical merge conflicts with migrations, but I could see it happening. I used to be on the SQL Server team at Microsoft and had some exposure to the customer support teams. So data integrity and eliminating any potential for errors was huge. So while I love the idea of migrations being generated on the fly from actual state in Production-System-5 to des…

Yeah. I’d love to see the academic paper with formalizations that help me understand the true scope of this problem. Your example is a great one that prompts many questions. Is it possible to travel directly to the commit o(1) or will the code have to calculate the diff of each commit and apply them one at a time o(n) and how much definition and dependency mapping humans need to do to have it work correctly?

Re: Ask HN: How does your development team handle database migrations?

#104

Post by nikisweeting »

PaulMest wrote: post #101
evanelias wrote: post #83

Earlier quoted context omitted.

That's great initially, but problems definitely crop up at scale: * What happens when your company creates new systems that aren't Python/Django? You can either still shoehorn all migrations into Django models, or have multiple separate schema change processes/pipelines... both options are not good. * If someone makes an out-of-band schema change manually (either by accident or to do a rapid hotfix), you're no longer…

* I see your point on not standardizing on one framework. Generally when that has happened for me, it turns into a new service and it has its own database/tables/migration management. It does get quite annoying, for sure. * I've seen enough things go wrong that on my teams I do not allow DDL to be executed outside of a controlled process that comes from code. But yeah, if that were to happen, it would annoying to fig…

Sharding is usually handled by patching the QuerySet / ObjectManager, how Citus handles it is a good example: http://docs.citusdata.com/en/v8.1/develop/migration_mt_djang...

Re: Ask HN: How does your development team handle database migrations?

#105

Post by erobbins »

evanelias wrote: post #71
abetlen wrote: post #63

Earlier quoted context omitted.

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.

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…

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)

Re: Ask HN: How does your development team handle database migrations?

#106

Post by scarejunba »

barrkel wrote: post #91
smoyer wrote: post #44

Earlier quoted context omitted.

This is actually a good place to ask important questions that will be closed by the moderators there.

In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.

Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.

Re: Ask HN: How does your development team handle database migrations?

#107

Post by Bjartr »

1337shadow wrote: post #21

It doesn't matter what tool you use, as long as you have automated migrations as part of the automated deployment process. A lot of the implementations look like this: create a migrations directory; add an initial migration script in it; make a migrate command to execute before service starts but after the backup. The migrate command recipe: create a migrations table in the db if it doesn't exist, otherwise fetch the…

We have an ant script that does almost exactly this

Re: Ask HN: How does your development team handle database migrations?

#108

Post by haihaibye »

nickserv wrote: post #75
Myrmornis wrote: post #49

Django. Django generates the schema migrations automatically from the changed table schema definitions that the developer used when testing their branch, and those rarely cause problems. Data migrations need to be tested against staging DBs with realistic data. But neither is really a major pain point: individual developers create and commit the migration files while preparing their branches for review.

Only major gotcha is when renaming fields: it'll drop the old field then create a new field, by default. You have to do the renaming by hand, trivial but can't be forgotten... Which is why you always test on staging first of course.

Makemigrations detects if the type is the same and asks you if you renamed it.

So, when changing columns do the type change and name change in separate migrations and you won't have to do it by hand.

Re: Ask HN: How does your development team handle database migrations?

#109

Post by evanelias »

erobbins wrote: post #105
evanelias wrote: post #71

Earlier quoted context omitted.

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…

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)

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.

Re: Ask HN: How does your development team handle database migrations?

#110

Post by sakopov »

At my .NET shop we use FluentMigrator [1] which allows you to just run up/down migrations. Our deployments are completely automated and, in all honesty, while this has been a great and a painless way to manage migrations there really is no silver bullet. Minor releases are dead simple. Larger releases require quite a bit of planning to make sure things are deployed in correct order and SQL updates are backwards-compatible (at least during the deployment window) so that we don't incur downtime or completely break things. The only time we ever had to roll back was when one of these large releases deployed a non-backwards-compatible SQL script which caused us a lot of bad data during 5 minute deployment window. So, this is usually a good motivating factor to try to deploy slim releases as frequently as possible. And if you do that you'll appreciate it when your DB migrations become boring and mundane things that just happen on auto-pilot.

[1] https://github.com/fluentmigrator/fluentmigrator

Post reply on HN