| 1 |
<!-- doc/src/sgml/limits.sgml --> |
| 2 |
|
| 3 |
<appendix id="limits"> |
| 4 |
<title><productname>PostgreSQL</productname> Limits</title> |
| 5 |
|
| 6 |
<para> |
| 7 |
<xref linkend="limits-table"/> describes various hard limits of |
| 8 |
<productname>PostgreSQL</productname>. However, practical limits, such as |
| 9 |
performance limitations or available disk space may apply before absolute |
| 10 |
hard limits are reached. |
| 11 |
</para> |
| 12 |
|
| 13 |
<table id="limits-table"> |
| 14 |
<title><productname>PostgreSQL</productname> Limitations</title> |
| 15 |
<tgroup cols="3"> |
| 16 |
<thead> |
| 17 |
<row> |
| 18 |
<entry>Item</entry> |
| 19 |
<entry>Upper Limit</entry> |
| 20 |
<entry>Comment</entry> |
| 21 |
</row> |
| 22 |
</thead> |
| 23 |
|
| 24 |
<tbody> |
| 25 |
<row> |
| 26 |
<entry>database size</entry> |
| 27 |
<entry>unlimited</entry> |
| 28 |
<entry></entry> |
| 29 |
</row> |
| 30 |
|
| 31 |
<row> |
| 32 |
<entry>number of databases</entry> |
| 33 |
<!-- 2^32 - FirstNormalObjectId - 1 --> |
| 34 |
<entry>4,294,950,911</entry> |
| 35 |
<entry></entry> |
| 36 |
</row> |
| 37 |
|
| 38 |
<row> |
| 39 |
<entry>relations per database</entry> |
| 40 |
<!-- (2^32 - FirstNormalObjectId - 1) / 3 (3 because of the table and the |
| 41 |
two types that are created to go with it) --> |
| 42 |
<entry>1,431,650,303</entry> |
| 43 |
<entry></entry> |
| 44 |
</row> |
| 45 |
|
| 46 |
<row> |
| 47 |
<entry>relation size</entry> |
| 48 |
<entry>32 TB</entry> |
| 49 |
<entry>with the default <symbol>BLCKSZ</symbol> of 8192 bytes</entry> |
| 50 |
</row> |
| 51 |
|
| 52 |
<row> |
| 53 |
<entry>rows per table</entry> |
| 54 |
<entry>limited by the number of tuples that can fit onto 4,294,967,295 pages</entry> |
| 55 |
<entry></entry> |
| 56 |
</row> |
| 57 |
|
| 58 |
<row> |
| 59 |
<entry>columns per table</entry> |
| 60 |
<entry>1,600</entry> |
| 61 |
<entry>further limited by tuple size fitting on a single page; see note |
| 62 |
below</entry> |
| 63 |
</row> |
| 64 |
|
| 65 |
<row> |
| 66 |
<entry>columns in a result set</entry> |
| 67 |
<entry>1,664</entry> |
| 68 |
<entry></entry> |
| 69 |
</row> |
| 70 |
|
| 71 |
<row> |
| 72 |
<entry>field size</entry> |
| 73 |
<entry>1 GB</entry> |
| 74 |
<entry></entry> |
| 75 |
</row> |
| 76 |
|
| 77 |
<row> |
| 78 |
<entry>indexes per table</entry> |
| 79 |
<entry>unlimited</entry> |
| 80 |
<entry>constrained by maximum relations per database</entry> |
| 81 |
</row> |
| 82 |
|
| 83 |
<row> |
| 84 |
<entry>columns per index</entry> |
| 85 |
<entry>32</entry> |
| 86 |
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry> |
| 87 |
</row> |
| 88 |
|
| 89 |
<row> |
| 90 |
<entry>partition keys</entry> |
| 91 |
<entry>32</entry> |
| 92 |
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry> |
| 93 |
</row> |
| 94 |
|
| 95 |
<row> |
| 96 |
<entry>identifier length</entry> |
| 97 |
<entry>63 bytes</entry> |
| 98 |
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry> |
| 99 |
</row> |
| 100 |
|
| 101 |
<row> |
| 102 |
<entry>function arguments</entry> |
| 103 |
<entry>100</entry> |
| 104 |
<entry>can be increased by recompiling <productname>PostgreSQL</productname></entry> |
| 105 |
</row> |
| 106 |
|
| 107 |
<row> |
| 108 |
<entry>query parameters</entry> |
| 109 |
<entry>65,535</entry> |
| 110 |
<entry></entry> |
| 111 |
</row> |
| 112 |
</tbody> |
| 113 |
</tgroup> |
| 114 |
</table> |
| 115 |
|
| 116 |
<para> |
| 117 |
The maximum number of columns for a table is further reduced as the tuple |
| 118 |
being stored must fit in a single 8192-byte heap page. For example, |
| 119 |
excluding the tuple header, a tuple made up of 1,600 <type>int</type> columns |
| 120 |
would consume 6400 bytes and could be stored in a heap page, but a tuple of |
| 121 |
1,600 <type>bigint</type> columns would consume 12800 bytes and would |
| 122 |
therefore not fit inside a heap page. |
| 123 |
Variable-length fields of |
| 124 |
types such as <type>text</type>, <type>varchar</type>, and <type>char</type> |
| 125 |
can have their values stored out of line in the table's TOAST table when the |
| 126 |
values are large enough to require it. Only an 18-byte pointer must remain |
| 127 |
inside the tuple in the table's heap. For shorter length variable-length |
| 128 |
fields, either a 4-byte or 1-byte field header is used and the value is |
| 129 |
stored inside the heap tuple. |
| 130 |
</para> |
| 131 |
|
| 132 |
<para> |
| 133 |
Columns that have been dropped from the table also contribute to the maximum |
| 134 |
column limit. Moreover, although the dropped column values for newly |
| 135 |
created tuples are internally marked as null in the tuple's null bitmap, the |
| 136 |
null bitmap also occupies space. |
| 137 |
</para> |
| 138 |
|
| 139 |
<para> |
| 140 |
Each table can store a theoretical maximum of 2^32 out-of-line values; see |
| 141 |
<xref linkend="storage-toast" /> for a detailed discussion of out-of-line |
| 142 |
storage. This limit arises from the use of a 32-bit OID to identify each |
| 143 |
such value. The practical limit is significantly less than the theoretical |
| 144 |
limit, because as the OID space fills up, finding an OID that is still free |
| 145 |
can become expensive, in turn slowing down INSERT/UPDATE statements. |
| 146 |
Typically, this is only an issue for tables containing many terabytes |
| 147 |
of data; partitioning is a possible workaround. |
| 148 |
</para> |
| 149 |
</appendix> |