How much disk-space is needed to store a NULL value using postgresql DB?
Laramie is right about the bitmap and he links to the right place in the manual. Yet, this is almost, but not quite correct:
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).
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.
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.
I ran extensive tests to verify all of that. More details:
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).
More in depth discussion from the docs here
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.
Edit: Laramie seems to know more about null than me :)