4 ms·

I had some pretty great success rewriting some of our longer running queries to use arrays. A couple things: I implemented several helper functions around this

by mrits 12y ago

I had some pretty great success rewriting some of our longer running queries to use arrays. A couple things: I implemented several helper functions around this. Basically I have a row based function that makes sets unique then I have aggregation functions that give me the cardinality of these sets. I use intset when I can, otherwise I use the "key" part of hstore in substitute of the missing set structure I've been pushing hard to use: https://github.com/aggregateknowledge/postgresql-hll https://github.com/aggregateknowledge/postgresql-hll Being able to switch algorithms based on size IMO makes that one of the most badass thing about postgres right now.

bonesmoses 12y ago
That's some pretty good stuff, right there. Out of curiosity, any reason you're using HSTORE instead of JSON? I've been watching what contexts JSON gets used in PostgreSQL systems, and having another data point never hurts. :)
- acveilleux 12y ago
  From what I can tell... HSTORE vs. JSON: Lower storage overhead, faster operations on stored data. HSTORE vs. JSONB: Significantly lower storage overhead but more limited
- pgaddict 12y ago
  JSON is just a TEXT data type with validation, while HSTORE stores the data in a structured way and allows a more flexible indexing and such stuff. OTOH it's just text=>text while JSON format allows additional data types. That changed with JSONB, which got introduced in 9.4 - that's essentially HSTORE2, with additional improvements.