v1-0001-doc-Add-some-OID-TOAST-related-limitations-to-the.patch

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