1

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?

2

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

  • The read speed vs write speed is a good point. I just clarified that in my question. I don't need good write speed at all, only good read speed. I guess that means JSON is the way to go? – Jonah Jul 30 '15 at 16:19
  • Actually, reading your answer again, sounds like you're recommending JSON for write speed, not read speed. So do you think JSON, or JSONB? – Jonah Jul 30 '15 at 16:21
  • JSON has better write speed. I'm guessing JSONB will have better read speed, but it should be very close. Why don't you test both and report back? – Colin 't Hart Jul 30 '15 at 17:06
  • Colin I think you're right, a performance test is in order. – Jonah Jul 30 '15 at 18:25
  • 3
    Okay, I ran a bit of a crude test. JSON comes out about 20% faster than JSONB for read speed. I filled up a table with about 150,000 records with two columns: one JSON and the other JSONB. Selecting all rows from the JSON column took 10 seconds, selecting all from JSONB took about 12 seconds. I ran each several times, and the results were consistent. TL;DR JSON has faster read speed. My guess is that JSONB has to convert the binary format back to JSON, but JSON is already in a string form. – Jonah Aug 3 '15 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.