Re: Fwd: [GENERAL] 4B row limit for CLOB tables - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Fwd: [GENERAL] 4B row limit for CLOB tables
Date
Msg-id 20150425181910.GB17791@momjian.us
Whole thread Raw
In response to Re: Fwd: [GENERAL] 4B row limit for CLOB tables  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Responses Re: Fwd: [GENERAL] 4B row limit for CLOB tables
List pgsql-hackers
On Fri, Apr 24, 2015 at 11:39:04PM -0500, Jim Nasby wrote:
> On 4/24/15 7:11 PM, Álvaro Hernández Tortosa wrote:
> >On 24/04/15 05:24, Tom Lane wrote:
> ...
> >>TBH, I've got very little enthusiasm for fixing this given the number
> >>of reports of trouble from the field, which so far as I recall is zero.
> >>Álvaro's case came up through intentionally trying to create an
> >>unreasonable number of tables, not from real usage.  This thread likewise
> >>appears to contain lots of speculation and no reports of anyone hitting
> >>a problem in practice.
> >
> >     It is certainly true that this was a very synthetic case. I
> >envision, however, certain use cases where we may hit a very large
> >number of tables:
> 
> The original case has NOTHING to do with the number of tables and
> everything to do with the number of toasted values a table can have.
> If you have to toast 4B attributes in a single relation it will
> fail. In reality, if you get anywhere close to that things will fall
> apart due to OID conflicts.
> 
> This case isn't nearly as insane as 4B tables. A table storing 10
> text fields each of which is 2K would hit this limit with only 400M
> rows. If my math is right that's only 8TB; certainly not anything
> insane space-wise or rowcount-wise.
> 
> Perhaps it's still not fixing, but I think it's definitely worth
> documenting.

And it is now documented in the Postgres FAQ thanks to 'Rogerdpack',
which is where that "maximum" table came from:
https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F
Note if you are storing a table with rows that exceed 2KB in size(aggregate size of each row) then the "Maximum number
ofrows in atable" may be limited to 4 Billion, see TOAST. 
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +



pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: INSERT ... ON CONFLICT syntax issues
Next
From: Andres Freund
Date:
Subject: Re: INSERT ... ON CONFLICT syntax issues