4 ms·

I'm surprised there's nothing in there about JSON; their page on json/jsonb [0] seems pretty clear that json has been all-but-superceded by jsonb. When would j

I'm surprised there's nothing in there about JSON; their page on json/jsonb [0] seems pretty clear that json has been all-but-superceded by jsonb. When would json be preferable to jsonb? I can imagine cases where write-speed is essential and fancier queries and indices aren't needed, but that seems rare. [0] https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html

dllthomas 7y ago
If you need to preserve formatting, maybe.
JelteF 7y ago
It's a wiki right, so you can simply add it
edoceo 7y ago
Duplicate key names are supported in 'json', formatting preserved. jsonb won't allow that and reformats.
skrebbel 7y ago
We recently migrated a large jsonb column to json because, iirc, both the write and the read speeds were much better when there was no (de)serialization involved. My current understanding is that json is faster and bigger than jsonb.
- malisper 7y ago
  > My current understanding is that json is faster and bigger than jsonb. FYI, it's the other way around. When you use the json type, Postgres validates it and stores the raw string. Operations on json are very slow. Whenever you extract a field from a json column, Postgres has to parse the full json. On the other hand, jsonb is stored in a custom binary format. This makes operations a lot faster as Postgres does not have to parse the full json. There's also some other advantages such as jsonb stores the keys in sorted order making it possible to do a binary search to find a specific key in a lookup. There are two primary downsides of jsonb. First, it doesn't preserve whitespace. This can be a problem for some use cases. Second, since there is a lot of metadata in jsonb, jsonb is larger. Postgres stores information like the offsets of every key and value.
  - skrebbel 7y ago
    Good point. We don't extract individual fields or query on them. We just schlep the data in and out, and for that, json appears to be faster.
    - marton78 7y ago
      In that case, couldn't you just use text?
rpedela 7y ago
If you just need to store JSON, it is better to use json rather than jsonb because it will be compressed just like text. If you need to query it, jsonb is better. I have several use cases where I just need to store JSON.

dllthomas 7y ago

If you need to preserve formatting, maybe.

JelteF 7y ago

It's a wiki right, so you can simply add it

edoceo 7y ago

Duplicate key names are supported in 'json', formatting preserved. jsonb won't allow that and reformats.

skrebbel 7y ago

We recently migrated a large jsonb column to json because, iirc, both the write and the read speeds were much better when there was no (de)serialization involved. My current understanding is that json is faster and bigger than jsonb.

malisper 7y ago
> My current understanding is that json is faster and bigger than jsonb. FYI, it's the other way around. When you use the json type, Postgres validates it and stores the raw string. Operations on json are very slow. Whenever you extract a field from a json column, Postgres has to parse the full json. On the other hand, jsonb is stored in a custom binary format. This makes operations a lot faster as Postgres does not have to parse the full json. There's also some other advantages such as jsonb stores the keys in sorted order making it possible to do a binary search to find a specific key in a lookup. There are two primary downsides of jsonb. First, it doesn't preserve whitespace. This can be a problem for some use cases. Second, since there is a lot of metadata in jsonb, jsonb is larger. Postgres stores information like the offsets of every key and value.
- skrebbel 7y ago
  Good point. We don't extract individual fields or query on them. We just schlep the data in and out, and for that, json appears to be faster.
  - marton78 7y ago
    In that case, couldn't you just use text?

rpedela 7y ago

If you just need to store JSON, it is better to use json rather than jsonb because it will be compressed just like text. If you need to query it, jsonb is better. I have several use cases where I just need to store JSON.