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

On Thu, Apr 27, 2023 at 7:36 PM Jakub Wartak <jakub(dot)wartak(at)enterprisedb(dot)com>
wrote:

> > Spurious whitespace.
>
> Hopefully fixed, I've tried to align with the other entries tags.

Hope springs eternal. ;-)

--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>

+
<table id="limits-table">

@@ -92,11 +93,24 @@
<entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
</row>

- <row>
- <entry>partition keys</entry>
- <entry>32</entry>
- <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
- </row>
+ <row>
+ <entry>partition keys</entry>
+ <entry>32</entry>
+ <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
+ </row>

- <entry></entry>
+ <entry>see note below on TOAST</entry>

Maybe:

"further limited by the number of TOAST-ed values; see note below"

> > + <row>
> > + <entry>large objects size</entry>
> > + <entry>subject to the same limitations as single <symbol>relation
size</symbol></entry>
> > + <entry>LOs are stored in single pg_largeobjects relation</entry>
> > + </row>
> >
> > Are you under the impression that we can store a single large object up
to table size? The limit is 4TB, as documented elsewhere.
>
> 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.

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.

+ <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". Also the shared counter is the cause of the
slowdown, but not the reason for the numeric limit. "Today" is irrelevant.
Needs polish.

> > + After 1000000 failed attempts to get a free OID, a first log
> > + message is emitted "still searching for an unused OID in relation",
but operation
> > + won't stop and will try to continue until it finds the free OID.
> >
> > Too much detail?
>
> OK - partially removed.

+ 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?

+ 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."

> > + 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.

--
John Naylor
EDB: http://www.enterprisedb.com