I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB.

I suspect that limits as such do exist as one would not come up with Netezza, Greenplum, or Vertica, etc, however I would like to ask if anyone here has a reference to any research paper or formal case studies where these limits are quantified.


1 Answer 1


There's no simple answer to your question, but here are a few things to think about.

First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems. There are 32TB databases on PostgreSQL out there. However, at the same time performance will degrade somewhat because it is having to hit disk on everything. Similarly if you have 50TB if data but have a commonly hit set of about 100GB, then you can build a server with enough RAM to keep that part of the db in memory and you are golden.

On the other hand if you are trying to take the mode (most common value) out of 1TB of data, it doesn't matter what system you are using, this is going to be painful with or without sharding. (Edit: Sharding may, in fact, make this problem worse.)

The major problems you will run into with huge db's on MySQL and PostgreSQL involve the fact that neither supports intraquery parallelism. In other words a query is run as a single block by a single thread, and it can't be broken down into pieces and run separately. This is most often an issue when running large analytical queries over large amounts of data. This is where Postgres-XC and Green Plum come to the rescue since they separate storage from execution, and can do this at the coordinator level. Note that Postgres-XC and Green Plum essentially use sharding internally but the coordinators enforce all consistency globally.

With intraquery parallelism you can break up the query, have different processors/disk I/O channels run parts of it, and report back pieces of the result set to be assembled and passed back to the application. Again, this is usually most helpful in analytic rather than transaction processing loads.

The second thing is that some systems, like Vertica or Greenplum store columns of information together. This makes the system harder to use from an OLTP perspective and decreases performance there, but it drastically increases performance for large analytic workloads. So this is a workload-specific tradeoff.

So the answer is that once you get above 1-2 TB in size you may find yourself faced with a number of tradeoffs between systems and workloads. Again this is specific to databases, size of working sets, etc. However at this point you really have to go with snowflake systems, i.e. ones unique and tailored to your workload.

This of course means that the limits are not generally quantifiable.

Edit: I have now worked with a 9TB database which handles a mixture of decision support and transactional processing workloads in PostgreSQL. The single largest challenge is that if you have questions that hit large portions of the data set, you will have to wait a while for the answer.

However with careful attention to the fundamentals (including indexes, autovacuum, how these work on the low level, etc) and sufficient computing resources, these are entirely manageable (and I estimate would be manageable well into the 30TB range in Pg).

Edit2: Once you head to 100TB though what works will depend on your data set. I am working on one right now that will not scale into this range because it will hit the 32TB per table limit in PostgreSQL first.

  • 2
    It seems that Postgres 9.6 will get some intra-query parallelism enhancements (parallel seq scan, parallel join).
    – user1822
    Commented Jan 4, 2016 at 14:55
  • 1
    I figure it will take a couple more releases to get this to be really useful. Commented Jan 5, 2016 at 9:31
  • @ChrisTravers Is there another database that supports this kind of situation better? Maybe not necessarily RDBMS? Thanks
    – konung
    Commented Sep 11, 2017 at 21:22
  • 1
    @konung I don't know to be honest. I think it is worth playing around with MapReduce engines at a certain scale because this helps shape the way you think about your data. At very large scales you really have to know what you are doing. Solutions like Teradata and Postgres-XL help but they are solutions which demand clear knowledge of what you are doing (and you can always build your own at that point built on any RDBMS out there). Commented Sep 12, 2017 at 7:17
  • 1
    Also one reason I recommend playing with Mongo is that although (maybe even because) it doesn't scale so well, it does teach you how to think about federated data and MapReduce when you get to that point. Commented Sep 12, 2017 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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