3

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?

  • @ypercubeᵀᴹ: The OP mentioned the 1 byte null bitmap for up to 8 user columns (not 2 bytes). I ran extensive tests in the past and studied the source code, and I can confirm that is correct. Nullable or not is irrelevant, though, and that's the point here. – Erwin Brandstetter Feb 9 '18 at 3:00
  • @ypercube : I didn't know how the additional bytes were added in the row storage. ErwinBrandstetter gives the answer below : the initial null bitmap is extended. So I was wrong. My tests were only about total row size, not storage order details. – mnementh64 Feb 9 '18 at 8:20
  • Thnx, that's why I asked. The question was not clear on how you performed and tests and how you arrived into a conclusion. – ypercubeᵀᴹ Feb 9 '18 at 8:29
2

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.

  • Yes, that's what I thought was going, too. So if the table has 9 to 16 columns, the null map is extended to 2 bytes. If 17-24 to 3 bytes, etc. Correct? (unless MAXALIGN is different) – ypercubeᵀᴹ Feb 9 '18 at 7:26
  • @ErwinBrandstetter : thank you for the precision. I have seen the reason, but I didn't understand why. And your explanation is perfectly right : having to rewrite the whole table for any NOT NULL constraint removal is too high cost. – mnementh64 Feb 9 '18 at 8:13
  • @ypercube : since a 9th column is added, the null bitmap is extended by MAXALIGN (typically 8) bytes. So it can handle up to 64 more columns. And again since a 73th column is added ... – mnementh64 Feb 9 '18 at 8:15
  • @mnementh64: Exactly right. – Erwin Brandstetter Feb 9 '18 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.