Re: [GENERAL] CLOB & BLOB limitations in PostgreSQL

Bruce Momjian wrote
> On Mon, Apr 14, 2014 at 02:01:19PM +0200, Ivan Voras wrote:
>> On 11/04/2014 16:45, Jack.O'

> Sullivan@

> wrote:
>>
>> > 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?
>>
>> This seems strange. A core developer should confirm this but it doesn't
>> make much sense - "bytea" fields are stored the same as "text" fields
>> (including varchar etc), i.e. the "varlena" internal representation, so
>> having the limit you are talking about would mean that any non-trivial
>> table with long-ish text fields would be limited to 2^32 entries...
>
> [ moved to hackers ]
>
> Uh, I had not thought of this before but I think we need oids for toast
> storage, which would explain this wiki text:
>
> https://wiki.postgresql.org/wiki/BinaryFilesInDB
>
> Storing binary data using bytea or text data types
>
> Minus
>
> bytea and text data type both use TOAST
> limited to 1G per entry
> --> 4 Billion entries per table
>
>
> Is that correct?

Reading only http://www.postgresql.org/docs/9.3/static/storage-toast.html
...

Since only actual out-of-line values require chunk_id (an OID) the number of
main table rows has a minimum but not a maximum. However, the minimum would
appear to be "2^32 / {# of toast-able columns }" - each table can only have
one "pg_class.reltoastrelid" so all toast-able columns on that table pull
from the same OID pool.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Re-GENERAL-CLOB-BLOB-limitations-in-PostgreSQL-tp5800032p5800037.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.