Обсуждение: Re: [GENERAL] CLOB & BLOB limitations in PostgreSQL
On Mon, Apr 14, 2014 at 02:01:19PM +0200, Ivan Voras wrote: > On 11/04/2014 16:45, Jack.O'Sullivan@tessella.com 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 1Gper entry--> 4 Billion entries per table Is that correct? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
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.
Bruce Momjian <bruce@momjian.us> writes: > 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? No. It'd be 4 billion toasted-out-of-line entries per table (actually, you'd start hitting performance issues well below that, but 4G would be the hard limit). Small values, up to probably a KB or so, don't count against the limit. regards, tom lane