They won their way to becoming a standard, for good reason.
You can also opt our of rigid schema by using JSON columns. Which I generally promote as a new best practice when the database is being uses as a dumb store for a smart application. It comes down to who should be the source of truth for the schema.
It's a decent language for ad-hoc querying by humans; the problem is it's the only interface to the database that you get. It was never designed for machine use; in a modern RDBMS, 3/4 of the time to execute a pkey lookup is spent parsing the SQL string. Yes, prepared statements can help in some cases, but they come with their own overheads that make them difficult to use safely in a large system.
> You can also opt our of rigid schema by using JSON columns.
You can, but usually in a database-specific way, and support for that in drivers and especially at the ORM level is pretty spotty.
Network latency is the real performance drag, not string parsing.
I think JSON support will improve, but SQLAlchemy for example is ok with a common JSON interface over mySQL and Postgres. I am sure this will resolve itself in time, its just a bit new for SQL.
In principle yes; in practice you can expect to find full-featured drivers in all major languages, and anything higher-level that claims support for Mongo will also have support. Certainly even the most basic Mongo drivers will let you have things like collection columns.
> Network latency is the real performance drag, not string parsing.
Depends what kind of network, if any, is between the two endpoints. But the performance aspect is just illustrative of what a poor format for expressing machine-level queries it is.
This works extremely well with Postgres. The MySQL one is I think just saving it as a text string, which may become a killer.
These times are on the order of sub-milliseconds; about 0.1ms with PostgreSQL on my modest laptop with ~30 million row table.
You make it sound like it's some sort of horrible performance hog, but 0.1ms for parsing a dynamic language really isn't that bad. Actually fetching the row takes about 1ms (without anything cached, faster otherwise), so that's hardly "3/4th" either. With cache it's about 0.1ms, which is about half.
But in reality most queries are more complex, and the parsing time becomes negligible; even for a fairly complex query it's about 0.6ms, which is hardly worth thinking about if you consider that the query execution takes about 60ms.
Depends on your usage pattern. If you're actually doing a bunch of different ad-hoc aggregations (which is what SQL was designed for) then yes, query parse time is irrelevant. If you're using an RDBMS as a glorified key-value store (which is what most web applications tend to do) then it's very possible for pkey lookups to be the majority of your queries. (My point isn't really about performance, it's about SQL not being designed for that use style in general).
Yes, because the database-level builtins are too restricted; pretty much the only behaviour you can get out of them is "reject the change and throw away the data" which is almost never what you want, and requires you to keep the data around on the client side and hope that all clients implement recovery consistently. Think about the user-facing behaviour that you want when e.g. one user tries to join a group at the same time as another user deletes the group. It ends up being easier to implement the right thing without using database-level transactions and foreign keys.
You join first and the group gets deleted or the group gets deleted and you can't join. I don't see the issue.
There is a reason relational databases are the standard. It's one of the rare things in computer science actually based on mathematics.
What point are you trying to make? The point of that article was that the rhetoric about "simplicity" is unproductive, no-one makes a deliberate choice to use something they think is complex. But I didn't make that kind of argument.
> There is a reason relational databases are the standard. It's one of the rare things in computer science actually based on mathematics.
Relational databases are a very finely engineered hammer. But not everything is a nail.
> difficulty deploying schema changes
Definitely agreed, even with tools like Flyway, Liquibase, dbmate or most of the framework provided options (such as Active Record Migrations for Rails and Doctrine for Symfony), most migrations still end up feeling brittle, because you oftentimes do things like renaming a column, or processing some data into a new format, or cleaning up old data etc. Well, you want to do that anyways, but then you realize that instead of simply renaming a column, you'll probably do a rolling migration for the apps that use the DB, therefore you need to create a new column that the app will write data into, then migrate all of the app instances to the new version and then clean up the old column, god forbid validations use the wrong column while this is going on. I don't think it's possible to work around problems like this with technologies like MongoDB either, since then dealing with missing data in an "old" version of a document would still be annoying. I don't know of any good solutions to address how data evolves over time, regardless of technology.
> difficulty sharding
Definitely agreed, in general it seems like most DBMS mostly scale vertically better than they do horizontally. For example, master-slave replication seems doable, but once you want to do master-master replication, you run into problems with latency and data consistency. There are some solutions like TiDB which attempt to give you a distributed database in a transparent way, without making you worry about its inner workings, but that only works until suddenly it doesn't. It seems like this problem affects most distributed systems and i'm not sure how to address it, short of making each new data entry reference the previous state, like CouchDB does with revisions ( https://docs.couchdb.org/en/stable/intro/api.html#revisions ) and even that won't always help.
> an awkward square-table model and a terrible query language
Partially agreed, SQL is pretty reasonable for what it does, despite its dialects being somewhat inconsistent, many of the procedural extensions being clunky and most of the in-database processing heavy systems that i've encountered being a nightmare from a debugging and logging perspective, though i guess that's mostly the fault of the tooling surrounding them. Discoverability can be a big problem if OTLT and EAV are heavily used ( https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-bi... ) and foreign keys are not used. Window functions, analytical functions, partitioning and other functionality feels like it's implemented in unintuitive ways in some systems, but that could also be a question of familiarity and a steep learning curve.
> if you make the mistake of trying to use the transactional functionality that's the one actual selling point of those datastores then you're practically guaranteed to deadlock yourself in production at some point during your growth process
Partially agreed, it can definitely happen, but being able to revert bad changes to the data and even test them in the first place sometimes feels like a godsend. Well, there should always be a local instance that's safe to break, but in practice that doesn't really come true often.
NoSQL databases do nothing special to achieve horizontal scaling. They simply don't support transactions or atomic operations across documents. If that's what you want you can just choose an RDBMS with that behavior.
IME the best way to do it is to build your system on stream transformation (i.e. Kafka) and then you can just produce the new representation in parallel, wait for it to catch up, migrate the readers over gradually and then eventually stop producing the old representation. That tends to be what you end up doing with a traditional RDBMS too, but if you're using something like Kafka then the pieces that you use are more normal parts of your workflow so it's less error-prone.
> It seems like this problem affects most distributed systems and i'm not sure how to address it, short of making each new data entry reference the previous state, like CouchDB does with revisions ( https://docs.couchdb.org/en/stable/intro/api.html#revisions ) and even that won't always help.
There are two approaches that I've known to work: 1. actual multiple concurrent versions as you say, with vector clocks or equivalent, forcing the clients to resolve conflicts if you're not using CRDTs - Riak was the best version of this approach, 2. having a clear shard key and allowing each partition to have its own "owner", making it clear what you do and don't guarantee across partitions - e.g. Kafka.
> Partially agreed, SQL is pretty reasonable for what it does, despite its dialects being somewhat inconsistent, many of the procedural extensions being clunky and most of the in-database processing heavy systems that i've encountered being a nightmare from a debugging and logging perspective, though i guess that's mostly the fault of the tooling surrounding them.
I wasn't talking about the fancy analytics so much as just the basic data model - e.g. having a collection-valued column is just way harder than it should be. Everything being nullable everywhere is also a significant pain.
1. Curiosity and interest in new tech, so they are keeping abreast of developments and can think of modern best practices where useful.
2. Ability to ignore the new/sexy bits when it comes to implementation decisions, because the trade-off for stability/speed to implement is necessary in a growing company.
CouchDB is really nice and has cool features, but for people used to SQL, its a very different beast. Querying is surprising in many ways (less so if you're used to MongoDB I guess).
As you can guess, joins across documents and the like are not really great... and often necessary for relational data.
The correct approach, assuming that we'd want to continue with it would be to denormalize the data much further (which we did not do so far), and perform data reconciliation...
The offline-first case did sound convincing enough to me in the beginning, because we were targeting high level executives, which probably travel a lot more often than others, and still want to be able to work.
But the world ain't static.. It's a different story in post-covid world :p
The only valid reasons to choose non-relational databases are scalability and performance.
Agree 200%. And people seem to get a wrong idea of where this argument starts making sense. With the right indexes, I can easily handle 200req/s on my last SaaS running on a $50/m PostgeSQL.
It's also significantly easier to move from a standardized relational model to a document DB than the opposite.
Checkout the DocumentStore library: MartenDb
It's documentation makes it pretty clear what postgress can do concerning this and event sourcing
If you prefer nosql, consider something like Hasura which reveals a graph on top of a Postgres dB. I’m not affiliated with either but it’s important to not make tech decisions based on what a developer hears others like to use.