在 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?

推荐答案

实际上比这更复杂.

空位图需要行中每列一位，四舍五入到完整字节.只有当实际行至少包含一个 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 字节长.实际数据以 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 (Maximum data alignment) 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

在典型的基于 Debian 的 Postgres 12 安装中:

On a typical Debian-based installation of Postgres 12 that would be:

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

无论哪种方式，在标头和对齐的数据开始之间都有一个空闲字节，空位图可以利用它.只要您的表具有8 列或更少，NULL 存储实际上绝对免费(就磁盘空间而言).

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).

之后，为空位图分配另一个MAXALIGN(通常为8个字节)以覆盖另一个(通常)64个字段.等

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

这至少对 8.4 - 12 版本有效，并且很可能不会改变.

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

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