1

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
3
  • MySQL is not the DBMS with best performance. So definitely PostgreSQL if you have to choose between them. Not sure though how well PostgreSQL performs with petabyte size.
    – John
    Aug 5 '15 at 12:31
  • @John thanks for that. I am looking for a solution to last me very well before I get to petabyte scale anyway..
    – Yogesch
    Aug 5 '15 at 12:35
  • @John just curious if you can point us to a URL comparing the performance of the two? OP, you might want to look at digitalocean.com/community/tutorials/… Which says "When Not To Use PostgreSQL Speed: If all you require is fast read operations, PostgreSQL is not the tool to go for." Aug 5 '15 at 14:00
0

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.

3
  • Thanks for the answer. The scalability related advantages you mentioned which are part of the Enterprise Edition, does PostgreSQL have anything comparable?
    – Yogesch
    Aug 5 '15 at 14:54
  • PostgreSQL has many enterprise forks, from which the best is IMHO EnterpriseDB, which also has extended replication capabilities. But neither of these are as powerful as MySQL Enterprise. Aug 5 '15 at 19:34
  • That is not surprising at all, considering PostgreSQL's replication is a relatively recent feature. But it does seem production ready - based not on my direct experience, but from dba.stackexchange.com/questions/34472/…
    – Yogesch
    Aug 5 '15 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.