Re: Fwd: [GENERAL] 4B row limit for CLOB tables

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 of rows in a
table" may be limited to 4 Billion, see TOAST.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +