Similarly, with the e-commerce orders: what if you want to retrieve by the product ordered? It's not unreasonable to assume a situation where certain products are fulfilled by warehouse A and others by warehouse B. Well, you're in the same boat again doing a less efficient thing.
Plus, what is hard to scale when it comes to a database? The article is passing off SELECT statements as hard to scale. Now, I'm not saying that they're a piece of cake. You can get into a lot of trouble. However, reads are relatively easy to scale since you can just add more boxes. Writes are hard to scale because, unless you shard and do other hard things, you only get the power of one box for writes (since the writes have to be done on every box while a read only has to occur on one box).
So, even if you're using a document based store, you eventually have to shard. Now, when you never relate data, sharding can be a lot easier since it can be done based on a hash function. Systems like memcached do this automatically. So when you say get(1, 42, 64, 128) it will be able to hash those ids and send each request to the proper server for that item. But that means that you lose out on a lot of ease. And most of these alternative stores don't do that for you (and it's why memcached is such a useful tool alongside a RDBMS).
And SQL databases do scale a lot more than Heroku (in these two articles) seems to let on. Wikipedia, Facebook, Craigslist, and Flickr are all backed by MySQL. Now, not MySQL alone. Memcached is a big part of it for all/most of them (I don't remember what each site uses exactly). There's a reason why many of the largest sites use a SQL database and it's not because they're unaware of other storage engines.
It seems like Heroku might be getting a lot of complaints that the service isn't magically scaling. Computers aren't magic and document based databases aren't any more magic. CouchDB uses B-Tree indexes just like relational databases. The difference isn't so much that these data stores offer better performance for some lookups. It's more that they only offer the lookups that can have good performance.
I feel like I should offer some free advice to Heroku: your SQL databases would scale better if your dedicated SQL boxes were Amazon's high-RAM boxes rather than the high-CPU ones they opted for. RAM means more for database performance than CPU. Oh, also, offer some consulting for clients on their database woes. A lot of the time, people are doing lookups that should be using an index, but they haven't created it and so the database has to do a full table scan rather than an index lookup. And there's a big difference there. A full table scan of 1 million records will take 50,000 times longer than an index scan. Yeah, indexes are good. One of the reasons that CouchDB "scales so well" is that you can only do queries on things you've made indexes for.
I'm not saying that non RDBMS don't have their place. They do. However, we keep seeing articles posted about RDBMS not scaling and it doesn't seem like they quite know the purpose of scale. Scale doesn't have to be infinite. Nothing will do that. The questions is: will it scale enough to handle the traffic? And SQL databases will unless you're really, really big. Do you expect your site to become a top 500 site on the internet? Heck, WordPress.com at #20 is doing fine implementing a not very efficient SQL backed blog. Now, I'm sure they implement caching and such, but it's still SQL backed.
Basically: learn a lot about indexes. If you become one of the top sites on the internet, hire someone who can help you. In the meantime make your product and don't worry too much about the FUD.