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 TOASTed values</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 do anything 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 documented elsewhere.

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 here in the first place.

Exactly..

Regards,
-Jakub Wartak.