I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent Postgres features (e.g. gin indexes) from being used?

  • 5
    Arrays will take much more space. Nulls in Postgres will need exactly 0 space under some circumstances. Jul 13, 2016 at 16:12

2 Answers 2


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. See:

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.)

Arrays have an overhead of 24 bytes. Plus actual data. And handling is much less convenient and much more expensive:

This related answer has a detailed assessment for three relevant options:

  1. separate boolean columns
  2. an integer to encode up to 32 boolean values
  3. a bitstring (bit(n) or bit varying(n))

  • 1
    Also the performance hit loading an array at runtime and fetching an element from it. Also the clarity hit by no longer having a dedicated, descriptive name for each boolean.
    – jpmc26
    Aug 27, 2019 at 1:23

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).

  • Is there a way I can make a user defined type or something so that I don't have to remember what each bit means?
    – Xodarap
    Jul 13, 2016 at 20:12
  • I think you could solve this problem at the application level, using as index to the array an enumeration or something like it according to your programming language.
    – Renzo
    Jul 13, 2016 at 20:22
  • Yeah, I could do that but it's always nicer if the sql is intuitive as well
    – Xodarap
    Jul 13, 2016 at 21:13
  • 4
    Still, the type bit(n) has 5 or 8 bytes overhead depending on the length of the string. The total size of 5 boolean NOT NULL columns is 5 bytes. bit(n) is not efficient in terms of memory. The only possible advantage is that a single NULL can cover all 5 flags. 1 bit vs. 5 bit in the NULL bitmap. But that typically doesn't make any effective difference in storage. Jul 14, 2016 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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