Is there a common pattern for storing many boolean flags other than adding a ton of columns?

I have a bunch of booleans I need to associate with user accounts. They need to be toggled true and false, though not particularly frequently - they're going to be read more often than they're written. Most commonly they'll just be set from False to True once, but that won't always be the case.

Right now I'm thinking of appending columns to the user table like this:

UserID
other_user_cols
did_a
did_b
did_c

1
...
False
True
False

2
...
False
False
False

3
...
True
False
False

The thing is, there's quite a lot of these boolean columns.

Let's say you needed to add like, 100 columns. Is this really still the best way to do it?

Assume:

• It's not going to be very sparse - most users will toggle most booleans at some point

• The booleans aren't logically related in a way that could naturally be better organized

• New ones may need to be added from time to time

• the flags are directly associated with each user and no other info is needed other than the single boolean value

I feel this must be a pretty common pattern, and yet this way of just jamming more boolean columns into my table seems rather messy. Is there a more typical way of doing it that's a bit cleaner? Or is this actually the best way?

I'm using Postgres for this.

Here are some alternatives I've considered:

• serialize into a json list of strings (= the column names). This sounds even messier than the original and also not ideal if I ever want to do any serious SQL operations on the data

• Use numbers and bitwise operations - not sure how well this is supported and similar concerns about ever needing to do operations/lookups on individual flags

• Have a separate table like:

UserID
flag_name
value

1
did_a
False

1
did_b
False

3
did_a
True

That seems most reasonable to me as an alternative but not sure if it's really worth the trouble or if there's a better way.

I recommend using varbit, mostly because it is space efficient. Sure, storage space is cheap, but you have to cache in RAM, which is not so cheap, and manipulating smaller rows performs much better.

It is also easy to manipulate:

CREATE TABLE mytable (id bigint PRIMARY KEY, flags varbit);

INSERT INTO mytable VALUES (1, b'11010001000110100');

To get the value of the tenth flag:

SELECT get_bit(flags, 9) FROM mytable WHERE id = 1;

 get_bit 
═════════
       0
(1 row)

To change the tenth flag:

UPDATE mytable SET flags = set_bit(flags, 9, 1) WHERE id = 1;

SELECT * FROM mytable WHERE id = 1;

 id │       flags       
════╪═══════════════════
  1 │ 11010001010110100
(1 row)

To add a new bit at the end:

UPDATE mytable SET flags = flags || b'1' WHERE id = 1;

StackExchange Database Administrators Q#294111, answer score: 16