Hacker News Reader

Comment by gdeglin | Hacker News Reader

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.

View on HN

> 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.htm...

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.

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.

I believe they are stored more efficiently, so if your data set plus indices don't fit into RAM, you may see some gains.

Reply on news.ycombinator.com