Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns

On Tue, Jun 13, 2023 at 10:20 AM John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
wrote:

Hi John,

v3 is attached for review.

> >
> > - <entry></entry>
> > + <entry>see note below on TOAST</entry>
>
> Maybe:
> "further limited by the number of TOAST-ed values; see note below"

Fixed.

> > I've wrongly put it, I've meant that pg_largeobject also consume OID
> > and as such are subject to 32TB limit.
> No, OID has nothing to do with the table size limit, they have to do with
the max number of LOs in a DB.

Clearly I needed more coffee back then...

> Also, perhaps the LO entries should be split into a separate patch. Since
they are a special case and documented elsewhere, it's not clear their
limits fit well here. Maybe they could.

Well, but those are *limits* of the engine and they seem to be pretty
widely chosen especially in migration scenarios (because they are the only
ones allowed to store over 1GB). I think we should warn the dangers of
using pg_largeobjects.

> > + <para>
> > + For every TOAST-ed column (that is for field values wider than
TOAST_TUPLE_TARGET
> > + [2040 bytes by default]), due to internal PostgreSQL implementation
of using one
> > + shared global OID counter - today you cannot have more than
4,294,967,296 out-of-line
> > + values in a single table.
> > + </para>
> > +
> > + <para>

> "column" != "field value". (..)"Today" is irrelevant. Needs polish.

Fixed.

> Also the shared counter is the cause of the slowdown, but not the reason
for the numeric limit.

Isn't it both? typedef Oid is unsigned int = 2^32, and according to
GetNewOidWithIndex() logic if we exhaust the whole OID space it will hang
indefinitely which has the same semantics as "being impossible"/permanent
hang (?)

> + out-of-line value (The search for free OIDs won't stop until it finds
the free OID).

> Still too much detail, and not very illuminating. If it *did* stop, how
does that make it any less of a problem?

OK I see your point - so it's removed. As for the question: well, maybe we
could document that one day in known-performance-cliffs.sgml (or via Wiki)
instead of limits.sgml.

> + Therefore, the OID shortages will eventually cause slowdowns to the
> + INSERTs/UPDATE/COPY statements.

> Maybe this whole sentence is better as "This will eventually cause
slowdowns for INSERT, UPDATE, and COPY statements."

Yes, it flows much better that way.

> > > + Please note that that the limit of 2^32
> > > + out-of-line TOAST values applies to the sum of both visible and
invisible tuples.
> > >
> > > We didn't feel the need to mention this for normal tuples...
> >
> > Right, but this somewhat points reader to the queue-like scenario
> > mentioned by Nikita.

> That seems to be in response to you mentioning "especially to steer
people away from designing very wide non-partitioned tables". In any case,
I'm now thinking that everything in this sentence and after doesn't belong
here. We don't need to tell people to vacuum, and we don't need to tell
them about partitioning as a workaround -- it's a workaround for the table
size limit, too, but we are just documenting the limits here.

OK, I've removed the visible/invisible fragments and workaround techniques.

-J.