// the find
citusdata/postgresql-hll
PostgreSQL extension adding HyperLogLog data structures as a native data type
A PostgreSQL extension that adds an `hll` column type holding a HyperLogLog sketch, so you can store approximate distinct counts per day or per segment and combine them later with `hll_union_agg` instead of rerunning COUNT(DISTINCT) against a large fact table. It suits people running analytics rollups in Postgres who will accept a few percent error in exchange for fixed-size storage and fast reads.
Unions are lossless: `hll_union_agg` over stored daily sketches gives the same estimate as sketching the raw rows, so weekly, monthly and sliding-window uniques come from rollups without touching the fact table. The EMPTY, EXPLICIT, SPARSE, FULL promotion ladder keeps small sets exact and small sketches small, and log2m, regwidth, expthresh and sparseon let you choose the accuracy-to-memory trade per column. The storage layout is a published spec (hll-storage-spec v1.0.0) with Java, JS and Go implementations, so sketches can leave Postgres. The regression suite is deep for a C extension: the expected-output cases cover promotion boundaries, unions across every representation pair, binary COPY, and MurmurHash3 known-answer vectors.
Hashing is entirely the caller's job. Every input has to go through `hll_hash_*` with one constant seed, and sketches built with different seeds will union into silently wrong counts; the type system only blocks raw integers, not mismatched seeds. Intersections by inclusion-exclusion are noisy because the error scales with the union, and the README's own example (a 1B set and a 10M set sharing 5M) shows a 1% error in the larger sketch can swamp the answer. Defaults are compile-time constants in hll.c (DEFAULT_LOG2M and friends), so changing them means rebuilding the extension, and expthresh stops at 18. It does not build on 32-bit systems.