在PostgreSQL中不使用NULL仍然在标题中使用NULL位图？ [英] Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

问题描述

显然，PostgreSQL在每个数据库行的标题。

Apparently PostgreSQL stores a couple of values in the header of each database row.

如果我不在该表中使用NULL值 - 是否仍然存在空位图？

定义具有 NOT NULL 的列有什么区别？

If I don't use NULL values in that table - is the null bitmap still there?
Does defining the columns with NOT NULL make any difference?

推荐答案

p>它实际上比这更复杂。

It's actually more complex than that.

空位图需要在行中每列一位，向上舍入到全部字节。只有在实际的行包含至少一个NULL值并且在这种情况下才被完全分配的情况下才是这样。 NOT NULL 约束不直接影响到这一点。 （当然，如果你的表的所有字段都是 NOT NULL ，那么永远不会有一个空位图。）

The null bitmap needs one bit per column in the row, rounded up to full bytes. It is only there if the actual row includes at least one NULL value and is fully allocated in that case. NOT NULL constraints do not directly affect that. (Of course, if all fields of your table are NOT NULL, there can never be a null bitmap.)

堆元组头（每行）长23个字节。实际数据开始于之后的$ code> MAXALIGN 的倍数，这通常是64位操作系统上的8个字节（32位操作系统上的4个字节）。以root用户身份从PostgreSQL二进制目录运行以下命令，以得到一个确定的答案：

The "heap tuple header" (per row) is 23 bytes long. Actual data starts at a multiple of MAXALIGN after that, which is typically 8 bytes on 64-bit OS (4 bytes on 32-bit OS). Run the following command from your PostgreSQL binary dir as root to get a definitive answer:

./pg_controldata /path/to/my/dbcluster

在Postgres 9.3的典型Debian安装中，将是：

On a typical Debian installation of Postgres 9.3 that would be:

sudo /usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/main

无论哪种方式，标题和数据的对齐开始之间都有一个空闲字节，这是空位图可以利用的。只要您的表具有 8列或更少，空存储就可以有效绝对免费（就磁盘空间而言）。

Either way, there is one free byte between the header and the aligned start of the data, which the null bitmap can utilize. As long as your table has 8 columns or less, NULL storage is effectively absolutely free (as far as disk space is concerned).

之后，为零位图（加上填充）分配另一个（通常为64个字段）的另一个 MAXALIGN （通常为8个字节）。等等。

After that, another MAXALIGN (typically 8 bytes) is allocated for the null bitmap (plus padding) to cover another (typically) 64 fields. Etc.

这至少适用于8.4 - 9.6版本，很有可能不会更改。

This is valid for at least versions 8.4 - 9.6 and most likely won't change.

这篇关于在PostgreSQL中不使用NULL仍然在标题中使用NULL位图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！