NoSQL: The Baby and the Bathwater
brooker.co.za
brooker.co.za
Add all that up and NoSQL should be fine. But then the actual NoSQL databases let people throw out all the concepts that make SQL so sticky. Schemaless databases are in the same class as goto statements - there are people I trust to use them to do amazing things. The projects I inherit to maintain are never written by those people. Transactions and consistency guarantees are necessary to get a group of programmers together writing a reliable application. The relational model is the best idea to come out of database research.
I wish NoSQL was a postgres extension. Instead we get MongoDB advocates. Bless them, but PostgreNoSQL would be so much better for all the use cases I have than MongoBD.
Arguably psql does offer a NoSQL-like experience with JSON columns.
I hope not. JSON columns purge all the good things a relational database offers and keeps the SQL. It is the worst of every option.
That really should be the opposite of the NoSQL experience. Disbarred by the fact that SQL is involved.
The unfortunate naming leads to this kind of confusion. Probably a technology should never be named for what it is not.
It malappropriated in 2009.
And for some of us MongoDB is a better option than PostgreSQL.
Many of us simply can't rely on scalability and high availability being something that isn't part of the core product.
The reason being that Mongo is written in C++ with lots of RAII and will simply die on a memory allocation failure. Postgres won't. It'll keep running in many low memory scenarios and the operation will error out.
When I first heard about NoSQL I thought some databases were finally introducing some replacement for abominable SQL. But no, they were just creating schemaless databases.
SQL is relational algebra. Sure you can write it some other way. Lots of projects tried, none succeeded. Wonder why is that?
End of the day - if you just learn the syntax, the hard part will be in the logic. As it should be.
In case you aren't: I didn't state my argument at all. Unless you dug through my comment history to find my complaints. This is precisely the kind of nonsense you get in response to even stating that you dislike the language.
> SQL is relational algebra. Sure you can write it some other way.
The same applies to general purpose programming languages, yet they have improved immensely since COBOL.
> Lots of projects tried, none succeeded. Wonder why is that?
Because of inertia, the mixed userbase of SQL, and ORMs and things like LINQ. The fact that most projects avoid direct use of SQL when possible is telling enough on its own.
It’s not by the way, there’s an entire segment of the esolang space for which it’s the goal (starting with the canonical example that is INTERCAL).
But ignoring esolangs, there are lots of very unpleasant programming languages out there. M/MUMPS is a well known one (especially with “legacy” coding styles, or so I gather). I also consider XSLT to be abhorrent, especially given the nice underlying conceptual idea (not entirely dissimilar to SQL really, just worse).
About SQL, an idea I saw surface recently in a related discussion was how nice it’d be if databases could expose the data model interaction and allow building on that directly: most every SQL database compiles the query into some sort of bytecode (combining direct translation and planner information) to actually run it on its storage layer, some databases allow peeking at the bytecode (sqlite actually prints the bytecode as part of its EXPLAIN output) but I don’t know that any allows bytecode input.
TBF the bytecode is very much considered an internal detail, it can change a lot between versions and (most importantly) tends to be more or less completely unchecked, it relies on the compiler generating it being correct (not unlike cpython for instance).
https://news.ycombinator.com/item?id=34181319
Also: https://github.com/ajnsit/languages-that-compile-to-sql
With the lens that SQL is a DSL for set theory and set transformation (which it is), composability takes the form of views, temp tables, and CTEs. Not what you're looking for? That's fine. But they 100% make SQL composable.
I take issue with saying it's inconsistent as well. Are there warts? Sure! Like any language. But fundamentally inconsistent from the lens of DSL for set theory and transformation? No.
Folks keep trying to make relational system access like their favorite programming language, and it's not going to happen. Are folks' favorite general purpose programming languages 4th gen languages with an emphasis on set theory and transformation? No? Then stop trying to shoe horn it on! It only leads to frustration. Embrace the set theory and 90% of SQL's issues don't register as issues anymore.
As for NoSQL like MongoDB as a Postgres extension, that's what jsonb and its related operators and functions are for. Seriously. to_jsonb(…) and jsonb_populate_recordset(…) are seriously under-appreciated. They're like rocket fuel for JSON processing. Of course you're also welcome to keep a jsonb column lying around to keep things "schemaless", but experience has shown that defining your data (with types) rather than allowing free-form blobs in your database is better in the long term. You obviously see this already by your trust statement with regard to coworkers. "Better" meaning more performant and easier to maintain.
Just like thinking "functionally" takes training and practice to grok when you've been object-oriented all your life, that "set theory" takes training and practice as well. And it's so worth it. Feels like a goddamn superpower sometimes.
Many of the so-called NoSQL ones like MongoDB or Cassandra can support schemas, transactions, strong consistency, joins, secondary indexes etc. And SQL databases like PostgreSQL support schema-less data structures.
And with Presto, Spark SQL etc you can use SQL with almost any data store.
Our team had a lot of trouble trying to map highly relational data to a noSQL database (mongo)
It could have been a failing of our team, but I also think it just made our lives way harder. We’re on Postgres now and a lot of issues have faded away.
We had a small-ish application that was originally built in top of MongoDB. Once it made it into production and started to see some success, it became quickly apparent that the schemaless design caused problems that an RDBMS would have solved.
It was decided fairly quickly to remove the MongoDB underpinnings and migrate everything to Postgres. It was the right call, and the final nail in any remaining affection — and interest — I had for NoSQL stores.
Then there are a few domains that NEED other solutions. In terms of CPU cycles these may be consuming more resources. These are operating systems, game engines, ML libraries, search engines, social media platforms and huge webshops like Amazon.
But to store all data in Spark or MongoDB just because that's what <insert Cargo Cult idol> is doing makes about as much sense at programming everything in C/C++.
That's not a sufficient argument against adopting SQL for something, but other solutions are certainly _not_ just SQL with extra steps. Not everything maps nicely onto flat tables with predefined slots.
TL;DR the ISO standards committees behind SQL are working on bring graph query languages and SQL databases closer together.
Obviously there is work to be done at the storage/query planning layer after that, but I’m hopeful once the surface exists more widely that will drive more work in those areas.
For a newer project, where we basically use one object, I've chosen a NoSQL database. Get the object, edit it in the browser, put it back. Done. No need to update relations, ORMs or any of that. But: that won't fly for more complex projects.
So I agree: pick the right tool for the job.
Have a lot of indexes on a collection? Mongo will eat 5-10ms of CPU time per query even with cached query plan stats just to start executing the query. So no different than PG here.
What you're getting at is Joins, but I haven't seen a company that didn't end up doing joins with Mongo at some point. Or, they do it in the app layer, in Java/Node/PHP which requires more CPU than it would in the DB.
Also, wanna store an array in a document in Mongo? Every time you add to that array, the whole array is replicated to your secondaries. That eats a lot of CPU.
Anyway, most systems that last more than a couple of years tend to change over time, meaning the schema is likely to need extensions.
A lot of your suggestions are actually exactly the same for SQL based databases. If your schema is not fit for the task at hand, it can slow it down by an order of magnitude, and the process of changing schema is also similar.
Though, i would think, a properly designed SQL database would need such full schema refactoring less often, since adding a few tables within the same structure is easier.
It sound like you're describing use cases with greater data volumes than I usually use SQL for. (Mostly Data Science use cases, where larger datasets typically end up in Spark, or similar)
My experience is that SQL based systems work best up to a few 100 millions of records in the largest tables (a few billion at most), and with transactions per second is less than about 10000. Around those volumes is where SQL start to get really expensive.
And often SQL is used for use cases where number of records per table of less than 10 million and transactions per second in the low hundreds or lower.
But I'm probably biased in the opposite direction that you are. For me, performance usually means efficient joins. Which means that even if I'm leaving RDBMS's behind, I still use SQL where I can (such as in Spark).
NoSQL DBs take large volumes of data, little CPU, and almost no RAM
SQL DBs take lower volumes of data, loads of CPU and RAM
Storage is cheap, CPU is expensive, hence NoSQL is cheap. Even when your project is not in the 100 of milions records this can be significant because you then are able to offer a cheaper product than the competition's
This is also a common approach for SQL based databases when low latency is needed for common queries.
> SQL DBs take lower volumes of data, loads of CPU and RAM
That depends on the schema and the queries being run on them. Large amounts of CPU is really only needed if there are either 1000s of queries per second or large joins. RAM is very useful for hash joins with medium sized tables (up to a few millions of records). It has some utility for caching indexes or tables that need lower latency than the IO can provide, but that's similar to most NoSQL that I'm familiar with.
Most RDBMS's also come with ACID support, which has a significant cost (especially when writing). That has little to do with the SQL language, though. Spark tends to run on SQL without paying those costs. (More about this in the OA)
> Even when your project is not in the 100 of milions records this can be significant because you then are able to offer a cheaper product than the competition's
For small databases (<100 million records in the largest tables), SQL can be quite affordable, especially with pragmatic schema designs. I was working on SQL based systems more than 20 years ago with tables with several billion records on quite tiny hardware (by today's standards). 100 million is nothin by comparison.
For large tables (100 million to 10 billion records), good schema design and well written queries may be needed for SQL to perform, but that's not that different from what you're saying about NoSQL. Still, as you approach the upper end of this range, the compromises in needed with regards to denormalization, slack transaction management, etc may come at the cost of eliminating many of the advantages of traditional RDBMS's (such as consistency enforced by the schema definition through normalization and integrity constraints).
More than 10 billion, and traditional RDBMs start to break down, of course (you may need a cluster, and you may need to use sharding or similar methodologies from NoSQL / Big Data or similar paradigms, even if technically still on a RDBMS).
For small-medium sized databases (<100 million records/table), and especially for the small (<10 million records/table) I find that you often win back some of the (very moderate) extra costs simply from the consistency enforcement features. (ACID compliance for multi-table transactions, data normalization with referential integrity enforced by foreign key constraints, and so on.)
Btw, I'm not saying that other kinds of databases don't have their place, especially for various types of unstructured data, document oriented data or situations with extreme volumes or throughput requirements. 20 years ago RDBMS's were certainly overused. But 5-10 years ago, the pendulum had swung a bit in the other direction, imo.
A lot of devs that left college around 2010 seems to have jumped on NoSQL databases less because of their strengths than because of how they enabled the devs to trivially persist object oriented data structures with a line or two of code, and because they'd never learned how to use traditional RDBMS's properly.
This is fine as long as they don't need the kind of data consistency features provided by relational data models, but when consistency is needed, it tends to cause unnecessary problems. (Especially as a system ages, dev teams and application logic evolve.)
The reason I started my previous message with "That's interesting", is that your approach to NoSQL clearly shows you have a mature approach to NoSQL, as opposed to those who consider it a silver bullet that makes all complexity go away.
Anyway, it seems that a good design, suitable both to the problems at hand and the technology chosen/available is a universal in the field. Usually more important than specific tech choices.
I get that many devs are using NoSQL in a careless way that produces horrors. I just don't think that is NoSQL's fault, hence I dislike comments critizicing NoSQL DBs. I am an efficiency (mostly in $ terms) freak, so I really love how NoSQL avoids the pitfalls of SQL (CPU and RAM usage - plus licenses if paid). That makes my take subjective
I maintain clusters with 50+ machines with 128gb+ of RAM...
Is it? Most of AWS runs on NoSQL databases, and continues to ship new features that do not fit into existing schema. This assertion then is clearly incorrect.
Anyway this is a weird argument. Ideally you get your schema right from the get go no matter the DB, of course things will be easier. But we don't. That's why we have migrations. Also, what's right today, might not be after acquiring Jonny Big Corp.
The whole point of NoSQL is to dump documents into it, of varying structure, and query/index them as-needed.
Computer Science research seems to either be fully academic, where research into practical solutions doesn't happen, or it's limited to private companies trying to solve one business problem. Very little actual advancement of the state of the art, or solving of long standing limitations.
You cannot have a live service that is interrupted by database modifications.
My solution is to use JSON.
But the most important feature to add is HTTP as transport and async-to-async (both client and server needs to only use a thread when they are doing work, zero idling): To scale a distributed database across continents:
2000 line distributed DB: http://root.rupy.se
It's certainly the most important feature to KEEP for others.
Schemas allow you to keep the more critical business relationships consistent. For variable data elements, you can always use JSON columns for that.
So don't do that, then. Designing database migrations to be non-breaking is part of the game and if you're not doing it, you can't claim to understand the technology you're replacing. Not having an explicit schema doesn't mean you don't have to think about your schema. It just means you've chucked out all the tooling for keeping it sane.