From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Jack(dot)O'Sullivan(at)tessella(dot)com *EXTERN*" <Jack(dot)O'Sullivan(at)tessella(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CLOB & BLOB limitations in PostgreSQL |
Date: | 2014-04-14 08:19:51 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17CEFFA6@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Jack.O'Sullivan wrote:
> 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/ <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 <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?
Yes, if you store the BLOBs as bytea. The limits will be per partition.
If you want to use LOBs, there cannot be more than 2^32 per database.
> 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
> <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?
I think it is "4 billion rows that contain a column that is TOASTed".
NULLs won't contribute.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Rémi Cura | 2014-04-14 09:58:08 | Re: efficient way to do "fuzzy" join |
Previous Message | Albe Laurenz | 2014-04-14 08:03:16 | Re: streaming replication + wal shipping |
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Kreen | 2014-04-14 08:55:33 | Re: Problem with txid_snapshot_in/out() functionality |
Previous Message | Heikki Linnakangas | 2014-04-14 08:06:17 | Re: Race condition between PREPARE TRANSACTION and COMMIT PREPARED (was Re: Problem with txid_snapshot_in/out() functionality) |