Database solution for long term (horizontal) scalability

Question

It is well recognized that at very large scale (tera to peta and over bytes of data), column stores or something specifically custom-built are the default choices.

What are off-the-shelf SQL based choices before one gets to petabyte scale? In particular, considering a multi master setup. Or, is sharding the real answer to these kind of scalability questions regardless of database choice?

In particular, some of the main requirements are:

• easy to start with
• time tested solution
• supports sharding (scalable horizontally across tens to thousands of servers) out of the box or with off-the-shelf tools
• good built in text search or easily integrable with lucene, solr, etc.
• ACID compliant
• fast read
• concurrent read-writes
• good support for variety of indexing techniques
• open source and decent community support

Answer 1

In general, the best database for scaling is exactly the one, that your current (or possible to hire) team knows the best from performance & scaling perspective.

Definitely MySQL is the easiest database of all to start with.

It also has many scalability-related advantages over Postgres, like High Availability, much better scalability threading model, data encryption, monitoring, auditing support etc. However many of these advantages are not open source, and not free. Instead they are part of MySQL Enterprise Edition, which you can buy from Oracle for $5000 per server per year.

Core open source MySQL is faster than Postgres in most typical scenarios, however you can never be sure, which database will be faster in your particular environment and its queries.

MySQL is also easier to maintain automatically, which results in easier sharding. Facebook used to use over 4000 shard instances of MySQL.

Postgres has backup, which can be very useful at large deployments. MySQL is easier to backup, but you can only dump a full backup, which is much larger and the whole operation is slower, when you have lots of data.

These are only a few example considerations from my professional experience, but in general, the most important thing is what I wrote at the beginning of this answer.