使用 postgresql DB 存储 NULL 值需要多少磁盘空间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4229805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How much disk-space is needed to store a NULL value using postgresql DB?
提问by Chris
let's say I have a column on my table defined the following:
假设我的桌子上有一列定义了以下内容：
"MyColumn" smallint NULL
Storing a value like 0, 1 or something else should need 2 bytes (1). But how much space is needed if I set "MyColumn" to NULL? Will it need 0 bytes?
存储 0、1 或其他值之类的值应该需要 2 个字节 (1)。但是如果我将“MyColumn”设置为 NULL 需要多少空间？它需要0字节吗？
Are there some additional needed bytes for administration purpose or such things for every column/row?
是否有一些额外的需要用于管理目的的字节或每列/行的此类内容？
(1) http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html
(1) http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html
回答by Erwin Brandstetter
Laramieis right about the bitmap and he links to the right place in the manual. Yet, this is almost, but not quite correct:
Laramie关于位图是正确的，他链接到手册中的正确位置。然而，这几乎但不完全正确：
So for any given row with one or more nulls, the size added to it would be that of the bitmap(N bits for an N-column table, rounded up).
因此，对于具有一个或多个空值的任何给定行，添加到其中的大小将是位图的大小（N 列表的 N 位，向上取整）。
One has to factor in data alignment. The HeapTupleHeader(per row) is 23 bytes long, actual column data always starts at a multiple of MAXALIGN(typically 8 bytes). That leaves one byte of padding that can be utilized by the null bitmap. In effect NULL storage is absolutely free for tables up to 8 columns.
必须考虑数据对齐。的HeapTupleHeader（每行）为23个字节长，实际列数据总是开始于的倍数MAXALIGN（通常为8个字节）。这留下了一个可由空位图使用的填充字节。实际上，NULL 存储对于最多 8 列的表是完全免费的。
After that, another MAXALIGN(typically 8) bytes are allocated for the next MAXALIGN * 8(typically 64) columns. Etc. Always for the total number of user columns (all or nothing). But only if there is at least one actual NULL value in the row.
之后，MAXALIGN为接下来的MAXALIGN * 8（通常为 64）列分配另一个（通常为 8 个）字节。等等。总是针对用户列的总数（all 或 nothing）。但前提是该行中至少有一个实际的 NULL 值。
I ran extensive tests to verify all of that. More details:
我进行了广泛的测试来验证所有这些。更多细节：
回答by Laramie
Null columns are not stored. The row has a bitmap at the start and one bit per column that indicates which ones are null or non-null. The bitmap could be omitted if all columns are non-null in a row. So for any given row with one or more nulls, the size added to it would be that of the bitmap(N bits for an N-column table, rounded up).
不存储空列。该行在开头有一个位图，每列有一个位，指示哪些是空的或非空的。如果一行中的所有列都不为空，则可以省略位图。因此，对于具有一个或多个空值的任何给定行，添加到其中的大小将是位图的大小（N 列表的 N 位，向上取整）。
More in depth discussion from the docs here
从这里的文档中进行更深入的讨论
回答by J V
It should need 1 byte (0x00) however it's the structure of the table that makes up most of the space, adding this one value might change something (Like adding a row) which needs more space than the sum of the data in it.
它应该需要 1 个字节 (0x00) 但是它是构成大部分空间的表的结构，添加这个值可能会改变一些东西（比如添加一行），它需要比其中数据总和更多的空间。
Edit:Laramie seems to know more about null than me :)
编辑：Laramie 似乎比我更了解 null :)
