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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
>> On Sun, Feb 1, 2015 at 03:54:03PM +0100, lvaro Hernndez Tortosa wrote:
>>> The problem here is that performance degrades exponentially, or
>>> worse. Speaking here from experience, we already tested this for a
>>> very similar case (table creation, where two oids are consumed from
>>> a global sequence when inserting to pg_class). Have a look at
>>> http://www.slideshare.net/nosys/billion-tables-project-nycpug-2013,
>>> slides 43-45. We tested there this scenario and shown that table
>>> creations per second dropped from 10K to a few per second and then
>>> to a few per day. In the graphs you can't even realize there were
>>> more tables been created. At around 8K tables from the theoretical
>>> limit of 4B oids consumed, the process basically stopped (doing more
>>> insertions).

>> We don't report the maximum number of tables per database, or the
>> maximum number of TOAST values. Agreed?

> For my 2c, this limitation is a surprise to users and therefore we
> should add documentation to point out that it exists, unless we're going
> to actually fix it (which is certainly what I'd prefer to see...).

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.

Certainly this is likely to become an issue at some point in the future,
but I'm not finding it very compelling to worry about now. By the time
it does become an issue, we may have additional considerations or use
cases that should inform a solution; which seems to me to be a good
argument not to try to fix it in advance of real problems. Perhaps,
for example, we'd find that at the same time we ought to relax the 1GB
limit on individual-value size; or perhaps not.

Having said all that, if we did try to fix it today, I'd imagine changing
TOAST value identifiers to int64 and inventing a new TOAST pointer format
for use when 32 bits isn't wide enough for the ID. But I think we're best
advised to hold off doing that until the need becomes pressing.

regards, tom lane