PostgreSQL JSONB – Should JSONB Be Used for Integer Arrays?

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach.

Failing that, you can and should use an array field instead of a comma separated list. Using a comma-separated list is just downright horrid from a design point of view. It makes queries harder to write, more error prone, and forces you to do lots of slow and inefficient string manipulation and number parsing just for simple operations, and prevents any kind of integrity checking without very inefficient CHECK constaints or triggers. I think Bill nailed it with:

1,2,3,banana,5

With an array you can use the intarray extension to provide a GiST index that lets you quickly test if the array contains a given value using an indexable @> or <@ operation. You may want to add the btree_gist extension too, so you can create a composite GiST index of main_id, the_values_array, in case your queries are usually of the form:

WHERE main_id = blah AND the_values_array @> ARRAY[42]

(or have two separate indexes and see if it'll do a bitmap index scan).

You can't enforce a foreign key relationship into an array in PostgreSQL yet, though the feature seems to be on the way. You'd need to do it with somewhat complicated triggers in the mean time. Still, it's a lot better than a comma-separated list.

This is indeed a reasonable case for storing object-like or key/value data, and representing it as JSON in jsonb fields in PostgreSQL is a reasonable way to do that.

In general it's time to consider hstore, xml, jsonb, etc when you're starting to look at alternatives like EAV or wide tables where the app adds columns dynamically. jsonb is basically the new hstore with a more standard way of representing its data and with nesting capabilities, so it's preferable most of the time.

Static tables for different kinds of thing, with parent tables for more general categories of thing, still make sense if the things being tracked are (a) fairly static and not user-defined, and (b) not excessively numerous. That does not sound like the case here.

I still recommend keeping attributes as regular columns where:

• They form part of a referential relationship (is-a, has-a, contains, is-contained-by, owns, is-owned-by, etc);
• They are a natural key where a UNIQUE constraint makes sense;
• They are filtered on or otherwise queried in the large majority of queries

You can define a unique constraint - or rather, unique index - over an expression that queries the json field. It's just often cleaner to have them separate. Similarly expression indexes can help with oft-queried fields you want to keep part of the json object. It's really the first point that's the most important, as RI constraints cannot reference the value of a json object key.