Database schema changes are hard (2017) • Hacker News Board

Re: Database schema changes are hard (2017)

#41

Post by vbezhenar » Sat, Mar 02, 2019, 9:46 AM UTC

Existenceblinks wrote: post #33

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

> But that's silly. Because Database Schema Changes are Not Hard. Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.

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.

Re: Database schema changes are hard (2017)

#42

Post by antpls » Sat, Mar 02, 2019, 9:56 AM UTC

> Fundamentally, a schema is a guarantee that your data conforms to the structure you intend.

My opinion :

The reality is that you never have enough information about how the data will be queried and connected together in the long run. A table structure is a model you built from a snapshot of your knowledge of the reality, but your knowledge changes over time and so should the model. As new information goes, some assumptions made before become obsolete, while new assumptions need to be made.

Even if a SQL relational database works OK for most of the business cases, they are not good at frequently evolving over time with new information or needs. They are also not that good if you have different models of the data that must exist in parallel.

I could see database schema as a specialized form of organizing knowledge, but it shouldn't be considered the best and only one.

Fundamentally, if you think you can fit the complexity of the reality in a bunch of relational tables, you are starting with the wrong assumptions already

Re: Database schema changes are hard (2017)

#43

Post by bfung » Sat, Mar 02, 2019, 9:59 AM UTC

The reason files with numbers with different changes are kept is because when there are more than a handful of devs working on the same schema, who has the right magic 1 step "Dev schema"?

Your migration tool is bad and you should feel bad.

Schema changes aren't hard. It's the mismatch of devs knowing how big a table is in production + the downtime caused by a data migration that is the hard problem. Data retention, partitioning, and other data archiving things are rarely the top of mind for apps until it's usually too late and "hard".

Re: Database schema changes are hard (2017)

#44

Post by [deleted] » Sat, Mar 02, 2019, 10:08 AM UTC

vbezhenar wrote: post #41

Existenceblinks wrote: post #33

Earlier quoted context omitted.

> But that's silly. Because Database Schema Changes are Not Hard. Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.

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.

[deleted]

Re: Database schema changes are hard (2017)

#45

Post by gnud » Sat, Mar 02, 2019, 10:12 AM UTC

vbezhenar wrote: post #41

Existenceblinks wrote: post #33

Earlier quoted context omitted.

> But that's silly. Because Database Schema Changes are Not Hard. Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.

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's not very hard.

If you have a big database (100s of GB), and can't have downtime, you can't grab a full table lock to rewrite all the records in one go. You'll have to do something like

  1) create the new table (easy)
  2) create a marker column on the old table saying 'data moved to new  table', set to false on all records
  3) deploy version of application that can use both schemas
  4) migrate all data to the new table, flipping the marker as you go.
  5) nce all records are migrated, deploy new version of application that only uses new schema
  6) drop marker and old columns

(there are multiple ways to do this, of course - this is one approach)

Now imagine that you have 4 or 5 applications using this database. (Some reporting application, some integration with an accounting system, some integration with a marketing tool, etc). And you must do a live migration.

That is _hard_.

Re: Database schema changes are hard (2017)

#46

Post by chousuke » Sat, Mar 02, 2019, 10:37 AM UTC

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.

That's just up to how you model your data. there's nothing that prevents you from using a single table with a JSON blob in an RDBMS either, but it's throwing away most of the benefits.

A NoSQL database might seem to make migrations easier, but I'm not convinced that it actually does.

Re: Database schema changes are hard (2017)

#47

Post by ddebernardy » Sat, Mar 02, 2019, 10:56 AM UTC

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 really end up needing to rewrite an enormous table and you want to minimize downtime while doing so.

Re: Database schema changes are hard (2017)

#48

Post by djrobstep » Sat, Mar 02, 2019, 11:15 AM UTC

bfung wrote: post #43

The reason files with numbers with different changes are kept is because when there are more than a handful of devs working on the same schema, who has the right magic 1 step "Dev schema"? Your migration tool is bad and you should feel bad. Schema changes aren't hard. It's the mismatch of devs knowing how big a table is in production + the downtime caused by a data migration that is the hard problem. Data retention,…

> who has the right magic 1 step "Dev schema"?

Hint: It's right there in the name. The developers.

> Schema changes aren't hard.

I'm glad you enjoy tedious manual work. I'd rather automate it.

Re: Database schema changes are hard (2017)

#49

Post by djrobstep » Sat, Mar 02, 2019, 11:17 AM UTC

emptysea wrote: post #11

Most of my migrations with Django are column renames, column adds, deletions, etc., which seem to fit nicely with this tool, but there are some cases where I want to do some more complex transformation which require running a function using Django's `RunPython()`[0]. For example, combining `firstName` and `lastName` columns into a single `name` column. How would these changes be accomplished? [0]: https://docs.django…

Just create a migration script that mixes (autogenerated) sql and the python you need.

Re: Database schema changes are hard (2017)

#50

Post by zepolen » Sat, Mar 02, 2019, 11:44 AM UTC

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.

If only there was some way to query the data from the old tables and insert them into the new ones...