Postgresql – Performance: storing list of thousands of items in postgresql’s JSON field vs having separate table for items

jsonperformancepostgresql

Let's say I have some table A which have json-field items:

A:
 foo: 'foo'
 items: [
  {a: 1, b:2},
  {a: 3, b:4},
  ...
 ]

There could be couple of thousands of items in one A object.

Is there any sense to introduce another table Item instead of having json field?

Item:
 a: 1
 b: 2

Both methods allow me to query stuff that I need, but it's much easier to work with separate table and it also provide out of the box validation for data.
My only concern is that it might be much slower than dealing with all items that already stored in one place.

Should I worry about performance?

Best Answer

You will need to measure the two ways against your performance requirements.

That said, in the world of RDBMS (which Postgresql is), generally it's better to keep tables rather than JSON/XML/Blobs etc.

The databases are optimised and designed for relational data and while it's true they have supporting features for JSON/XML etc, often these are not as mature, performant or generally as well thought out as the relational side.

Additionally there are more third party data analysis, reporting, ETL, ORM systems for relational than JSON.

Any or all of those statements may well become more or less true over time so you really need to decide for yourself as there's not generally a right way for all cases.

Me personally? If it's data in a known, structured format and your DB and requirements tend towards relational then go relational and get rid of your JSON storage completely.

Related Question