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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
