Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns - Mailing list pgsql-hackers
| From | John Naylor |
|---|---|
| Subject | Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns |
| Date | |
| Msg-id | CAFBsxsGwsieS3=Nw35LjufXTC8MK99Z4OT4Sxst4itSzdrgWtQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns (Jakub Wartak <jakub.wartak@enterprisedb.com>) |
| Responses |
Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns
(Jakub Wartak <jakub.wartak@enterprisedb.com>)
|
| List | pgsql-hackers |
On Wed, Apr 26, 2023 at 5:18 PM Jakub Wartak <jakub.wartak@enterprisedb.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.)
> 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.)
pgsql-hackers by date: