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

91–100 of 150 posts

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

#91

Post by barrkel »

smoyer wrote: post #44
freedomben wrote: post #35

Earlier quoted context omitted.

> HN is the new StackOverflow? Could you clarify what you mean by this?

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.

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

#92

Post by evanelias »

nickserv wrote: post #86
evanelias wrote: post #37

Earlier quoted context omitted.

I call this declarative schema management, since the repo declares the desired state, and the tooling knows how to reach this state. This concept is finally catching on lately, although some huge companies have already been doing it this way for quite some time. Facebook is a key example; they've managed their schema changes in a pure-SQL declarative fashion, company-wide, for nearly a decade. I'm developing a suite…

Curious, how do you deal with renaming fields or tables? This is a (minor) pain point for traditional migration systems.

Excellent question! The short answer is Skeema doesn't directly support renames yet. Renames are inherently more imperative than declarative, so they don't fit in well with the model. I've thought about handling them via tracking their history/state, but it would be hacky.

Two workarounds exist in Skeema currently:

* You can do the rename "out of band" (e.g. manually, rather than via `skeema push`), and then update your schema repo via `skeema pull`. This isn't ideal, but then again, table/col renames typically involve nasty code-deploy-order complexities to begin with (regardless of migration system): there's no way to rename something at the same exact instant that your new code goes live, and it's difficult/annoying to write code that can correctly interact with both names.

* For new empty tables, happily a rename is equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.

I've written a bit more about this at https://github.com/skeema/skeema/blob/master/doc/requirement... , as well as the bottom of https://www.skeema.io/blog/2019/01/18/declarative/ .

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

#93

Post by adwf »

jakswa wrote: post #13

Depends on the database, in my opinion. Each one has quirks to be mindful of, depending on the amount of data you're migrating/touching. For example, if you accidentally put a 'default' on a column when you add it to postgres, it will lock the entire table while it rewrites every row, inserting that default value. Another common postgres blunder is creating indexes on big tables without using 'concurrently'. This als…

Just for reference, as of PG11 it no longer locks or rewrites for default column creation.

It's one of the nice things about Postgres, they're always improving. A few years ago, concurrent index creation wasn't a thing either. Nor were "CREATE/DROP IF (NOT) EXIST" statements for various bits and pieces, but they just keep adding to it over time.

In the upcoming PG12 there is REINDEX CONCURRENTLY, so we'll finally be able to fix corrupt/bloated indexes on the fly without having to drop and recreate them.

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

#94

Post by Izkata »

nickserv wrote: post #81
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…

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,…

> 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.

They're actually a directed graph; this means a conflict wasn't handled on the branches that should have been, and would probably have been a problem regardless.

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

#95

Post by evanelias »

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.

A totally valid point, but I'd argue those should be handled by a separate tool or process. Data migrations tend to be fully programmatic; tools and frameworks can help reduce the code required, but cannot handle every possible case. (having performed numerous multi-billion-row data migrations, I learned this painfully first-hand...)

For simpler cases, where it may make sense to run a data migration immediately after a schema change, a good generic middle-ground may be configurable hook scripts. A declarative schema management system can then pass relevant info to the hook (which tables were changed, for example) and then the script can run any arbitrary row data diff/apply/migrate type of logic.

I do understand your point though; for relatively straightforward data migrations, an imperative system can capture these much more cleanly by just coupling them with the corresponding schema migration code.

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

#96

Post by PaulMest »

nickserv wrote: post #81
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…

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 desired state of commit 27a73e, I'm skeptical of it working that well in practice. Certain cases come to mind where there might be intermediate migrations from [full name] -> ([first name] [last name]) -> ([first initial] [last name]). The system would have to be smart enough to know A -> C may require A -> B -> C or prompt the engineering team for a better DML migration script.

Also, you will want there to be documentation about what was performed whether that is a migrations table that points to a .py file... or a .json output... or a log file.

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

#97

Post by liyanchang »

wvenable wrote: post #85
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…

Can that handle column renames? Most schema-to-schema diff tools can't tell the difference between a rename and a delete/add.

I’ll openly admit that we don’t have everything ironed out. In fact my next big project is to tackle derived columns (rename is a column where the transformation is the identity function).

It requires a bit more finesse and integration into our code base as it requires multiple deploys with code that knows how to handle both columns.

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

#99

Post by keithnz »

theclaw wrote: post #80

We use dbup[0]. Its philosophy[1] is that you should treat your DB changes like code changes in source control, and only perform up migrations. I agree with this. We previously spent a lot of time and effort writing down migrations that were never, ever used. If you need a down migration, take a backup before running your up migrations. We're a C# shop. Our DB migration scripts are simply named with a datestamp and a…

a lot of time writing down migrations? why was it so hard?

I use https://fluentmigrator.github.io/ and also used dbup..

I generally like the up/down better, and both the up and down is remarkably trivial to write generally, the downs are useful during development you might change your mind about the DB structure. Never used a down in production.

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

#100

Post by sheeshkebab »

- Flyway for relational databases, and

- flyway-like go based homegrown process for Cassandra (that includes support for executing go scripts for data migrations etc)

Above work pretty well - for many, many microservices and continuous deployments all the way to prod.

Post reply on HN