Manually increase PostgreSQL's table size limit of 32 TB?

Question

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?

Answer 1

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.

• Craig Ringer: Note that non-default block sizes aren't as well tested, though. Bugs have cropped up before.

Answer 2

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.

• goulashsoup: This question is now 6 years old i wonder if this is still a thing? Increasing the block-size seems less overhead compared to table partinioning.
• Craig Ringer: @goulashsoup Actually because of the costs involved in huge b-tree indexes, it's usually cheaper to partition.
• Vérace: @CraigRinger - this question has had more ressurections than Lazarus! :-) Could you expand on the difference between indexes on an 8kb block-size table and one of 32kb blocks?