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

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