如何计算Postgresql中的最大列数

Question

我想知道在postgresql表中计算最大列数的正确方法。他们的网站上说：

每个表的最大列数为250-1600，取决于列类型。

因此，根据列类型，我如何确定最大列数？

- Luke101

---

@vyegorov的答案详细介绍了技术细节。但是，我想指出，如果您担心达到列计数限制，则可能存在模式设计问题。即使在正确规范化的架构中，250个列也应该非常罕见。(Pg可以处理超过250-1600列范围的动态创建结果集，只有磁盘存储受到该值的限制。) - Craig Ringer

Answer 1

你需要深入了解PostgreSQL的物理数据存储细节，主要是页面布局。

如你所知，PostgreSQL的默认块大小为8KB（8192字节）。你还应该知道，在PostgreSQL中，表行不能跨越块边界。这已经给出了8192字节的大小限制。但是……看一下上面的页面布局，还有PageHeader的开销，在当前的PostgreSQL版本中它是24个字节。因此，我们只剩下8168字节。但是……还有一个指针数组ItemIdData。假设这个页面只有1条记录，因此这个条目只占用4个字节（1个条目）。因此，我们只剩下8164字节。但是……每个记录还有一个RecordHeader，占用23个字节。因此，我们只剩下8141个字节。但是……在RecordHeader后面还有一个NULL位图，但假设我们已经定义了所有列都具有NOT NULL约束条件，因此这里仍然是8141个字节。但是……还有一个叫做MAXALIGN的东西。看看Erwin提供的这个精彩答案。我们在这里讲述的是24+4+23=51的偏移量。现在一切都取决于您系统上这个参数的值。如果它是32位的，则偏移量将对齐到52，这意味着我们会浪费一个字节。如果它是64位的，则偏移量将对齐到54，这意味着我们会浪费3个字节。我的系统是64位的，因此我认为我们只剩下8138个字节。

所以现在我们剩下了这个空间。现在一切都取决于我们选择的列的类型以及它们如何组合在一起（记住MAXALIGN）。让我们为所有列选择int2。简单的计算表明，我们应该能够挤入4069个这种类型的列：所有列都是NOT NULL并且相同类型。

简单脚本：

echo "CREATE TABLE tab4069 (" > tab4069.sql
for num in $(seq -f "%04g" 1 4069); do
  echo "    col$num  int2 not null," >> tab4069.sql; done
echo "    PRIMARY KEY (col0001) );" >> tab4069.sql

然而，如果您尝试创建此表格，将会遇到以下错误：

错误：表格最多只能有1600列

通过一些搜索，我们找到了类似问题，并查看了PostgreSQL的源代码，我们得到了答案（第23至47行）：

/*
 * MaxTupleAttributeNumber limits the number of (user) columns in a tuple.
 * The key limit on this value is that the size of the fixed overhead for
 * a tuple, plus the size of the null-values bitmap (at 1 bit per column),
 * plus MAXALIGN alignment, must fit into t_hoff which is uint8.  On most
 * machines the upper limit without making t_hoff wider would be a little
 * over 1700.  We use round numbers here and for MaxHeapAttributeNumber
 * so that alterations in HeapTupleHeaderData layout won't change the
 * supported max number of columns.
 */
#define MaxTupleAttributeNumber 1664        /* 8 * 208 */

/*
 * MaxHeapAttributeNumber limits the number of (user) columns in a table.
 * This should be somewhat less than MaxTupleAttributeNumber.  It must be
 * at least one less, else we will fail to do UPDATEs on a maximal-width
 * table (because UPDATE has to form working tuples that include CTID).
 * In practice we want some additional daylight so that we can gracefully
 * support operations that add hidden "resjunk" columns, for example
 * SELECT * FROM wide_table ORDER BY foo, bar, baz.
 * In any case, depending on column data types you will likely be running
 * into the disk-block-based limit on overall tuple size if you have more
 * than a thousand or so columns.  TOAST won't help.
 */
#define MaxHeapAttributeNumber  1600       /* 8 * 200 */

有很多可变长度类型，它们固定的开销为1或4个字节+实际值中的一些字节。这意味着你在拥有实际值之前永远不会知道记录需要多少空间。当然，这些值可能会通过TOAST分别存储，但通常是更大的值(总长度约2KB)。

请参考官方文档中有关类型的内容，以了解固定长度类型所使用的空间。您还可以检查pg_column_size()函数对于任何类型的输出，特别是对于复杂类型，如数组、hstore或jsonb。

如果您想要更完整的了解此主题，您将需要深入了解更多细节。

- vyegorov

---

在考虑哪些列可以进行TOAST处理时，还需要处理存储类的问题。 - Craig Ringer

哇，非常有启发性。不过，在我能理解你所写的一切之前，我必须先了解postgresql技术。 - Luke101