What is the best way to store a large (thousands of properties) nested JSON object in Postgres? It does not need to be index, queried, or anything. Just written and read. Write speed isn't an issue at all, only read speed. Should I still opt for JSONB, even though it seems like the benefits of JSONB would be rendered inert? Perhaps JSON? Or even just a normal text type?
PostgreSQL JSON – Storing Large Nested Objects
jsonpostgresql
Related Question
- PostgreSQL 9.3 – How to Disable Postgres from Listening on IPv6
- Postgresql – Does PG somehow support the ability to set a column to “variable type”
- Postgres JSONB – Flatten Nested Objects and Groupings
- Postgresql – Postgres 12 and full text search against arbitrary key-value pairs
- PostgreSQL Performance – Simple Query on 300M Row Table is Very Slow
Best Answer
As with most things, it depends.
First of all, if you're not 100% certain that the data will always be valid JSON, use a normal text type instead.
If the data is indeed always JSON, I would use a JSON type if for no other reason that as documentation that the column contains JSON data.
If there will ever be any chance of requiring indexing of data, go for the JSONB datatype. If insert performance is critical, choose JSON (or even a text type) instead. The JSON datatype stores the JSON data as-is, while the JSONB datatype removes empty nodes and whitespace and even duplicate keys (the last one is kept). This means that JSONB is storing less data, but requires minor reconstruction on retrieval.
For all other cases, I would go with JSONB as the "best" generic JSON type in PostgreSQL at present, and this is also the recommendation in the documentation. See http://www.postgresql.org/docs/9.4/static/datatype-json.html