jsonb and primary/foreign keys: which performs better in PostgreSQL?

Question

I'm looking at using PostgreSQL's jsonb column type for a new backend project that will mainly serve as an REST-ful JSON API. I believe that PostgreSQL's jsonb will be a good fit for this project as it will give me JSON objects without need for conversion on the backend.

However, I have read that the jsonb data type slows down as keys are added, and my schema will have need of using primary keys and foreign key references.

I was wondering if having primary keys/foreign keys in their own columns (in the standard relational database way) and then having a jsonb column for the rest of the data would be beneficial, or would this cause problems (whether now or down the road)?

In short, would:

table car(id int, manufacturer_id int, data jsonb)

perform better or worse than:

table car(data jsonb)

Especially when looking up foreign keys frequently?
Would there be downsides to the first one, from a performance or a schema perspective?

Answer 1

All values involved in a PRIMARY KEY or FOREIGN KEY constraint must be stored as separate columns (best in normalized form). Constraints and references do not work for values nested inside a json / jsonb column.

As for the rest of the data: it depends. Having them inside a jsonb (or json) value carries the well-known advantages and disadvantages of storing unstructured document-type data.

For attributes that are present for all or most rows, it is typically better (faster, cleaner, smaller storage) to store them as separate columns. Especially simpler and cheaper to update. Easier indexing and other queries, too. The new jsonb has amazing index capabilities, but indexing dedicated columns is still simpler / faster.

For rarely used or dynamically appearing attributes, or if you want to store and retrieve JSON values without much handling inside the DB, look to jsonb.

For basic EAV structures with mainly character data, without nesting and no connection to JSON I would consider hstore. There are also the xml (more complex and verbose) and json data types (mostly superseded by jsonb), which are losing ground.