You need HA and no matter your other requirements, or the ability of modern RDBMSs to scale up and cluster out, you're going NoSQL. Sorry Postgres, MySQL... You power some vast systems but you're just not webscale enough for Sphax here :)
On a serious note, scaling and replicating and clustering aren't trivial topics (yet) because there are so many different ways of doing it, to fit different performance profiles.
But it can be done. It can be done well. If a large HA database is a core part of your product of company, hire somebody who knows about databases to do databases.
I worked with PostgreSQL, work currently with MySQL and Cassandra. With Cassandra you get HA out of the box, with PostgreSQL and MySQL not so much.
Your last paragraph is true, except most company can't afford that.
It's the unwavering, front-line suggestion for NoSQL that I consider HDD. There was tons of this after Mongo started getting popular. But it's suggesting a satsuma as the best type of apple. Yeah, NoSQL does some stuff well, but there's a pile of things it doesn't handle at all. It's not a drop-in replacement and people treating it as such have wasted so much developer time trying to turn it back into a relational database.
HA isn't a feature exclusive to either type of database.
Besides, that switch you're talking about just doesn't work out of the box. We never had any success with failover on our Galera cluster. Now, I'm not saying it's not doable, because I'm sure some teams have this work flawlessly. My point is that with Cassandra, it works out of the box without problems.
For instance Twitter going down would be annoying, but global air traffic control going down would "matter".
Which I think might somewhat align with the original article's sentiment. Part of the hype cycle is driven by a hubris that we often engage in. Which is that we want our problems to be bigger and more important than they might actually be.