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

61–70 of 93 posts

Re: Database schema changes are hard (2017)

#61

Post by vinceguidry »

gnud wrote: post #45
vbezhenar wrote: post #41

Earlier quoted context omitted.

Why is it hard? Most of changes are atomical and either in background or instant. What change require downtime? I would argue that software change without downtime is harder and basically not solved problem for most environments unless you're using cluster of machines.

Live changes are hard because they requires precise planning, and must often be done in several steps. Imagine you're extracting some data to a new table in order to support 1:N instead of 1:1 of something. For example, introducing an address table, instead of storing the address directly in the customer table. If you can have downtime, and have a small database, this can be done with one change and one deploy - that…

I've been wanting a 12factor-type methodological doctrine for data for a long time. Ideally, one that can fully account for how, when, and why to start introducing different kinds of databases and when you need to start exploring those options.

But I'd be happy with a lexicon and fundamental strategy for managing data that allows you do relax and rely on the fundamental primitives, like 12 factor does of overall application architecture.

Re: Database schema changes are hard (2017)

#62

Post by wolco »

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.

A nosql database schema change would force you to update every document requiring the change.

I would say rdms schemas are straight forward. Remove constrants, alter tables/move data, add constrants.

Changing a nosql schema going forward is simple. Adding new columns is equally simple. Deleting columns: rdms is much easier. Updating rdms is simplier.

Re: Database schema changes are hard (2017)

#63

Post by jyounker »

ddebernardy wrote: post #47

Interesting tool, but anecdotally the difficult part about database migrations has very little to do with the actual schema changes. Rather, it has to do with: 1. Ensuring the app works properly with the old schema as well as the new one -- not hard by itself, but requires rigor. 2. Minimizing locking during the migration itself. That right there actually is the much trickier part of migrations, because sometimes you…

^^^^ This. A thousand times, this. ^^^^^

As a supporting example of the arguments, I recently had to replace a subtly corrupted table in production with one rebuilt from trusted sources. The table was about 160k rows and core to the operation of the production system I work on.

The initial "easy" approach to replacing this table took many hours to execute, and locked the table for much of that time. It took a day or two of effort, but the final series of SQL operations pushed the total run time down to less than four minutes, was recoverable from any intermediate state, and pushed the locking time down to three seconds.

The complicated part was not the final state of the database, but finding a path to reach that state that fit within the operational constraints.

Re: Database schema changes are hard (2017)

#64

Post by wolco »

jjeaff wrote: post #13

So how do you deal with it when you actually need to "migrate" data? Getting a correct schema is only part of the migration process. Many times a refactoring requires a new table or field to be populated with data from the old table or field. For example, a single table has a one to one relationship that we must select from to insert data into a newly created table so we can have a one to many relationship.

I'm confused. Wouldn't this type of statement cover those cases?

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Re: Database schema changes are hard (2017)

#65

Post by akx »

twic wrote: post #57
djrobstep wrote: post #14

Earlier quoted context omitted.

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…

You're only thinking about SaaSy things there.

What about on-premises or just open-source software, where you any number of any version of your software may be installed around the universe, and those users need to upgrade to a newer version?

Re: Database schema changes are hard (2017)

#66

Post by paulryanrogers »

wolco wrote: post #62
speedplane wrote: post #39

Earlier quoted context omitted.

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.

A nosql database schema change would force you to update every document requiring the change. I would say rdms schemas are straight forward. Remove constrants, alter tables/move data, add constrants. Changing a nosql schema going forward is simple. Adding new columns is equally simple. Deleting columns: rdms is much easier. Updating rdms is simplier.

With RDBMS the integrity constraints can make gradually changes to large data sets more difficult. It often means a lot of downtime or the code must tolerate old and new structures for a while. With NoSQL the constraints are probably already in the application layer.

Re: Database schema changes are hard (2017)

#67

Post by postgrescompare »

bartread wrote: post #32
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 (…

Even if you do know SQL syntax using the tool to make the change script for you can save quite a bit of time, especially if you've made a bunch of changes which can happen if you've added new functionality. DISCLAIMER: I worked at Redgate, who make a tool called SQL Compare that works with SQL Server, for nearly a decade. Since this article is about PostgreSQL you should check out Postgres Compare, built by my friend…

Thanks Bart!

I know for PostgresCompare a lot of its utility is in finding the differences. Providing clarity into what has changed while one was building a feature.

A lot of users still write the SQL themselves. They let the tool figure out the easy stuff, get the dependencies correct, and they make a few edits here or there.

Re: Database schema changes are hard (2017)

#69

Post by paulryanrogers »

gnud wrote: post #45
vbezhenar wrote: post #41

Earlier quoted context omitted.

Why is it hard? Most of changes are atomical and either in background or instant. What change require downtime? I would argue that software change without downtime is harder and basically not solved problem for most environments unless you're using cluster of machines.

Live changes are hard because they requires precise planning, and must often be done in several steps. Imagine you're extracting some data to a new table in order to support 1:N instead of 1:1 of something. For example, introducing an address table, instead of storing the address directly in the customer table. If you can have downtime, and have a small database, this can be done with one change and one deploy - that…

Queued jobs which can auto-retry will help, of they can detect incompatible changes in the data structure. Of course that'll produce some false positives in the error logs. But it's a small price to pay IMO

Re: Database schema changes are hard (2017)

#70

Post by lenticular »

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…

I've had bad luck with ORMs in general, mainly because they fall apart whenever I need to do something complicated. The abstraction is just too leaky.

However, I've had really good luck with Doobie, a purely functional SQL library in Scala. The big difference is that queries can be manipulated as ordinary pure values, which really lets you do some cool stuff. I actually used these features to significantly speed up a schema change in a large database I was working on.

https://tpolecat.github.io/doobie/docs/01-Introduction.html

Post reply on HN