0

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.

1 Answer 1

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.

2
  • NB Sounds like a candidate for a partial index. Oct 7, 2021 at 5:13
  • 1
    @Colin'tHart Absolutely. But that works with NULL as well as FALSE. Oct 7, 2021 at 5:32

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.