postgresql-hll

HyperLogLog and PostgreSQL: https://github.com/citusdata/postgresql-hll Or even simpler, Roaring Bitmaps: https://pncnmnp.github.io/blogs/roaring-bitmaps.html https://blog.quastor.org/p/grab-rate-limiting https://github.com/RoaringBitmap/CRoaring

I don't have a good enough grasp HLL to summarize accurately, so for convenience here is the original paper[0] and hopefully HN comes through with a more digestible explanation. The blog post uses Citus' Postgres extension[1] which uses an augmented various of the original algorithm to reduce the size of the data structure so that it is fixed-size [0] HLL paper: http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf [1] postgresql-hll: https://github.com/citusdata/postgresql-hll

Thought Postgres had hyperloglog for a while https://github.com/citusdata/postgresql-hll ?

some of my favorites: - transactional DDL: https://www.google.com/search?q=transactional+ddl+postgresql - expression indexes: https://www.postgresql.org/docs/current/indexes-expressional... - partial indexes: https://www.postgresql.org/docs/current/indexes-partial.html - Javascript UDFs (plv8): https://plv8.github.io - hyperloglog: https://github.com/citusdata/postgresql-hll/releases/tag/v2....

HyperLogLog, also by citusdata (no affiliation), is a great data type for statistically approximating counts of distinct values very quickly within an adjustable range of error: https://github.com/citusdata/postgresql-hll This has been a great secret weapon for me in a few big counting problems.

> We’ve been big fans of the Postgres HyperLogLog extension for many years and are excited to be taking on responsibility to maintain HyperLogLog going forward. Conceptually hyperloglog should be really simple (count leading zeroes) so I wasn't sure what there was to "maintain". Looking at the citus repo it seems like they made a lot of interesting improvements to the original algorithm: https://github.com/citusdata/postgresql-hll Interesting how fitting a simple theory work to the real world takes 3.3k lines of code.

"We are migrating our analytics db from Mongo to PG but we hit a wall because SELECT COUNT(DISTINCT x)) performs abysmally in Postgres." https://github.com/aggregateknowledge/postgresql-hll ? or even https://www.periscopedata.com/blog/hyperloglog-in-pure-sql.h... ?

There's a postgres extension that implements hll for postgres. Rather useful: https://github.com/aggregateknowledge/postgresql-hll

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 Being able to switch algorithms based on size IMO makes that one of the most badass thing about postgres right now.

As a quick side note, PostgreSQL had the HyperLogLog data type for a while: https://github.com/aggregateknowledge/postgresql-hll For people who need SQL and HyperLogLog, we found this extension to work pretty well.

On a related note, the recently released HyperLogLog extension for postgres: https://github.com/aggregateknowledge/postgresql-hll