Take the 2-minute tour ×
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. It's 100% free, no registration required.

I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database software like Oracle).

Is there a way to manually increase that limit? And if so, what is the penalty for doing so?

share|improve this question
6  
May I ask what you are storing that requires an unpartitioned table >32Tb? Completely unmanageable... –  Phil Nov 12 '12 at 14:49
5  
I'm interested to know what table you have that would need to be larger than 32TB. –  JNK Nov 12 '12 at 14:51
4  
If you've basically trying to replicate Google (storage and search of every website) - a single table in postgres isn't going to cut it. –  rfusca Nov 12 '12 at 15:55
6  
It is regularly much more efficient to store binary large object like images in the file system. Do that and store the file name / path in the database along with URL and other metadata. You loose guaranteed referential integrity, but you gain an order of magnitude in speed and storage size. How would you backup xxTB tables anyway? –  Erwin Brandstetter Nov 12 '12 at 18:17
3  
In terms of answering your actual question, the best bet for what amounts to trying to change the internal postgres code and recompile is to ask on the postgres mailing list. Getting an answer from somebody like Tom Lane is what I'd do before I attempted to do something this drastic. But you'll likely get the same "why in the world would you want to" response. –  rfusca Nov 12 '12 at 18:22

2 Answers 2

up vote 8 down vote accepted

Just use a different setting for the block size:

--with-blocksize=BLOCKSIZE

The default, 8 kilobytes, is suitable for most situations; but other values may be useful in special cases. The value must be a power of 2 between 1 and 32 (kilobytes).

Using 32 kilobytes, your table has a maximum size of 128TB.

share|improve this answer
    
+1 Niiiiiiiiice. –  rfusca Nov 12 '12 at 20:49
    
Cool thing, man. –  user15267 Nov 12 '12 at 21:09
2  
Note that non-default block sizes aren't as well tested, though. Bugs have cropped up before. –  Craig Ringer Nov 13 '12 at 0:51

Frank's answer is entirely correct, but there's more to it.

Don't do this. Partition your table instead. PostgreSQL's table partitioning isn't wonderful, but it's going to be better than a 32TB+ table.

share|improve this answer
    
So, so, sooooo true. –  rfusca Nov 13 '12 at 1:32
    
I love the "Don't do this", +1. And welcome to 2ndQuadrant! –  Frank Heikens Nov 14 '12 at 7:03

Your Answer

 
discard

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