How much disk-space is needed to store a NULL value using postgresql DB?

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?

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

Solution 1:

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:

• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

Solution 2:

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