PostgreSQL中的可空列是否占用额外空间？

Question

我有一个包含7列的表，其中5列将为空值。这些空值将分别出现在 int, text, date, boolean,和 money 数据类型中。该表将包含数百万行数据，其中有很多空值。我担心这些空值会占用空间。

此外，你知道Postgres是否会对空值建立索引吗？我希望防止它对空值进行索引。

- Luke101

Answer 1

基本上，NULL值在NULL位图中占用1位。但情况并非如此简单。

如果行中至少有一个列包含NULL值，则为每行分配一组null bitmap。这可能会导致一个看似自相矛盾的效果，在有9个或更多列的表中：将第一个NULL值赋值给列可能会占用更多磁盘空间，而不是向其写入一个值。反之，从行中删除最后一个NULL值也会删除NULL位图。

实际上，初始的NULL位图在HeapTupleHeader（23字节）和实际列数据或行OID（如果您仍在使用它）之间占用1个字节 - 它们始终以MAXALIGN（通常为8字节）的倍数开始。这留下了1个字节的填充，初始的null bitmap会利用它。

实际上，对于8列或更少的表，NULL存储完全免费（包括已删除但尚未清除的列）。
之后，为下一个MAXALIGN * 8列（通常为64）分配另外MAXALIGN字节（通常为8个）。等等。

更多细节请参见手册以及下面这些相关问题：

• 使用postgresql DB存储NULL值需要多少磁盘空间？
• 在PostgreSQL中不使用NULL仍然使用标题中的NULL位图吗？
• 我可以在Heroku上的5MB PostgreSQL中存储多少条记录？

一旦您理解了数据类型的对齐填充，您还可以进一步优化存储：

• 在PostgreSQL中计算和保存空间

但是能够节省大量空间的情况并不常见。通常不值得花费这个力气。

@Daniel已经讨论了对索引大小的影响。

请注意，删除的列（虽然现在看不见）仍保留在系统目录中，直到表被重新创建。这些"僵尸"可能会强制分配一个（扩大的）NULL位图。详见：

• 在大型数据集上删除Postgres列

- Erwin Brandstetter

---

“NULL存储对于8列或以下的表格是完全免费的。” - 如果只有1列为空，那么这意味着将创建一个仅用于保存1位的1字节空位图吗？ - Dejell

如果存在任何空值，则会有一个空位图，其中包含足够的字节来覆盖所有列，以及填充到下一个MAXALIGN的倍数。 - Erwin Brandstetter

谢谢Erwin。我还不确定 - 如果我没有任何空值呢？它还会在那里吗？（多对多表） - Dejell

@Dejell：如果行中没有空值，那么也不会有空位图。这在上面的答案中已经解释了。请点击链接获取更多详细信息。 - Erwin Brandstetter

为什么文档说标题占用23个字节（在大多数机器上），但表格中长度的总和等于27？ - user1738984

是的，有点混乱。我猜 t_cid ... (overlays with t_xvac) 在这里 意味着这两个项目共享同一个物理字段。源代码中的注释也暗示了这一点：https://doxygen.postgresql.org/htup__details_8h_source.html 第72行。 - Erwin Brandstetter

Answer 2

NULL值能否进入索引，至少取决于索引的类型。基本上，对于btree和gist索引类型，答案是YES；对于hash索引类型，则为NO；而对于gin索引类型，由于PostgreSQL版本的不同，答案似乎是YES或NO。

pg_catalog.pg_am表中曾经有一个布尔列amindexnulls，它包含了这些信息，但在9.1中被删除了。可能是因为随着PG改进，索引变得更加复杂了。

在您的数据的特定情况下，了解最好的方法是使用pg_relation_size('index_name')函数测量索引在完全为NULL和完全为NOT NULL内容之间的大小差异，同时考虑您的确切PG版本、确切数据类型、确切索引类型和定义。请注意，任何这些参数的未来更改都可能会改变结果。

但无论如何，如果您“只是”想避免索引NULL值，那么始终可以创建部分索引：

CREATE INDEX partial_idx(col) ON table WHERE (col is not null)

这将占用更少的空间，但是它是否有助于查询性能取决于这些查询。

- Daniel Vérité

---

+1 洞察力。需要注意的是，对于部分索引，只有在查询规划器可以验证条件在查询中得到覆盖时，才能使用它们。查询规划器很聪明，但在这方面无法解决复杂逻辑。要确保索引可用，可以将"WHERE"子句与原样匹配（可能与更多条件结合使用）。详见手册。 - Erwin Brandstetter

很酷...感谢你深入的回答。 - Luke101

Answer 3

我认为每个行会在位图中使用一个单独的比特位。请参考这里：http://www.postgresql.org/docs/9.0/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE

- Cody