我有一个包含7列的表,其中5列将为空值。这些空值将分别出现在 int
, text
, date
, boolean
,和 money
数据类型中。该表将包含数百万行数据,其中有很多空值。我担心这些空值会占用空间。
此外,你知道Postgres是否会对空值建立索引吗?我希望防止它对空值进行索引。
我有一个包含7列的表,其中5列将为空值。这些空值将分别出现在 int
, text
, date
, boolean
,和 money
数据类型中。该表将包含数百万行数据,其中有很多空值。我担心这些空值会占用空间。
此外,你知道Postgres是否会对空值建立索引吗?我希望防止它对空值进行索引。
基本上,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个)。等等。
更多细节请参见手册以及下面这些相关问题:
一旦您理解了数据类型的对齐填充,您还可以进一步优化存储:
但是能够节省大量空间的情况并不常见。通常不值得花费这个力气。
@Daniel已经讨论了对索引大小的影响。
请注意,删除的列(虽然现在看不见)仍保留在系统目录中,直到表被重新创建。这些"僵尸"可能会强制分配一个(扩大的)NULL位图。详见:
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)
这将占用更少的空间,但是它是否有助于查询性能取决于这些查询。
MAXALIGN
的倍数。 - Erwin Brandstettert_cid ... (overlays with t_xvac)
在这里 意味着这两个项目共享同一个物理字段。源代码中的注释也暗示了这一点:https://doxygen.postgresql.org/htup__details_8h_source.html 第72行。 - Erwin Brandstetter