Storing user-defined segments JSONB vs separate table?

Question

I want to store user-defined segments. A segment will consist of several different rules. I was thinking I would either create a separate separate table of "Rules" with three columns: attribute name, operator, and value. For example, if a Segment is users in the united states the rule would be "country = US" in their respective columns. A segment can have many rules.

The other option is to store these as JSONB via Postgres in a "Rules" column in the Segment table. I'd follow a similar pattern to the above with an array of rules or something. What are the pros and cons of each method?

Maybe neither one of these is the right approach.

Answer 1

The choice is basically about the way you wish to read the data.

You are better off with JSON if:

• you are not going to filter (with a WHERE clause) through the Rules
• you do not need to get statistics (i.e. GROUP BY)
• you will not imply any constraints on attributes/operators/values
• you simply select the values (SELECT ..., Rules)

If you meet these requirements you can store data as JSON, thus eliminating JOINs and subselects, eliminating the overhead of primary key and indexes on Rules, etc.

But if you don't meet these you should store the data in a common relational design - your approach 1.