hll

Overview

Version

Install

You can install hll directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install hll;          # Install for current active PG version

pig ext install -y hll -v 18  # PG 18
pig ext install -y hll -v 17  # PG 17
pig ext install -y hll -v 16  # PG 16
pig ext install -y hll -v 15  # PG 15
pig ext install -y hll -v 14  # PG 14

dnf install -y hll_18       # PG 18
dnf install -y hll_17       # PG 17
dnf install -y hll_16       # PG 16
dnf install -y hll_15       # PG 15
dnf install -y hll_14       # PG 14

apt install -y postgresql-18-hll   # PG 18
apt install -y postgresql-17-hll   # PG 17
apt install -y postgresql-16-hll   # PG 16
apt install -y postgresql-15-hll   # PG 15
apt install -y postgresql-14-hll   # PG 14

Create Extension:

CREATE EXTENSION hll;

Usage

hll: type for storing hyperloglog data

The hll extension provides a HyperLogLog data type for probabilistic distinct-value counting. It enables efficient approximate COUNT(DISTINCT) operations with configurable accuracy, and supports set union operations that allow pre-aggregated data to be combined without loss of precision.

Data Types

• hll – HyperLogLog accumulator with parameters: hll(log2m, regwidth, expthresh, sparseon)
• hll_hashval – Hashed value type for insertion into HLL structures

Core Functions

Hash Functions

Operators

Example: Daily Unique User Tracking

-- Store daily unique user counts
CREATE TABLE daily_uniques (
    date  date UNIQUE,
    users hll
);

-- Aggregate daily data
INSERT INTO daily_uniques(date, users)
    SELECT date, hll_add_agg(hll_hash_integer(user_id))
    FROM facts GROUP BY 1;

-- Weekly uniques (unions are lossless)
SELECT hll_cardinality(hll_union_agg(users))
FROM daily_uniques
WHERE date >= '2012-01-02' AND date <= '2012-01-08';

-- Monthly breakdown
SELECT EXTRACT(MONTH FROM date) AS month,
       #hll_union_agg(users) AS approx_uniques
FROM daily_uniques
WHERE date >= '2012-01-01' AND date < '2013-01-01'
GROUP BY 1;

-- 7-day sliding window
SELECT date, #hll_union_agg(users) OVER seven_days
FROM daily_uniques
WINDOW seven_days AS (ORDER BY date ASC ROWS 6 PRECEDING);

Configuration Parameters

• log2m (4–31): Number of registers as log-base-2. Controls accuracy with relative error of +/-1.04/sqrt(2^log2m). Default: 11.
• regwidth (1–8): Bits per register. Tuned alongside log2m for maximum cardinality estimation. Default: 5.
• expthresh (-1 to 18): Controls EXPLICIT-to-SPARSE promotion. -1 for auto mode, 0 to skip EXPLICIT. Default: -1.
• sparseon (0 or 1): Enables/disables SPARSE representation. Default: 1.

All inputs to a given HLL must use the same hash seed. HLLs intended for union operations must have been populated with identically-seeded hash values.