Does it make sense to store a couple of Boolean values as array?
TL;DR: Do not use an array. Use individual boolean
columns.
Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres.
- Do nullable columns occupy additional space in PostgreSQL?
A couple of boolean
columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean
columns hardly matter for the row size at all - even if defined NOT NULL
. 1 byte each, 5 bytes and no alignment restrictions. Hardly relevant. There are typically much more substantial things you can do to your row. (You should have provided your actual table definition.)
- Configuring PostgreSQL for read performance
Arrays have an overhead of 24 bytes. Plus actual data. And handling is much less convenient and much more expensive:
- Calculating and saving space in PostgreSQL
This related answer has a detailed assessment for three relevant options:
- separate
boolean
columns - an
integer
to encode up to 32 boolean values - a bitstring (
bit(n)
orbit varying(n)
)
- Is there any difference between integer and bit(n) data types for a bitmask?
In your case I think you could consider the use of a Bit String Type data type. For instance, something like:
CREATE TABLE yourtable (
booleans bit[5] default B'00000',
... other fields ... )
It is efficient in terms of memory and does not require the use of a complex type like a PostgreSQL array (actually it is a bit array), and more, you do not have to pay attention to the difference between false and null (and also you could set the entire field to a null value, if you need to).