Hacker News new | past | comments | ask | show | jobs | submit | magnush's comments login

Probably the most important difference is that the old JSON types preserves whitespace and attribute ordering, whereas the new one doesn't. If you don't care about those, the old one is also slightly smaller on disk - but I would say not worth it based on the simple fact that you might want to search it in the future some time...


Another important part about gist is that GIN has much higher overhead for tables that are frequently updated.


One important difference is that you don't need to extract the values to create an index. E.g. in Mongo I believe you still have to decide which keys to index. A GIN index on top of jsonb in PostgreSQL will make it possible to do an indexed search on any key, not just those that are specified at create index time.


PostgreSQL jsonb has the same problem in the actual document storage, with repeated keys. I believe it does better in the index, when you use the correct operator, but the table storage has the same type of overhead.


No, jsonb will still update the whole value when you edit it. For now at least.


Isn't this the case for postgresql data in general? ie, on updates, new data rows are written containing all the field values, whether or not they have changed.


It's more they case that when you want to reach into an existing stored document and change a single value you have to re-write the entire document from your application.

On the reading side there are functions and operators that allow you to reach into stored JSON and extract the parts you want. What would be nice would be to be able to do something similar for updates - although this is clearly more complex than reading, so I can see why it has been done this way.

Edit: I guess the most general solution would be to directly support something like JSON Patch:

https://tools.ietf.org/html/rfc6902


Yes, but you don't have to specify all the other columns that aren't being updated.

With json/jsonb, you have to provide the entire object graph each time you are updating it. You can't update one field in the graph. Which could be a pain if you have concurrent updates.

Hopefully we'll be able to update parts of the jsonb columns sometime.


You can do it right now, you just need to write your own updating function. With PL/Python or PL/V8 it would be quite simple.


You're right, because MVCC works on immutable rows. The structure can be broken up to accommodate large values[1], which saves updating parts that don't change, but json data is a single column and updates need to copy the entire document.

[1] http://www.postgresql.org/docs/devel/static/storage-toast.ht...


Didn't the hstore2 patch add this for hstore? Wouldn't it be easy to take the same function and make ti work for jsonb?


There were lengthy discussions about this in the [postgres] hackers mailing list and it turned out that, no, it wasn't.


Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: