Well, I'll tell you what my reality has been. It's been that every time I started a project, it appeared like I just needed to extract some objects from the database. But very soon it turned out that there wasn't just one way to view that information. There were two or three important use cases that destroyed my preferred, supposedly "natural" view.
Now, I'm fully aware that sometimes one preferred view of the information you have is so dominant that you have to optimize for that case. The question, is how do you optimize? Do you hard code that particular view from the beginning, or do you normalize first and then layer an optimized view on top of it, e.g. in the form of caching or materialized views? RDBMS make it easy to do the latter.
You say "they really only handle one (small) problem space well -- modeling relational data."
I think being relational is not an a priori property of data and relational modelling is not a problem space. It's the other way around. First you have a problem space, then you decide how to model it, and then you get the data according to that model. Assuming that information already has a kind of "natural" model attached to it is a fallacy in my view.
The reality is that 99% of web apps don't really want a RDBMS. There are no arbitrary queries to run, they just need to extract some objects from the database, interact with them, and render a web page. As of late, people have been using ORMs to make their relational database look like an object database. The problem is that the objects you get from an ORM don't work like real objects (try making a cyclical structure, or try `change-class`-ing the objects; doesn't work). This usually means you need to map the objects from your ORM into a better model of your app, adding complexity. (Some ORMs also screw up the relational part, making the data in the database nearly useless to any other apps. Oops.)
People are starting to realize that this model is a waste of their time, and they are using object databases instead. Now there is no ORM, they write their objects that the app interacts with, and those are persisted as needed. Suddenly half your app's code is gone, and it runs faster. That is why people are excited. Less code is always exciting.
This article is really heavy on key/value databases, probably because they are easy to understand. But really, key/value databases are like the assembly language of databases. You will have to do a lot of work to run your app on a key/value database. It is better to use something higher-level, like CouchDB or an object database. (For an OODB, I recommend KiokuDB. It has indexing (stolen from Postgres), and can store your data to BDB, a directory on disk, a RDBMS, Amazon SimpleDB, or CouchDB. This gives you a lot of flexibility.)