Does not using NULL in PostgreSQL still use a NULL

Question

Apparently PostgreSQL stores a couple of values in the header of each database row.

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?

Answer 1

The null bitmap is only present if the HEAP_HASNULL bit is set in t_infomask. If it is present it begins just after the fixed header and occupies enough bytes to have one bit per data column (that is, t_natts bits altogether). In this list of bits, a 1 bit indicates not-null, a 0 bit is a null. When the bitmap is not present, all columns are assumed not-null.

http://www.postgresql.org/docs/9.0/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE

so for every 8 columns you use one byte of extra storage. Then for every about million rows that would take up one megabyte of storage. Does not really seem that important. I would define the tables how they needed to be defined and not worry about null headers.

Answer 2

It's actually more complex than that.

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

The "heap tuple header" (per row) is 23 bytes long. Actual data starts at a multiple of MAXALIGN 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

On a typical Debian installation of Postgres 9.3 that would be:

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

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

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

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