Storing foreign keys in postgresql jsonb a bad idea?

Question

What are the problems associated with storing foreign keys in a jsonb column?

Context:

I have a table of items:

Item 
--------------------------
| id   | name | property |
| PK   | text |  jsonb   |

The property column is one-level jsonb of the following structure:

[
  {"value": "white", "item_attribute_id": "1"},
  {"value": "71", "item_attribute_id": "3"},
  {"value": "29", "item_attribute_id": "4"},
  {"value": "48-70", "item_attribute_id": "5"},
  {"value": "190", "item_attribute_id": "6"}
]

The item_attribute_id is a foreign key pointing to a table of attributes, which holds everything related to the given attribute (name, type, description).

I cannot find any literature on why this might be a good/bad practice. Are there any obvious directly related problems that I overlooked?

Answer 1

You need to take into consideration following things about JSONB type:

• queries will be more complex; if you even feel comfortable with all JSONB functions (they are more about SELECT statements), UPDATE operation will still be tricky - please consider @Erwin's answer regarding this.
• size overhead; for small docs it will not really matter, but at scale you might hit a wall. Just compare pg_column_size() results for your case.
• limited indexing support; you will have no luck when you perform search in array by element (functions like json_array_elements() etc). Default GIN index supports queries with the (CONTAINS) @>, (EXISTS)?, (EXISTS ALL)?& and (EXISTS ANY)?| operators, so you need to be careful with the queries you are using. jsonb supports btree and hash indexes. You can check details here.

Articles to consider:

• Rethinking JSONB from PGConf 2015
• JSONB type performance in PostgreSQL 9.4