4 ms·

We store key/value pairs in Hstore, since JSONB wasn't available when we started doing this. We've since updated to PostgreSQL 9.5. Does anyone know if we woul

by gdeglin 10y ago

We store key/value pairs in Hstore, since JSONB wasn't available when we started doing this. We've since updated to PostgreSQL 9.5. Does anyone know if we would see a performance advantage switching this to JSONB (either in selects or updates). My sense is no, but I haven't been able to find any good answers one way or the other. If it's yes, then HStore doesn't really serve any purpose at all anymore.

netghost 10y ago
I believe they are stored more efficiently, so if your data set plus indices don't fit into RAM, you may see some gains.
liquidise 10y ago
I am in the inverse position: i used JSONB when 9.4 came out. I cant speak for comparative performance, but our 8gb db server has no performance issues at all querying an unindexed jsonb column across many millions of rows. The one "gotcha" with the column type right now is comparing hash values to certain data types can be a bit confusing at times, but simple string comparisons in arrays and hashes is relatively straight forward.
_pmf_ 10y ago
> Does anyone know if we would see a performance advantage switching this to JSONB (either in selects or updates). I don't actually know anything from practical usage, but I think JSONB makes some kinds of membership tests more efficient (at the price of more expensive inserts). See 8.14.3. of https://www.postgresql.org/docs/9.4/static/datatype-json.html https://www.postgresql.org/docs/9.4/static/datatype-json.htm...
drob 10y ago
We've used both at Heap. I wouldn't expect much of a performance change from switching in either direction. JSONB has the advantage of being in a data format that lots of tools speak, and has generally been less annoying to use. But if your app is pretty stable and all of your code already speaks hstore, probably not worth it.

4 ms·

We store key/value pairs in Hstore, since JSONB wasn't available when we started doing this. We've since updated to PostgreSQL 9.5. Does anyone know if we woul

by gdeglin 10y ago

netghost 10y ago
I believe they are stored more efficiently, so if your data set plus indices don't fit into RAM, you may see some gains.
liquidise 10y ago
I am in the inverse position: i used JSONB when 9.4 came out. I cant speak for comparative performance, but our 8gb db server has no performance issues at all querying an unindexed jsonb column across many millions of rows. The one "gotcha" with the column type right now is comparing hash values to certain data types can be a bit confusing at times, but simple string comparisons in arrays and hashes is relatively straight forward.
_pmf_ 10y ago
> Does anyone know if we would see a performance advantage switching this to JSONB (either in selects or updates). I don't actually know anything from practical usage, but I think JSONB makes some kinds of membership tests more efficient (at the price of more expensive inserts). See 8.14.3. of https://www.postgresql.org/docs/9.4/static/datatype-json.html https://www.postgresql.org/docs/9.4/static/datatype-json.htm...
drob 10y ago
We've used both at Heap. I wouldn't expect much of a performance change from switching in either direction. JSONB has the advantage of being in a data format that lots of tools speak, and has generally been less annoying to use. But if your app is pretty stable and all of your code already speaks hstore, probably not worth it.