Decide on data type for tags: hstore, json, jsonb

[Ludee]

hstore vs. json vs. jsonb

I'm currently looking for an improvement for variable data structures (key-value-pairs).

Research:

• https://dba.stackexchange.com/questions/115825/jsonb-with-indexing-vs-hstore
• https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf
• https://www.ongres.com/blog/a_generalized_unstructured_data_type_for_postgres/
• https://www.postgresql.org/docs/9.3/functions-json.html
• https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/
• https://dzone.com/articles/using-jsonb-in-postgresql-how-to-effectively-store
• https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/
• https://medium.com/hackernoon/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3
• http://www.silota.com/docs/recipes/sql-postgres-json-data-types.html
• https://dba.stackexchange.com/questions/3492/optimizing-query-using-view-on-eav-structure
• https://dba.stackexchange.com/questions/105533/postgres-9-4-jsonb-instead-of-eav
• https://dba.stackexchange.com/questions/95758/postgresql-update-and-delete-property-from-jsonb-column

Criterial:

• Performance (Speed)
• Performance (Storage size)
• Usability (Syntax)

Examples:

OSM uses hstore.

I use this issue as documentation. Feel free to comment and improve!

[jh-RLI]

We chose JSON because it seems to be the best option for our use case and offers great support. I already updated the data package files.

[Ludee]

That doesn't really solve my question between json and jsonb.

see: http://www.silota.com/docs/recipes/sql-postgres-json-data-types.html

1. Difference between JSON and JSONB

The JSON data type is basically a blob that stores JSON data in raw format, preserving even insignificant things such as whitespace, the order of keys in objects, or even duplicate keys in objects. It offers limited querying capabilities, and it's slow because it needs to load and parse the entire JSON blob each time.

JSONB on the other hand stores JSON data in a custom format that is optimized for querying and will not reparse the JSON blob each time.

If you know before hand that you will not be performing JSON querying operations, then use the JSON data type. For all other cases, use JSONB.

The following example demonstrates the difference:

select '{"user_id":1, "paying":true}'::json, '{"user_id":1, "paying":true}'::jsonb;

        json                |             jsonb              

--------------------------------+-------------------------------- {"user_id":1, "paying":true} | {"paying": true, "user_id": 1} (1 row)

(the whitespace and the order of the keys are preserved in the JSOB column.)

[AsaiWiz]

I see one typo: (the whitespace and the order of the keys are preserved in the JSOB column.) Should read (the whitespace and the order of the keys are preserved in the JSON column.). JSONB does not retain whitespace and order of keys as it stored parsed json.

[henhuy]

Thanks for the info @Ludee! I think for our use cases, querying the JSONs is not crucial (perhaps this never occurs). Therefore, I tend to JSON. Additionally, I don't know if OEDialect supports JSONB. If not, maybe this should be added as it could be useful for metadata querying!