PostgreSQL database with ~10,000 boolean states per row

Question

I'm at a bit of a loss as to how design this particular database. It has the following features:

• Expected 3 million+ rows corresponding to 'users';
• Each 'user' has associated with them ~10,000 unique boolean states;
• These states are sparse in nature, and additional states will be added in the future, and likely shouldn't be stored in an ordered list;
• The states will be updated frequently, at a rate of about 20 in the span of 2 hours, every 24 hours, on average per active 'user';

The obvious design is to have a lookup table between the users and the states, but I'm concerned this will not be fast enough, when looking up states per user, with an expected 5 billion+ rows in the lookup table.

Any advice is appreciated.

Answer 1

I would create a relation between 2 tables

--------------------------------------------------
| ID | username                                  |
--------------------------------------------------
| 1  | bl-ro                                     |
--------------------------------------------------
| 2  | darkmukke                                 |
--------------------------------------------------

and then a relation for the bools

--------------------------------------------------
| ID | fk_user | bool                            |
--------------------------------------------------
| 1  | 1       | true                            |
--------------------------------------------------
| 2  | 1       | false                           |
--------------------------------------------------