I've been protected from this by the Django migrations system for over a decade at this point (that landed in Sep 2014, but predecessor South was usable for quite a few years before that point).
But if you don't have a good migrations system in place, changing the schema of your relational database (and keeping those changes synced across development machines, staging, production etc) is enormously painful - even more so for applications with a lot of production traffic.
I've seen many situations where engineers have made sub-par design decisions in order to avoid having to add or modify a column on a large, existing table for a production system.
NoSQL would still have migration issues as the types change over time, much like the table schemas. You would need to be able to read/write older objects stored, or transform them all to the newer object schema in one go.
I remember when Mongo DB first came out, it seemed the most liked feature was that you could just JSON.stringify and JSON.parse things into and out of the db.
I personally prefer SQL tables, as the act of designing the tables and their relations seems to half way solve a problem, and removes the issue of "which tree structure should this value live in, how do I copy it to both of these types?".
Thanks for your work!
There are multiple reasons to use NoSQL and non of them is this one.
Having said that - cool, I like relational databases and love my multi-joins going faster.
I have worked with two NoSQL databases and both have support for basic joins. Not that it's needed very often.
https://www.mongodb.com/docs/manual/reference/operator/aggre...
The problem is basically:
- Say you have a query filtering on 2 fields, e.g. `WHERE x = ... AND y = ...`. If you have separate indexes on x and y, if both filters return a lot of records, you'll have perf problems. Internally, the DB needs to fetch all of the records where `x = ...` AND all of the records where `y = ...`, them and then combine in memory. This is very, very slow with large data volumes, even if there are very few records that satisfy the combo (e.g. lots of records where `x = ...`, lots of records where `y = ...`, but few records where both `x = ... AND y = ...`)
- The same is true for many related problems, like filtering on 1 index, then sorting on another (e.g. the common `WHERE x = ... ORDER BY y LIMIT ...` queries)
- If your data is all in a single table/collection/whatever, then you can fix these performance issues with a compound index on (x,y), which allows you to efficiently filter/sort on the combination of x and y, without having to fetch huge intermediate record sets. But if your data is in different tables/collections/whatever, I'm not aware of any popular DBs that allow the creation of cross-table compound indexes, so that's out
NoSQL's hierarchical, join-free approach means you CAN create that compound index most of the time, while with relational DBs, you much more often can't. With NoSQL, it's much more likely that when these slow queries emerge, fields x and y are already in the same "collection". Traditionally, with relational DBs, people have solved these perf issues via denormalizing - if fields x and y live in different tables, you decide to keep a copy of field y on table x as well, to allow you to speed up the query with a compound (x,y) index. Works, but denormalizing sucks. NoSQL DBs are another approach, basically eliminating joins with more hierarchical data, but that has issues too (including, very often, requiring you to denormalize!).
"Worst-case optimal joins" are a way to have relational DBs solve this for you, without needing to resort to denormalization and compound indexes, which is very exciting! Instead of saying "fetch all the data where x = ..., and all the data where y = ..., then find the intersection in memory", they try hard to avoid this. e.g. intelligently fetch a little from x, a little from y, see if that's enough. It's a great concept, but only recently seeing much serious attention, and most big relational DBs don't support them yet.
There are two main reasons it continues to thrive: most developers who entered the industry since ~2010 have not used anything else, and the traditional SQL databases are painful to set up in multi-region cloud topologies without a dedicated DBA team.
(Excluding graph database use-cases, of course.)
I guess since NoSQL isn't new anymore, maybe the database choice at startups is sensible again - as they try something new in another area?
Database rows simply aren't objects. You have to squint so hard you risk retinal detachment before the two are even a little bit the same.
Other database paradigms exist, and have existed for a long time. File systems spring to mind. The Windows registry is another.
A case can at least be constructed for using something other than a relational database if all you want is to persist objects in a consistent manner.
