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

On Wed, Apr 26, 2023 at 5:18 PM Jakub Wartak <jakub(dot)wartak(at)enterprisedb(dot)com>
wrote:

> OK, so here is the documentation patch proposal. I've also added two
> rows touching the subject of pg_largeobjects, as it is also related to
> the OIDs topic.

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

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

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.

The new note needs a lot of editing to fit its new home. For starters:

+ <para>
+ For every TOAST-ed columns

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

+ 2^32

Perhaps it should match full numbers elsewhere in the page.

+(unsigned integer;

True but irrelevant.

+ 4 billion)

Imprecise and redundant.

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

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

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

Out of place.

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

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

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

+ It's also worth emphasizing that

Unnecessary.

+ only field
+ values wider than 2KB

TOAST_TUPLE_TARGET

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

+ especially if you have a wide range of value widths.

I never understood this part.

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

+ <row>
+ <entry>large objects number</entry>

"large objects per database"

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

+ <entry>LOs are stored in single pg_largeobjects relation</entry>

I would just say "also limited by relation size".

(note: Our catalogs are named in the singular.)

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