> It is essentially using the memory on a large number of nodes to buffer bursty throughput, and using background processes to collate the data later onto disk.
You're not describing NoSQL. You're describing something like MongoDB specifically. You're right about the specific case, wrong in the general case.
> There is almost no case where an explicit distributed caching or queuing solution backed by a traditional DB isn't strictly better.
Bah.
If you're looking at big enough data sets (think "Walmart scale", not "web scale") then you have to design your data store to cope with this volume. First, it has to be replicated to multiple servers to serve more queries and for reliability, and is an eventually consistent system a consequence (basically any time you use your read replica, or do anything on a multimaster configuration). You'll need something shardable or multimaster to achieve horizontally scalablity and you'll probably also lose your foreign key enforcement and uniqueness constraints when you do this, so you might as well go the distance and fully denormalise everything for consistent access times. (Have you ever had the Postgres query planner suddenly make something take 10x longer than it usually does because your data patterns changed and decided to use a different query plan than it's ever used before? Yeah, that's a world of fun. Now make that thing a time-critical daily batch job and really squirm.)
When you do the things to make this work, you're basically restricting yourself, abandoning database features. As a consequence, you are going to be in for a world of pain, period, sorry. One key advantage of good NoSQL software is that it doesn't give you those features to begin with, so it's harder to get it wrong. It also has data structures designed to make this sort of thing work well and be performant in ways that general purpose databases won't be, on account of being designed for general purposes.