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

Atlas – Terraform but for Database Migrations

atlasgo.io

Post reply on HN
Print view View original

31–40 of 92 posts

Re: Atlas – Terraform but for Database Migrations

#31

Post by nhoughto »

atombender wrote: post #13

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks. For example, with Postgres some indexes have to be performed with "CREATE INDEX CONCURRENTLY" or "DROP INDEX CONCURRENTLY", which cannot be done inside a transaction. (Also, a "CREATE INDEX" like this can fail halfway an…

Yep is a big long tail to think about solving for, decades of features / idiosyncrasies in a complex domain.

Re: Atlas – Terraform but for Database Migrations

#32

Post by hinkley »

redact207 wrote: post #5

I'll never be comfortable with any tool for that automatically generates schema changes, as I'm just never sure at what point it decides to delete parts of my prod db. All of my migrations are dumb DDL statements rolled up into a version. I know exactly what the final state is as it gets run and used when integration testing, staging etc. It's boring but pretty bulletproof. I can rename a table and be confident it'll…

It doesn't even have to delete anything.

What happens when it gets 53% of the way through whatever it's doing at it errors out? Now wtf do I do?

Re: Atlas – Terraform but for Database Migrations

#33

Post by marcc »

I’m the creator of https://schemahero.io, another utility that does something similar. I love seeing innovation in this space and congrats on building this. Atlas looks really cool, I’m going to check it out.

Maybe we can chat about the challenges of building automated database schema migrations sometime!

Re: Atlas – Terraform but for Database Migrations

#34

Post by paulgb »

sverhagen wrote: post #29
parhamn wrote: post #24

Earlier quoted context omitted.

> I'll never be comfortable with any tool for that automatically generates schema changes, as I'm just never sure at what point it decides to delete parts of my prod db. To be fair, they analogized themselves to Terraform, which can go as far as deleting the database itself (and all the other infra that goes with it). As with anything a good dry-mode and a good process around review is how you minimize the risk.

That's why we try to set our databases to deletion_protection=true, and make it hard to delete them. But your point still largely stands!

Interestingly, that's the default for GCP but not AWS, even though both providers are developed by Hashicorp. I was pleasantly surprised by how difficult it was to (even intentionally) delete my GCP database when I was starting to use terraform.

Re: Atlas – Terraform but for Database Migrations

#35

Post by evanelias »

atombender wrote: post #13

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks. For example, with Postgres some indexes have to be performed with "CREATE INDEX CONCURRENTLY" or "DROP INDEX CONCURRENTLY", which cannot be done inside a transaction. (Also, a "CREATE INDEX" like this can fail halfway an…

> So while a tool like this might be good when you're just starting out, for a "real" app you want to avoid this sort of automation, because the tool is almost certainly not going to be smart enough.

Tools can be smart enough -- it just requires a lot of work and domain expertise. For example, Facebook has used declarative schema management company-wide for over a decade, to manage schema changes for what is likely the largest MySQL fleet in the world.

Then again, maybe this is an area where MySQL's DDL limitations actually makes tooling more possible: there's no transactional DDL in MySQL, so you can't get real clever with ordering anyway. And historically companies don't actually use ALTER TABLE with large MySQL tables; instead they use an external online schema change (OSC) tool which builds a shadow table and then swaps it. Since the OSC tool isn't actually running an ALTER directly on the original table, the exact ALTER is conceptually not necessary, if the OSC tool just takes the desired state as a CREATE instead (as fb-osc does).

That said, I agree with the sentiment in this subthread that it's a complex domain with a lot of idiosyncrasies. I tend to raise an eyebrow when DB tools use generic DSLs and try to handle a lot of different database vendors, as it's extremely rare (almost unheard of...) for a tool's authors to be deeply well-versed in large-scale production experience of many different DBMS. The various communities around Postgres, MySQL, SQL Server, and Oracle tend not to have a lot of overlap at the expert level.

Re: Atlas – Terraform but for Database Migrations

#36

Post by kodah »

throwdbaaway wrote: post #19
jrockway wrote: post #12

Earlier quoted context omitted.

I feel the same way. I'd prefer to program an SQL database in SQL. What I personally do these days is to write migrations as normal (sql files for the "up" and "down" steps), and then have a `go generate` step that creates a randomly-named database, applies each migrations, and dumps the schema into a file that gets checked in. A test assures that the generated content is up to date with the rest of the repository. T…

I thought the established wisdom is to make schema migration compatible with both the old app and the new app, whenever it is possible? E.g. you can safely add a nullable column, and it shouldn't trip up the old app nor the new app, unless you are using some crappy ORM that does "SELECT *", or you are on an older MySQL version that may take hours/days/weeks to rewrite the whole table just to add a nullable column. ht…

You can get by on that "wisdom" for a while, but eventually your DB will garner a significant size and performance impact as a result. That said, even on very active products that takes a while, so there's probably something to blending the idea of one large migration every so often and only making schema compatible changes along the way.

Re: Atlas – Terraform but for Database Migrations

#37

Post by jayd16 »

redact207 wrote: post #5

I'll never be comfortable with any tool for that automatically generates schema changes, as I'm just never sure at what point it decides to delete parts of my prod db. All of my migrations are dumb DDL statements rolled up into a version. I know exactly what the final state is as it gets run and used when integration testing, staging etc. It's boring but pretty bulletproof. I can rename a table and be confident it'll…

Its not that bullet proof. It still runs into issues with version control type conflicts made by multiple developers/branches. Something like FlywayDB can at least spot these problems but not prevent them.

Not sure if any of the migration tooling can handle multiple branches or unordered changes.

Re: Atlas – Terraform but for Database Migrations

#38

Post by satyrnein »

evanelias wrote: post #22
choward wrote: post #15

I wish people would explain why they created their project and what their pain points were with existing alternatives. All I can find is "Contrary to existing tools, Atlas intelligently plans schema migrations for you, based on your desired state." What's so bad about writing an explicit migrations using something like Flyway? I'm a fan of declarative configuration for the most part but it doesn't seem that beneficia…

Declarative schema management provides some nice properties that aren't present in imperative/migration-based tools. I'm the creator of a widely-used declarative tool for MySQL/MariaDB called Skeema, and I wrote a blog post summarizing some advantages a few years back: https://www.skeema.io/blog/2019/01/18/declarative/ You are correct that renames are problematic with declarative tools, but in production renames are…

I find Skeema pretty compelling, but I couldn't figure out one aspect:

Say I'm adding a gender column to my employees table. I see how Skeema would be an alternative to running DDL in a migration. But I would end up still needing a migration to backfill the data.

So I could use Skeema, but I still need migrations (we also occasionally fix data due to bugs, etc). At that point, we were less motivated to add an additional process.

Is that what Skeema users do, or is there some other approach, or maybe this isn't what I should be doing in the first place?

Re: Atlas – Terraform but for Database Migrations

#40

Post by tianzhou »

evanelias wrote: post #35
atombender wrote: post #13

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks. For example, with Postgres some indexes have to be performed with "CREATE INDEX CONCURRENTLY" or "DROP INDEX CONCURRENTLY", which cannot be done inside a transaction. (Also, a "CREATE INDEX" like this can fail halfway an…

> So while a tool like this might be good when you're just starting out, for a "real" app you want to avoid this sort of automation, because the tool is almost certainly not going to be smart enough. Tools can be smart enough -- it just requires a lot of work and domain expertise. For example, Facebook has used declarative schema management company-wide for over a decade, to manage schema changes for what is likely t…

Facebook can do that because they have a homogeneous environment using only MySQL. And they have a strict engineering discipline and strong DBA team to limit the allowed schema structure.

Not so much for other companies.

Disclaimer: I don't work for Facebook, but I used to work at Google and build its Cloud SQL service.

Post reply on HN