What I need is an RDBMS that allows retrieving small chunks of data really fast (E.G. a user's posts, the likes for a post, etc), and yet is just as fast in concurrent insertions (E.G. while there is a query running for retrieving the likes for an item, 200 people might be liking 200 other posts).
I have read that Facebook is facing problems with MySQL, and has done heavy amounts of sharding to compensate for MySQL's shortcomings in handling large amounts of transactions. I'm looking for a solution that would allow developers to just code, and leave the scaling up to the database engine. I hear NewSQL engines are good for this, but I have no first-hand knowledge, and am looking for someone who has managed a system with a large amount of data and concurrent transactions, with the need for a really small query time.
If your product isn't written yet, write it well with whatever technology you already use. Use a standard relational database, perhaps with an ORM if you want to easily swap between MySQL and others. Then, if you get to a scaling problem, fix from there. You can scale outwards in the meantime.
The key message here is: don't expend great effort on scaling costs if you are not sure you need it. If you want to know more about that, search for "premature optimisation is the root of all evil" :).
I like Propel coupled with a web framework for this reason. I start off with something like MySQL, and then later on I might decide that PostgreSQL makes a better fit - and if I have done everything the 'Propel way', switching to the new engine is trivial - rebuild and go. Even if I have made use of custom SQL for performance reasons, a good MVC structure makes it a great deal easier to branch, work out what needs to change, and to modify for the new system.
Should you need it later, you can wire in pieces of radically different systems piecemeal. Perhaps one massive table is causing performance problems - cache fragments of HTML to memcache, or maybe store some data in denormalised form in NoSQL. A hybrid of all of the above is, in practice, much more pragmatic than a "pure" switch from one to another, and much less work!