Menu
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 json type and jsonb type (and maybe I can use a text 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.
584
In general, most applications should prefer to store JSON data as jsonb , unless there are quite specialized needs, such as legacy assumptions about ordering of object keys. RFC 7159 specifies that JSON strings should be encoded in UTF8.
The data types json and jsonb , as defined by the PostgreSQL documentation,are almost identical; the key difference is that json data is stored as an exact copy of the JSON input text, whereas jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.
JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
Json processes input faster than jsonb as there is no conversion involved in this. Jsonb converts the JSON data into the binary form so it has slightly slower input due to the binary conversion overhead. There is no change in the Schema design while working with JSON.
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!
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
