Postgresql 中文操作指南

Appendix K. PostgreSQL Limits

Table K.1 描述了 PostgreSQL 的各种硬限制。但是，实际上在达到绝对硬限制之前，可能受到性能限制或可用磁盘空间等实际限制。

Table K.1 describes various hard limits of PostgreSQL. However, practical limits, such as performance limitations or available disk space may apply before absolute hard limits are reached.

Table K.1. PostgreSQL Limitations

Item

Upper Limit

Comment

database size

unlimited

number of databases

4,294,950,911

relations per database

1,431,650,303

relation size

32 TB

with the default BLCKSZ of 8192 bytes

rows per table

limited by the number of tuples that can fit onto 4,294,967,295 pages

columns per table

1,600

further limited by tuple size fitting on a single page; see note below

columns in a result set

1,664

field size

1 GB

indexes per table

unlimited

constrained by maximum relations per database

columns per index

can be increased by recompiling PostgreSQL

partition keys

can be increased by recompiling PostgreSQL

identifier length

63 bytes

can be increased by recompiling PostgreSQL

function arguments

100

can be increased by recompiling PostgreSQL

query parameters

65,535

表的最大列数进一步减少的原因为，要存储的元组必须安装在一个 8192 字节的堆页中。例如，排除元组头，由 1,600 个 int 列构成的元组会占用 6400 字节，可以存储在堆页中，但由 1,600 个 bigint 列构成的元组将占用 12800 字节，因此不适合放在堆页中。类型为 text 、 varchar 和 char 等的可变长度字段的值，当值大到需要时，可以将其值存储在表中的 TOAST 表中。元组在表堆中只能保留一个 18 字节的指针。对于较短的可变长度字段，可以使用 4 字节或 1 字节字段头并将值存储在堆元组中。

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 1,600 int columns would consume 6400 bytes and could be stored in a heap page, but a tuple of 1,600 bigint columns would consume 12800 bytes and would therefore not fit inside a heap page. Variable-length fields of types such as text, varchar, and char can have their values stored out of line in the table’s TOAST 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.

已从表中删除的列也有助于最大元组数限制。此外，虽然新创建元组的已删列值在元组的位图中被内部标记为 null，但位图也占用空间。

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 null bitmap also occupies space.