CLOB & BLOB limitations in PostgreSQL

From: Jack(dot)O'Sullivan(at)tessella(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: CLOB & BLOB limitations in PostgreSQL
Date: 2014-04-11 14:45:14
Message-ID: OF312B1274.D3B65F45-ON80257CB7.004BA4DA-80257CB7.00510BD0@tessella.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I am working for a client who is interested in migrating from Oracle to
Postgres. Their database is currently ~20TB in size, and is growing. The
biggest table in this database is effectively a BLOB store and currently
has around 1 billion rows.

From reading around Postgres, there are a couple of limits which are
concerning in terms of being able to migrate this database. We are not up
against these limits just yet, but it is likely that they will be a
potential blocker within the next few years.

1) Table can be maximum of 32TB (http://www.postgresql.org/about/)

2) When storing bytea or text datatypes there is a limit of 4 billion
entries per table (https://wiki.postgresql.org/wiki/BinaryFilesInDB)

With both of these, are they hard limits or can they be worked around with
partitioning of tables? Could we set the table up in such a way that each
child table was limited, but there was no limit on the number of children?

With point two, does this mean that any table with a bytea datatype is
limited to 4 billion rows (which would seem in conflict with the
"unlimited rows" shown by http://www.postgresql.org/about)? If we had rows
where the bytea was a "null" entry would they contribute towards this
total or is it 4 billion non-null entries?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2014-04-11 15:09:57 Re: efficient way to do "fuzzy" join
Previous Message Achilleas Mantzios 2014-04-11 14:15:28 Re: Linux vs FreeBSD

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-04-11 15:09:22 Re: [PATCH] Negative Transition Aggregate Functions (WIP)
Previous Message Andres Freund 2014-04-11 14:28:38 Signaling of waiting for a cleanup lock?