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

Database schema changes are hard (2017)

djrobstep.com

Post reply on HN
Print view View original

51–60 of 93 posts

Re: Database schema changes are hard (2017)

#51

Post by BerislavLopac »

A very common problem I have seen in distributed applications is that the database is treated as an internal part of the application. As soon as you have a separate server running a database (basically most modern setups that don't use SQLite) you have a distributed system, and the database is an external dependency -- just like, say, Facebook/Google API you use for SSO. It becomes even more clear if your system has several independent services.

Once you accept this, the database schema becomes just yet another API contract to accept and respect, with all the same mechanisms to ensure compatibility -- versioning, integration tests etc.

Re: Database schema changes are hard (2017)

#52

Post by sametmax »

jasonkester wrote: post #26

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (…

That's assuming changing the schema is just about changing the layout of the database, which is definitly not true.

You have your code depending on it, documentation, constrainsts, replication, caching, db load, deployement systems, different envs and versions...

The SQL is the easy part and barely registers as an issue.

I see the same problem with SQL lovers rejecting ORMs as a dumb way to avoid using the right tool for the job.

But using an ORM is not about avoiding to write SQL. It's about all the rest: code introspection, data validation, documententation, having a common api, tooling, etc

Re: Database schema changes are hard (2017)

#56

Post by [deleted] »

speedplane wrote: post #39
chousuke wrote: post #37

Earlier quoted context omitted.

I don't really understand what is it about that makes RDBMS schema changes particularly harder in people's minds. All schema changes to your data can be hard or easy depending on what you do, regardless of whether that schema is implicit in the application or enforced by a database. If the schema change is additive, it's usually easy. On the other hand, if you drop data items that older versions of applications expec…

In an RDMS, a change in schema is hard because changing one table can affect many others. In a NOSQL database, there are far fewer connections between tables (or "objects"). There is less to wrap your head around.

[deleted]

Re: Database schema changes are hard (2017)

#57

Post by twic »

djrobstep wrote: post #14
jayd16 wrote: post #10

The reason you keep a migration chain is so you have a clear path from anything in the wild to the latest. Unless I'm misunderstanding, throwing this away means you need to migrate some unknown permutation to latest. The hardest part of the migration is migrating the data and as far as I can tell that is glossed over. Why is throwing away the iterative migration version useful exactly?

In many (most?) cases people have a single production environment. If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to. Moving data about is always going to be a manual process. This approach just helps you test it better.

> In many (most?) cases people have a single production environment.

No idea why this was downvoted, because this is a key truth.

You need to be able to migrate the production database to the latest version without losing any data.

You need to be able to replace any non-production database with the same schema as production. Ideally, with a (sanitised, subsetted, etc) copy of its data.

You don't need to be able to do anything else.

Re: Database schema changes are hard (2017)

#58

Post by pytester »

sametmax wrote: post #52
jasonkester wrote: post #26

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (…

That's assuming changing the schema is just about changing the layout of the database, which is definitly not true. You have your code depending on it, documentation, constrainsts, replication, caching, db load, deployement systems, different envs and versions... The SQL is the easy part and barely registers as an issue. I see the same problem with SQL lovers rejecting ORMs as a dumb way to avoid using the right tool…

ORM are frameworks that exist mainly as a way to avoid writing and maintaining thousands of lines of boilerplate.

Like most frameworks (i.e. packages that force you to write your code to conform to their patterns) a really good one confers a massive boost in productivity while a really bad one is horrifying to work - it fails in bizarre unexplainable ways and straitjackets your development.

IME, passionate rejection of ORMs predominantly comes from people who have been scarred by experience with really bad ORMs.

Re: Database schema changes are hard (2017)

#59

Post by Jupe »

The problem with database migrations are the rows, not the columns. I think I've written similar "column" tool like this for just about every DB project I've started, or write one for projects I've inherited/stepped into...

The rows (i.e. the actual data) is where the problem comes in. Just a few examples:

* Splitting overloaded fields ("5qt" => 5, "qt")

* Datatype changes ("ID" => ID)

* PK size/type change (NUMBER(10) => NUMBER(15))

* "Just" add a column (PK,Col1 => PK, Col1, Col2)... easy until the identifying column is no longer enough!

* Alter a PK (PK => PK1, PK2) - now cascade that through the Referential Integrity chain(s)

* Change the "Type" column for every row based on the output of some external API call

* "Rollback" part of the schema, with examples of the above applied

* Implement a 'vertical split' on a very wide table, and associated RI cascades/updates

* The infamous "was_migrated" flag, or "row_version" solutions that seemed like a good idea at the time

* etc.

And the next level of complexity comes with large-scale systems; doing these types of changes while the DB is online is even more complicated, and sometimes not possible or not worth the development effort.

These are not intractable problems, and a tool to help with these kinds of issues would be quite valuable; that's what I was hoping to see here.

[Edited; formatting]

Re: Database schema changes are hard (2017)

#60

Post by dwheeler »

This is interesting, though it really shouldn't be specific to one RDBMS (and I like PostgreSQL).

To be honest, I don't find the status quo particularly difficult to deal with. A directory with lots of little files doesn't take up much storage, and nobody cares about the space anyway. That said, improvements are always welcome.

I like how it looks at the diff and figures out simple schema changes. However, in my experience, the simple changes are no big deal. The more interesting challenge in migrations is when semantics change and you need to generate data from existing data. Simple diff cannot determine that, that needs to be provided by some set of queries, which easily fits in a migration file.

Is there a way to integrate more complex changes and dataset manipulations when needed?

Post reply on HN