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

Question

let's say I have a column in my table that defines the following:

"MyColumn" smallint NULL 

Storing a value, such as 0, 1 or something else, should be 2 bytes (1). But how much space is needed if I set "MyColumn" to NULL? Do you need 0 bytes?

Are there any additional necessary bytes for administrative purposes or such things for each column / row?

(1) http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html

Answer 1

Zero columns are not saved. A row has a bitmap at the beginning and one bit per column, which indicates which ones are null or nonzero. A bitmap may be omitted if all columns are not equal to zero in the row. Thus, for any given row with one or more zeros, the size added to it will have the bitmap size (N bits for the table of N-columns, rounded up).

A more detailed discussion of the documents is here.

Answer 2

Laramie is right about the bitmap, and he refers to the right place in the manual. However, this is almost, but not entirely correct:

So, for any given row with one or more zeros, the size of the bitbip added to it will be a bitbip (N bits for the table of N-columns, rounded up).

It is necessary to consider data alignment. HeapTupleHeader (for each row) has a length of 23 bytes, the actual column data always starts with a multiple of MAXALIGN (usually 8 bytes). This leaves one fill byte, which can be used by a null bitmap. In fact, NULL storage is completely free for tables with up to 8 columns .

After that, other MAXALIGN (usually 8) bytes are allocated for the next columns of MAXALIGN * 8 (usually 64). Etc. Always for the total number of user columns (all or nothing). But only if the string has at least one actual NULL value.

I conducted extensive tests to verify all of this. More details:

• Doesn’t NULL in PostgreSQL still use a NULL bitmap in the header?

Answer 3

It should contain 1 byte (0x00), however, the table structure, which makes up most of the space, adds this value, can change something (for example, add a row), which requires more space than the sum of data in it.

Edit: Laramie seems to know more about zero than me :)