Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns
Hi -hackers,
I would like to ask if it wouldn't be good idea to copy the
https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit
discussion (out-of-line OID usage per TOAST-ed columns / potential
limitation) to the official "Appendix K. PostgreSQL Limits" with also
little bonus mentioning the "still searching for an unused OID in
relation" notice. Although it is pretty obvious information for some
and from commit 7fbcee1b2d5f1012c67942126881bd492e95077e and the
discussion in [1]/messages/by-id/16722-93043fb459a41073@postgresql.org, I wonder if the information shouldn't be a little
more well known via the limitation (especially to steer people away
from designing very wide non-partitioned tables).
Regards,
-J.
Hi!
This limitation applies not only to wide tables - it also applies to tables
where TOASTed values
are updated very often. You would soon be out of available TOAST value ID
because in case of
high frequency updates autovacuum cleanup rate won't keep up with the
updates. It is discussed
in [1]/messages/by-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd=+RdMPFTyt-bRQ@mail.gmail.com.
On Fri, Apr 21, 2023 at 9:39 AM Jakub Wartak <jakub.wartak@enterprisedb.com>
wrote:
Hi -hackers,
I would like to ask if it wouldn't be good idea to copy the
https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit
discussion (out-of-line OID usage per TOAST-ed columns / potential
limitation) to the official "Appendix K. PostgreSQL Limits" with also
little bonus mentioning the "still searching for an unused OID in
relation" notice. Although it is pretty obvious information for some
and from commit 7fbcee1b2d5f1012c67942126881bd492e95077e and the
discussion in [1], I wonder if the information shouldn't be a little
more well known via the limitation (especially to steer people away
from designing very wide non-partitioned tables).Regards,
-J.
[1]: /messages/by-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd=+RdMPFTyt-bRQ@mail.gmail.com
/messages/by-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd=+RdMPFTyt-bRQ@mail.gmail.com
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
On Fri, Apr 21, 2023 at 12:14 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
This limitation applies not only to wide tables - it also applies to tables where TOASTed values
are updated very often. You would soon be out of available TOAST value ID because in case of
high frequency updates autovacuum cleanup rate won't keep up with the updates. It is discussed
in [1].On Fri, Apr 21, 2023 at 9:39 AM Jakub Wartak <jakub.wartak@enterprisedb.com> wrote:
I would like to ask if it wouldn't be good idea to copy the
https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit
discussion (out-of-line OID usage per TOAST-ed columns / potential
limitation) to the official "Appendix K. PostgreSQL Limits" with also
little bonus mentioning the "still searching for an unused OID in
relation" notice. Although it is pretty obvious information for some
and from commit 7fbcee1b2d5f1012c67942126881bd492e95077e and the
discussion in [1], I wonder if the information shouldn't be a little
more well known via the limitation (especially to steer people away
from designing very wide non-partitioned tables).[1] /messages/by-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd=+RdMPFTyt-bRQ@mail.gmail.com
These 2 discussions show that it's a painful experience to run into
this problem, and that the hackers have ideas on how to fix it, but
those fixes haven't materialized for years. So I would say that, yes,
this info belongs in the hard-limits section, because who knows how
long it'll take this to be fixed.
Please submit a patch.
I anticipate that edits to Appendix K Postgres Limits will prompt
improving the note in there about the maximum column limit, That note
is too wordy, and sometimes confusing, especially for the audience
that it's written for: newcomers to Postgres ecosystem.
Best regards,
Gurjeet https://Gurje.et
http://aws.amazon.com
Hi,
This is a production case for large databases with high update rates, but
is mistaken
with reaching table size limit, although size limit is processed correctly.
The note on TOAST limitation does not mention that TOAST values are not
actually
updated on UPDATE operation - old value is marked as dead and new one is
inserted,
and dead values should be vacuumed before value OID could be reused. The
worst
is that the INSERT/UPDATE clause does not fail if there is no OID available
- it is
looped in an infinite loop of sorting out OIDs.
On Sat, Apr 22, 2023 at 6:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:
On Fri, Apr 21, 2023 at 12:14 AM Nikita Malakhov <hukutoc@gmail.com>
wrote:This limitation applies not only to wide tables - it also applies to
tables where TOASTed values
are updated very often. You would soon be out of available TOAST value
ID because in case of
high frequency updates autovacuum cleanup rate won't keep up with the
updates. It is discussed
in [1].
On Fri, Apr 21, 2023 at 9:39 AM Jakub Wartak <
jakub.wartak@enterprisedb.com> wrote:
I would like to ask if it wouldn't be good idea to copy the
https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit
discussion (out-of-line OID usage per TOAST-ed columns / potential
limitation) to the official "Appendix K. PostgreSQL Limits" with also
little bonus mentioning the "still searching for an unused OID in
relation" notice. Although it is pretty obvious information for some
and from commit 7fbcee1b2d5f1012c67942126881bd492e95077e and the
discussion in [1], I wonder if the information shouldn't be a little
more well known via the limitation (especially to steer people away
from designing very wide non-partitioned tables).[1] -
/messages/by-id/16722-93043fb459a41073@postgresql.org
[1]
/messages/by-id/CAN-LCVPRvRzxeUdYdDCZ7UwZQs1NmZpqBUCd=+RdMPFTyt-bRQ@mail.gmail.com
These 2 discussions show that it's a painful experience to run into
this problem, and that the hackers have ideas on how to fix it, but
those fixes haven't materialized for years. So I would say that, yes,
this info belongs in the hard-limits section, because who knows how
long it'll take this to be fixed.Please submit a patch.
I anticipate that edits to Appendix K Postgres Limits will prompt
improving the note in there about the maximum column limit, That note
is too wordy, and sometimes confusing, especially for the audience
that it's written for: newcomers to Postgres ecosystem.Best regards,
Gurjeet https://Gurje.et
http://aws.amazon.com
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Hi,
These 2 discussions show that it's a painful experience to run into
this problem, and that the hackers have ideas on how to fix it, but
those fixes haven't materialized for years. So I would say that, yes,
this info belongs in the hard-limits section, because who knows how
long it'll take this to be fixed.Please submit a patch.
This is a production case for large databases with high update rates, but is mistaken
with reaching table size limit, although size limit is processed correctly.The note on TOAST limitation does not mention that TOAST values are not actually
updated on UPDATE operation - old value is marked as dead and new one is inserted,
and dead values should be vacuumed before value OID could be reused. The worst
is that the INSERT/UPDATE clause does not fail if there is no OID available - it is
looped in an infinite loop of sorting out OIDs.
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. Please feel free to send adjusted patches.
Regards,
-J.
Attachments:
v1-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchapplication/octet-stream; name=v1-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchDownload
From d97ed87e0f938b26446cc8f1f136517d535d8897 Mon Sep 17 00:00:00 2001
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 26 Apr 2023 12:07:42 +0200
Subject: [PATCH v1] doc: Add some OID/TOAST-related limitations to the limits
appendix.
Although https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit references
some OID/TOAST-related limitations, those are not very clear from the official
documentation. Put some information into Limits for better transparency.
Discussion: https://www.postgresql.org/message-id/flat/CAKZiRmwWhp2yxjqJLwbBjHdfbJBcUmmKMNAZyBjjtpgM9AMatQ%40mail.gmail.com
---
doc/src/sgml/limits.sgml | 51 ++++++++++++++++++++++++++++++++++++----
1 file changed, 46 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index d5b2b627dd..60de39dbc0 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>
+
<table id="limits-table">
<title><productname>PostgreSQL</productname> Limitations</title>
<tgroup cols="3">
@@ -51,8 +52,8 @@
<row>
<entry>rows per table</entry>
- <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>
</row>
<row>
@@ -93,10 +94,23 @@
</row>
<row>
- <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>
+ </row>
+
+ <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>
+
+ <row>
+ <entry>large objects number</entry>
+ <entry>subject to the same limitations as <symbol>rows per table</symbol></entry>
+ <entry>LOs are stored in single pg_largeobjects relation</entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -123,4 +137,31 @@
created tuples are internally marked as null in the tuple's null bitmap, the
null bitmap also occupies space.
</para>
+
+ <para>
+ For every TOAST-ed columns (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 (unsigned integer;
+ 4 billion) out-of-line values in a single table, because there would have to be
+ duplicated OIDs in its TOAST table. Please note that that the limit of 2^32
+ out-of-line TOAST values applies to the sum of both visible and invisible tuples.
+ It is therefore crucial that the autovacuum manages to keep up with cleaning the
+ bloat and free the unused OIDs.
+ </para>
+
+ <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. 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. Therefore,
+ the OID shortages may (in very extreme cases) cause slowdowns to the
+ INSERTs/UPDATE/COPY statements. It's also worth emphasizing that only field
+ values wider than 2KB will consume TOAST OIDs in this way. So, in practice,
+ reaching this limit would require many terabytes of data in a single table,
+ especially if you have a wide range of value widths. Partitioning your table
+ is a possible workaround.
+ </para>
+
</appendix>
--
2.30.2
On Sun, 23 Apr 2023, 3:42 am Gurjeet Singh, <gurjeet@singh.im> wrote:
I anticipate that edits to Appendix K Postgres Limits will prompt
improving the note in there about the maximum column limit, That note
is too wordy, and sometimes confusing, especially for the audience
that it's written for: newcomers to Postgres ecosystem.
I doubt it, but feel free to submit a patch yourself which improves it
without losing the information which the paragraph is trying to convey.
David
Show quoted text
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.)
--
John Naylor
EDB: http://www.enterprisedb.com
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 columnscolumn
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 2KBTOAST_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.
Attachments:
v2-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchapplication/octet-stream; name=v2-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchDownload
From d812dbb3623317f4ff01687c5dcf7380b36c83f6 Mon Sep 17 00:00:00 2001
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 26 Apr 2023 12:07:42 +0200
Subject: [PATCH v2] doc: Add some OID/TOAST-related limitations to the limits
appendix.
Although https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit references
some OID/TOAST-related limitations, those are not very clear from the official
documentation. Put some information into Limits for better transparency.
Discussion: https://www.postgresql.org/message-id/flat/CAKZiRmwWhp2yxjqJLwbBjHdfbJBcUmmKMNAZyBjjtpgM9AMatQ%40mail.gmail.com
---
doc/src/sgml/limits.sgml | 51 +++++++++++++++++++++++++++++++++++-----
1 file changed, 45 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index d5b2b627dd..8c9617861c 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>
+
<table id="limits-table">
<title><productname>PostgreSQL</productname> Limitations</title>
<tgroup cols="3">
@@ -52,7 +53,7 @@
<row>
<entry>rows per table</entry>
<entry>limited by the number of tuples that can fit onto 4,294,967,295 pages</entry>
- <entry></entry>
+ <entry>see note below on TOAST</entry>
</row>
<row>
@@ -92,11 +93,24 @@
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
</row>
- <row>
- <entry>partition keys</entry>
- <entry>32</entry>
- <entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
- </row>
+ <row>
+ <entry>partition keys</entry>
+ <entry>32</entry>
+ <entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
+ </row>
+
+ <row>
+ <entry>maximum large object size</entry>
+ <entry>4TB</entry>
+ <entry>see <xref linkend="lo-intro"/></entry>
+ </row>
+
+ <row>
+ <entry>large objects size per database</entry>
+ <entry>subject to the same limitations as <symbol>rows per table</symbol> and <symbol>relation size</symbol></entry>
+ <entry></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -123,4 +137,29 @@
created tuples are internally marked as null in the tuple's null bitmap, the
null bitmap also occupies space.
</para>
+
+ <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>
+ In practice, you want to have considerably less than that many TOASTed values
+ per table, because as the OID space fills up, the system will spend larger
+ amounts of time searching for the next free OID when it needs to generate a new
+ out-of-line value (The search for free OIDs won't stop until it finds the free OID).
+ Therefore, the OID shortages will eventually cause slowdowns to the
+ INSERTs/UPDATE/COPY statements. Please note that that the limit of 4,294,967,296
+ out-of-line TOAST values applies to the sum of both visible and invisible tuples.
+ It is therefore crucial that the autovacuum manages to keep up with cleaning the
+ bloat and deallocating the OIDs used by out-of-line TOAST.
+
+ Only field values wider than 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, especially with large number of wide columns. Partitioning your
+ table is a possible workaround.
+ </para>
+
</appendix>
--
2.30.2
On Thu, Apr 27, 2023 at 7:36 PM Jakub Wartak <jakub.wartak@enterprisedb.com>
wrote:
Spurious whitespace.
Hopefully fixed, I've tried to align with the other entries tags.
Hope springs eternal. ;-)
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>+
<table id="limits-table">
@@ -92,11 +93,24 @@
<entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
</row>
- <row>
- <entry>partition keys</entry>
- <entry>32</entry>
- <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
- </row>
+ <row>
+ <entry>partition keys</entry>
+ <entry>32</entry>
+ <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
+ </row>- <entry></entry>
+ <entry>see note below on TOAST</entry>Maybe:
"further limited by the number of TOAST-ed values; see note below"
+ <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.
No, OID has nothing to do with the table size limit, they have to do with
the max number of LOs in a DB.
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.
+ <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". Also the shared counter is the cause of the
slowdown, but not the reason for the numeric limit. "Today" is irrelevant.
Needs polish.
+ 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.
+ 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?
+ 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."
+ 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.
--
John Naylor
EDB: http://www.enterprisedb.com
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.
Attachments:
v3-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchapplication/octet-stream; name=v3-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patchDownload
From bfaddc3a08ca75acc8023d76768ece4cb09e7585 Mon Sep 17 00:00:00 2001
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 5 Jul 2023 16:32:37 +0200
Subject: [PATCH v3] doc: Add some OID/TOAST-related limitations to the limits
appendix
Although https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit references
some OID/TOAST-related limitations, those are not very clear from the official
documentation. Put some information into Limits for better transparency.
Discussion: https://www.postgresql.org/message-id/flat/CAKZiRmwWhp2yxjqJLwbBjHdfbJBcUmmKMNAZyBjjtpgM9AMatQ%40mail.gmail.com
---
doc/src/sgml/limits.sgml | 47 +++++++++++++++++++++++++++++++++++-----
1 file changed, 41 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index d5b2b627dd..d92341de3b 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>
+
<table id="limits-table">
<title><productname>PostgreSQL</productname> Limitations</title>
<tgroup cols="3">
@@ -52,7 +53,7 @@
<row>
<entry>rows per table</entry>
<entry>limited by the number of tuples that can fit onto 4,294,967,295 pages</entry>
- <entry></entry>
+ <entry>further limited by the number of TOAST-ed values; see note below</entry>
</row>
<row>
@@ -92,11 +93,25 @@
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
</row>
- <row>
- <entry>partition keys</entry>
- <entry>32</entry>
- <entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
- </row>
+ <row>
+ <entry>partition keys</entry>
+ <entry>32</entry>
+ <entry>can be increased by recompiling <productname>PostgreSQL</productname></entry>
+ </row>
+
+ <row>
+ <entry>maximum large object size</entry>
+ <entry>4TB</entry>
+ <entry>see <xref linkend="lo-intro"/></entry>
+ </row>
+
+ <row>
+ <entry>large objects size per database</entry>
+ <entry>subject to the same limitations as <symbol>rows per table</symbol> and <symbol>relation size</symbol>
+ (32 TB by default)</entry>
+ <entry></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -123,4 +138,24 @@
created tuples are internally marked as null in the tuple's null bitmap, the
null bitmap also occupies space.
</para>
+
+ <para>
+ For every TOAST-ed column (that is for columns wider than TOAST_TUPLE_TARGET
+ [2040 bytes by default]), due to internal PostgreSQL implementation of using one
+ shared global OID counter - you cannot have more than 4,294,967,296 out-of-line
+ values in a single table.
+ </para>
+
+ <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 will spend larger
+ amounts of time searching for the next free OID when it needs to generate a new
+ out-of-line value. This will eventually cause slowdowns for INSERT, UPDATE, and
+ COPY statements.
+
+ Only column values wider than 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, especially with large number of wide columns.
+ </para>
+
</appendix>
--
2.30.2
On Wed, Jul 5, 2023 at 9:45 PM Jakub Wartak <jakub.wartak@enterprisedb.com>
wrote:
[v3]
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -10,6 +10,7 @@
hard limits are reached.
</para>+
<table id="limits-table">
@@ -92,11 +93,25 @@
<entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
</row>
- <row>
- <entry>partition keys</entry>
- <entry>32</entry>
- <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
- </row>
+ <row>
+ <entry>partition keys</entry>
+ <entry>32</entry>
+ <entry>can be increased by recompiling
<productname>PostgreSQL</productname></entry>
+ </row>Ahem.
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.
I see no argument here against splitting into a separate patch for later.
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 (?)
Looking again, I'm thinking the OID type size is more relevant for the
first paragraph, and the shared/global aspect is more relevant for the
second.
The last issue is how to separate the notes at the bottom, since there are
now two topics.
--
John Naylor
EDB: http://www.enterprisedb.com
On Wed, Apr 26, 2023 at 4:48 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Sun, 23 Apr 2023, 3:42 am Gurjeet Singh, <gurjeet@singh.im> wrote:
I anticipate that edits to Appendix K Postgres Limits will prompt
improving the note in there about the maximum column limit, That note
is too wordy, and sometimes confusing, especially for the audience
that it's written for: newcomers to Postgres ecosystem.I doubt it, but feel free to submit a patch yourself which improves it without losing the information which the paragraph is trying to convey.
I could not think of a way to reduce the wordiness without losing
information. But since this page is usually consulted by those who are
new to Postgres, usually sent here by a search engine, I believe the
page can be improved for that audience, without losing much in terms
of accuracy.
I agree the information provided in the paragraph about max-columns is
pertinent. But since the limits section is most often consulted by
people migrating from other database systems (hence the claim that
they're new to the Postgres ecosystem), I imagine the terminology used
there may cause confusion for the reader. So my suggestion is to make
that paragraph, and perhaps even that page, use fewer hacker/internals
terms.
Technically, there may be a difference between table vs. relation, row
vs. tuple, and column vs. field. But using those terms, seemingly
interchangeably on that page does not help the reader. The page
neither describes the terms, nor links to their definitions, so a
reader is left with more questions than before. For example,
rows per table:: limited by the number of tuples that can fit onto 4,294,967,295 pages
A newcomer> what's a tuple in this context, and how is it similar
to/different from a row?
Please see attached the proposed patch, which attempts to make that
language newcomer-friendly. The patch adds one link for TOAST, and
replaces Postgres-specific terms with generic ones.
PS: I've retained line boundaries, so that `git diff --color-words
doc/src/sgml/limits.sgml` would make it easy to see the changes.
Best regards,
Gurjeet
http://Gurje.et
Attachments:
limits-generic-terms.diffapplication/octet-stream; name=limits-generic-terms.diffDownload
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index d5b2b62..fe1b138 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -51,14 +51,14 @@
<row>
<entry>rows per table</entry>
- <entry>limited by the number of tuples that can fit onto 4,294,967,295 pages</entry>
+ <entry>limited by the number of rows that can fit onto 4,294,967,295 pages</entry>
<entry></entry>
</row>
<row>
<entry>columns per table</entry>
<entry>1600</entry>
- <entry>further limited by tuple size fitting on a single page; see note
+ <entry>further limited by row size fitting on a single page; see note
below</entry>
</row>
@@ -102,25 +102,29 @@
</table>
<para>
- The maximum number of columns for a table is further reduced as the tuple
- being stored must fit in a single 8192-byte heap page. For example,
- excluding the tuple header, a tuple made up of 1600 <type>int</type> columns
- would consume 6400 bytes and could be stored in a heap page, but a tuple of
+ Tables and indexes, both, are referred to as relations in Postgres code.
+ </para>
+
+ <para>
+ The maximum number of columns for a table is further reduced as the row
+ being stored must fit in a single database block (usually 8192-bytes). For example,
+ excluding the row-header, a row made up of 1600 <type>int</type> columns
+ would consume 6400 bytes and could be stored in a database block, but a row of
1600 <type>bigint</type> columns would consume 12800 bytes and would
- therefore not fit inside a heap page.
- Variable-length fields of
+ therefore not fit inside a database block.
+ Variable-length columns of
types such as <type>text</type>, <type>varchar</type>, and <type>char</type>
- can have their values stored out of line in the table's TOAST table when the
+ can have their values stored out of line in the table's <link linkend="storage-toast"><acronym>TOAST</acronym></link> table when the
values are large enough to require it. Only an 18-byte pointer must remain
- inside the tuple in the table's heap. For shorter length variable-length
- fields, either a 4-byte or 1-byte field header is used and the value is
- stored inside the heap tuple.
+ inside the row in the table's storage. For shorter length variable-length
+ columns, either a 4-byte or 1-byte column header is used and the value is
+ stored inside the row.
</para>
<para>
Columns that have been dropped from the table also contribute to the maximum
column limit. Moreover, although the dropped column values for newly
- created tuples are internally marked as null in the tuple's null bitmap, the
+ created rows are internally marked as null in the rows's null bitmap, the
null bitmap also occupies space.
</para>
</appendix>
On Mon, Aug 21, 2023 at 1:33 PM Gurjeet Singh <gurjeet@singh.im> wrote:
Please see attached the proposed patch, which attempts to make that
language newcomer-friendly. The patch adds one link for TOAST, and
replaces Postgres-specific terms with generic ones.
This is off-topic for this thread (which has a CF entry), and overall I
don't find the changes to be an improvement. (It wouldn't hurt to link to
the TOAST section, though.)
--
John Naylor
EDB: http://www.enterprisedb.com
On 8 Aug 2023, at 12:31, John Naylor <john.naylor@enterprisedb.com> wrote:
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 (?)
Looking again, I'm thinking the OID type size is more relevant for the first paragraph, and the shared/global aspect is more relevant for the second.
The last issue is how to separate the notes at the bottom, since there are now two topics.
Jakub, do you have plans to address this feedback? Is the CF entry still relevant?
Thanks!
Best regards, Andrey Borodin.
Hi Andrey,
On Thu, Mar 28, 2024 at 1:09 PM Andrey M. Borodin <x4mmm@yandex-team.ru> wrote:
On 8 Aug 2023, at 12:31, John Naylor <john.naylor@enterprisedb.com> wrote:
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 (?)
Looking again, I'm thinking the OID type size is more relevant for the first paragraph, and the shared/global aspect is more relevant for the second.
The last issue is how to separate the notes at the bottom, since there are now two topics.
Jakub, do you have plans to address this feedback? Is the CF entry still relevant?
Yes; I've forgotten about this one and clearly I had problems
formulating it in proper shape to be accepted. I've moved it to the
next CF now as this is not critical and I would prefer to help current
timesenistive CF. Anyone is welcome to help amend the patch...
-J.
On Wed, Apr 3, 2024 at 4:59 AM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
Yes; I've forgotten about this one and clearly I had problems
formulating it in proper shape to be accepted. I've moved it to the
next CF now as this is not critical and I would prefer to help current
timesenistive CF. Anyone is welcome to help amend the patch...
I looked at your version and wrote something that is shorter and
doesn't touch any existing text. Here it is.
--
Robert Haas
EDB: http://www.enterprisedb.com
Attachments:
toastlimit.patchapplication/octet-stream; name=toastlimit.patchDownload
diff --git a/doc/src/sgml/limits.sgml b/doc/src/sgml/limits.sgml
index c549447013..1571761f15 100644
--- a/doc/src/sgml/limits.sgml
+++ b/doc/src/sgml/limits.sgml
@@ -135,4 +135,14 @@
created tuples are internally marked as null in the tuple's null bitmap, the
null bitmap also occupies space.
</para>
+
+ <para>
+ Each table can store a theoretical maximum of 2^32 out-of-line values; see
+ <xref linkend="storage-toast" /> for a detailed discussion of out-of-line
+ storage. This limit arises from the use of a 32-bit OID to identify each
+ such value. The practical limit is significantly less than the theoretical
+ limit, because as the OID space fills up, finding an OID that is still free
+ can become expensive. Typically, this is only an issue for tables containing
+ many terabytes of data; partitioning is a possible workaround.
+ </para>
</appendix>
On Tue, May 14, 2024 at 8:19 PM Robert Haas <robertmhaas@gmail.com> wrote:
I looked at your version and wrote something that is shorter and
doesn't touch any existing text. Here it is.
Hi Robert, you are a real tactician here - thanks for whatever
references the original problem! :) Maybe just slight hint nearby
expensive (to me indicating a just a CPU problem?):
finding an OID that is still free can become expensive ->
finding an OID that is still free can become expensive, thus
significantly increasing INSERT/UPDATE response time.
? (this potentially makes it easier in future to pinpoint the user's
problem to the this exact limitation; but feel free to ignore that too
as I'm not attached to any of those versions)
-J.
On Mon, May 20, 2024 at 5:43 PM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
On Tue, May 14, 2024 at 8:19 PM Robert Haas <robertmhaas@gmail.com> wrote:
I looked at your version and wrote something that is shorter and
doesn't touch any existing text. Here it is.Hi Robert, you are a real tactician here - thanks for whatever
references the original problem! :)
I like this text as well.
Maybe just slight hint nearby
expensive (to me indicating a just a CPU problem?):finding an OID that is still free can become expensive ->
finding an OID that is still free can become expensive, thus
significantly increasing INSERT/UPDATE response time.? (this potentially makes it easier in future to pinpoint the user's
problem to the this exact limitation; but feel free to ignore that too
as I'm not attached to any of those versions)
Extra explicitness might be good. "Response time" seems like a
networking concept, so possibly ", in turn slowing down INSERT/UPDATE
statements." I'm inclined to commit that way in a couple days, barring
further comments.
PS: Sorry for the delay in looking at the latest messages
On Mon, Aug 12, 2024 at 11:01 AM John Naylor <johncnaylorls@gmail.com> wrote:
Extra explicitness might be good. "Response time" seems like a
networking concept, so possibly ", in turn slowing down INSERT/UPDATE
statements." I'm inclined to commit that way in a couple days, barring
further comments.
This is done.