Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 31 down vote accepted

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.

share|improve this answer
1  
It seems that Postgres 9.6 will get some intra-query parallelism enhancements (parallel seq scan, parallel join). – a_horse_with_no_name Jan 4 at 14:55
1  
I figure it will take a couple more releases to get this to be really useful. – Chris Travers Jan 5 at 9:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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