PostgreSQL – Why Create Additional NULL Bitmap for NOT NULL Fields?

database-designnullpostgresql

I don't refer to the 1 Byte null bitmap included in the row header that could be used to flag null values for any of the first 8 columns. See:

https://stackoverflow.com/questions/12145772/do-nullable-columns-occupy-additional-space-in-postgresql/12147130#12147130

My question is about the new 8 bytes null bitmap added by Postgresql to each row:

  • with 9 or more columns
  • and including at least one null

I have done some tests by creating / populating some tables and checking row size using pgstattuple extension and I have observed this strange behavior.

The new null bitmap is created even if the 9th (and last) column is created with NOT NULL constraint.

Do you have any explanations? Or am I doing something wrong?

Best Answer

The answer to your question is also in the linked answer:

NOT NULL constraints do not directly affect that.

The size of the NULL bitmap is not influenced by NOT NULL constraints at all.

And there is only ever one (or no) NULL bitmap per row. Never an "additional" NULL bitmap. For rows up to 8 user columns the spare byte between row header and column data (or an optional OID) is used. With more than 8 user columns, the NULL bitmap is extended by MAXALIGN (typically 8 bytes). Column data moves back by the same number of bytes. Resulting in a single NULL bitmap.

Even if it might seem reasonable not to extend the bitmap for trailing columns with NOT NULL constraints, complications might ensue. Like: removing a constraint would have to trigger a whole-table rewrite.

And even if that might be handled, I expect no core developer would be willing to complicate the code to save a few bytes per row for this rare special case.

Related Question