On Tue, Jun 13, 2023 at 10:20 AM John Naylor <
john.naylor@enterprisedb.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.