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

Hi John,

Thanks for your review. Here's v2 attached.

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

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

> -     <entry>limited by the number of tuples that can fit onto 4,294,967,295 pages</entry>
> -     <entry></entry>
> +     <entry>limited by the number of tuples that can fit onto 4,294,967,295 pages or using up to 2^32 OIDs for
TOASTedvalues</entry>
 
> +     <entry>please see discussion below about OIDs</entry>
>
> I would keep the first as is, and change the second for consistency to "see note below on TOAST".

Fixed.

> Also, now that we have more than one note, we should make them more separate. That's something to discuss, no need to
doanything just yet.
 

OK.

> The new note needs a lot of editing to fit its new home. For starters:
>
> + <para>
> +  For every TOAST-ed columns
>
> column

Fixed.

> + (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
>
> + 2^32
>
> Perhaps it should match full numbers elsewhere in the page.

Fixed.

>
> +(unsigned integer;
>
> True but irrelevant.
>
> +  4 billion)
>
> Imprecise and redundant.

Removed both.

> + out-of-line values in a single table, because there would have to be
> +  duplicated OIDs in its TOAST table.
>
> The part after "because" should be left off.

Removed.

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

> +  It is therefore crucial that the autovacuum manages to keep up with cleaning the
> +  bloat and free the unused OIDs.
> + </para>
>
> Out of place.

I have somewhat reworded it, again just to reference to the above.

> + <para>
> +  In practice, you want to have considerably less than that many TOASTed values
> +  per table, because as the OID space fills up the system might spend large
> +  amounts of time searching for the next free OID when it needs to generate a new
> +  out-of-line value.
>
> s/might spend large/will spend larger/ ?

Fixed.

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

> + Therefore,
> +  the OID shortages may (in very extreme cases) cause slowdowns to the
> +  INSERTs/UPDATE/COPY statements.
>
> s/may (in very extreme cases)/will eventually/

Fixed.

> + It's also worth emphasizing that
>
> Unnecessary.

Removed.

> + only field
> +  values wider than 2KB
>
> TOAST_TUPLE_TARGET

Good catch, fixed.

> + will consume TOAST OIDs in this way. So, in practice,
> +  reaching this limit would require many terabytes of data in a single table,
>
> It may be worth mentioning what Nikita said above about updates.

I've tried (with the above statement with visible and invisible tuples).

> +  especially if you have a wide range of value widths.
>
> I never understood this part.

I've changed it, but I wonder if the new "large number of wide
columns" isn't too ambiguous due to "large" (?)

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

I've wrongly put it, I've meant that pg_largeobject also consume OID
and as such are subject to 32TB limit.

>
> +   <row>
> +     <entry>large objects number</entry>
>
> "large objects per database"

Fixed.

> +     <entry>subject to the same limitations as <symbol>rows per table</symbol></entry>
>
> That implies table size is the only factor. Max OID is also a factor, which was your stated reason to include LOs
herein the first place.
 

Exactly..

Regards,
-Jakub Wartak.