Postgres advantages of BOOLEAN NOT NULL vs BOOL with TRUE/NULL

Question

I want to add a flag to a frequently accessed table in my database. It is logically a boolean that is set for a very small portion of the records <0.1%. I want to ensure that I am not causing a significant performance cost unnecessarily.

Reading the docs and other answers it seems to me that a BOOLEAN NOT NULL column will take 1 byte in every row (amortized) whereas if I instead use a BOOLEAN that is NULL for most rows it will only take 1 bit (amortized, in the boolean bitmap) and one byte where it is set to TRUE.

What are the advantages and disadvantages to using NULL instead of FALSE assuming that I do not and will never need a tristate.

Answer 1

Yes, you will be saving a byte per row, and possibly even more, if the next column is of a type with a type alignment greater than 1.

However, the NULL might make your queries more complicated or harder to read, which might be worse than the benefit of saving a little disk space.

I'd decide based on the latter: if your queries are still efficient and readable using NULL values, go for it, otherwise don't.

• Colin 't Hart: NB Sounds like a candidate for a partial index.
• Laurenz Albe: @Colin'tHart Absolutely. But that works with NULL as well as FALSE.