Does anyone else think the scenario in the explanation is an unreasonable request to make of a relational database? I think that if you've created a design that requires you to update a 50K row table 500 times a second that itself is heavily indexed and used heavily in joins, you have a software design problem more than a database problem. I wouldn't expect any database to handle that and am surprised that mysql does. One has to ask: for how long will it work? Surely the clock is running out on such a design.
You're not wrong. But mostly we haven't collectively agreed that relational databases aren't great for highly-indexed rapid-update join tables.
I think we will at some point. That's the primary original use case for a lot of NoSQL, and the reason Twitter had so much trouble with relational databases.
But these are cultural understandings, and those move slowly. Also, we're poorly (collectively) equipped to handle subtlety in these discussions, so mostly we're trying to move from "relational databases are perfect for all use cases" to "NoSQL databases are perfect for all use cases" -- which is even less true, not more true.
Culturally, this is a hard thing to keep in our collective brain.
How has NoSQL addressed join tables? All the approaches I've seen are much, much slower than with a traditionally vertically scaled relational database—or by moving away from joining altogether—it's traditionally been the twin pressures of scale and replication that force people to move to a distributed database.
Non-relational DBs (the Not Only SQL sort) usually passes the buck up to the application layer for several things. It is in a way a good thing to do if the DB wants to focus on scale out issues such as consistency of replicas or high availability. I personally find it pretty easy to work with alternate data models, thus not relying on the possibility of a join or a transaction, as long as I am not writing a banking application. Without a relational data model, the biggest problem becomes verification IMHO; verification of the DB, the application logic, and whether the application logic makes the right assumptions about the DB it is using.
Cassandra, for instance, makes it possible to query your join-type tables in relatively manual chunks using slice queries. You have to do a lot more of it in the application so you wind up with weird partial failures...
But at scale, weird partial failures, results as they are found and eventual consistency are usually preferable to a really long DB query that never returns.
Above a certain scale, big joins are simply not usable. NoSQL's manage-it-yourself approach is good for getting partial results where full results are too expensive.
You can think of it as applying a heuristic approach where an exact approach is too expensive, if it makes you feel less like NoSQL sullies the purity of databases :-)
Well, F1 does. I'm fairly sure FoundationDB did too. It isn't incompatible--I'd argue joins are natural for certain tasks, and certainly reduce developer load, especially if the latency isn't a driving priority.
Yes, that's SQL, but the term nosql was always orthogonal to the priorities of the movement (horizontal scalability).
Absolutely - going from Postgres to MySQL is only trading one set of problems for another. It's a longer runway, but not infinite.
Their exact use-case is what things like Cassandra were built for - insanely high writes / updates. They're also built to split your load across N systems, as long as you're still using a monolithic database (even with read replicas) you physically can't get the same performance that you could with one of the NOSQL distributed systems. YMMV for specific performance, I've seen a huge MySQL burn through queries like butter and a Cassandra cluster crawl on a tiny data set.
The second part of your question is the real crux of the problem - "heavily indexed and used heavily in joins". Neither of those (RDBMS or NOSQL) work well in either scenario. This article indicates why PG isn't great, the Uber article indicates some of the (minor) downsides of MySQL. They're already using Schemaless to bend their RDBMS into a fancy key-value store, so they're halfway to using a real one with their home-built indexes already. For NOSQL you generally don't get joins, unless you write them yourself. You also end up with manual "write amplification" since you denormalize, write the data 10 times to index it 10 different ways. You can be smart about it so it's not exactly 10x, but you'll end up with more than your original problematic throughput, albeit spread across more systems.
An Uber engineer at a conference said that none of the open-source NoSQL systems could handle their load, and they they had to heavily hack one of them (which I think was Cassandra but the memory is vague) to get the last bit of performance out of it while they were building Schemaless.
They seem to be running Cassandra still, at least as of last month - "Running Cassandra on Apache Mesos Across Multiple Datacenters at Uber" https://www.youtube.com/watch?v=U2jFLx8NNro
I believe that was the subtext that the writer was trying to convey. Despite it being a bad pattern, it's one that their users still encounter and people are asking a solution for.
> I think that if you've created a design that requires you to update a 50K row table 500 times a second that itself is heavily indexed and used heavily in joins, you have a software design problem more than a database problem.
This would almost certainly be true if the design were widespread (which it's not as far as I know), but it isn't necessarily true for all cases.
I think it would be better framed as an optimization problem. If you design for a domain that actually models 500 events per second in a dataset of 50K items, the simplest correct implementation will implement exactly that. If that domain also involves reads which benefit from joins and indices that make those writes prohibitively slow, you have a conflicting set of optimization paths. The fact that some tools don't accommodate that well is an implementation detail, and addressing that fact is optimization, not necessarily a primary design consideration.
Yeah it feels like the kind of thing where regardless of the database system they use they're going to eventually hit some performance issues, but undoing that DB mess is likely no easy task and they're betting that they can keep kicking the can there.
Multiply the number of updates times the number of indexes. Then consider that with such a small table, there's going to be contention for the same rows.
Updates actually create new rows, so vacuum needs to be able to remove older, no-longer-visible versions of the row.
But vacuum can't keep up, because at any given time many versions of the row are potentially visible.
