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
What are the advantages and disadvantages to using
NULL instead of
FALSE assuming that I do not and will never need a tristate.
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.