medBo medBo - 1 year ago 70
JSON Question

What data type to choose json or jsonb or text

I would like to store a big json hash (or content, call it like you want) and by "big" I mean something above 1000 key value pairs, I don't want to do any search on that json field, I just want to retrieve it from the database and pass it to javascript to parse it and construct a visual result.

On Postgresql there is a

type and
type (and maybe I can use a
field as well to store json) . I just want to make the right choice for that purpose, so I would like some advice from experienced people.

Answer Source

Assuming you're talking about actual, strict JSON (without quirks such as unquoted keys)...

json isn't too different from text. It doesn't do much apart from validating JSON.

jsonb is a different beast compared to the these two: it's a full-fledged data structure with its own internal format that has much more operations available in searches. For instance json has no applicable = (equality operator). jsonb has. (text has too, even though it's semantically different.)

It's much more sensible to index, but it has to be transformed back and forth during reads and writes.

Given that, jsonb doesn't look like a sound choice here.

... So there is only one decision left to make:

Do you want to ensure that your database contains only valid JSON values in your column? On the database level? Or do you trust every client of that database (server apps, typically) to only supply valid data?

json is a relatively safe choice either way. Using text could theoretically improve performance by a negligible margin due to absence of validation, but you'll only get specific numbers by benchmarking. But it wouln't have that safeguard against non-JSON values, and an accidental bug in the client could go unnoticed. Test responsibly!