The hll extension adds the HyperLogLog (HLL) data type to PolarDB, enabling fast, memory-efficient estimation of distinct element counts. With 1,280 bytes of HLL data, it can accurately estimate billions of distinct values — useful for high-scale analytics such as page view (PV) and unique visitor (UV) counting in internet advertising.
Why HLL instead of COUNT DISTINCT
COUNT DISTINCT answers one question at a time: "How many unique users visited today?" To answer "How many unique users visited this week?", you must re-scan all raw data.
HLL sketches are additive: store daily HLL aggregates once, then union them across any time window without re-reading raw data. Weekly uniques, monthly uniques, 7-day sliding windows, and lost-user counts all come from a single pre-aggregated table — returning in milliseconds instead of minutes.
Prerequisites
Before you begin, ensure that you have:
PolarDB PostgreSQL 14 (kernel minor version 14.5.2.0 or later), or PolarDB PostgreSQL 11 (kernel minor version 1.1.28 or later)
To check your kernel version, run:
SHOW polar_version;Install the extension
CREATE EXTENSION hll;Data types
| Data type | Description |
|---|---|
hll | An HLL sketch. Stores a compressed probabilistic representation of a set of hashed values. |
hll_hashval | A hashed element. Hash raw values into hll_hashval before adding them to an HLL sketch. |
Operators
hll operators
| Operator | Description | Example |
|---|---|---|
= | Equality | hll_add_agg(1::hll_hashval) = hll_add_agg(2::hll_hashval) |
!=, <> | Inequality | — |
|| | Add an element to an HLL sketch, or union two HLL sketches | hll_add_agg(1::hll_hashval) || hll_add_agg(2::hll_hashval) |
# | Estimate the number of distinct elements | #hll_add_agg(1::hll_hashval) |
hll_hashval operators
| Operator | Description |
|---|---|
= | Equality |
!=, <> | Inequality |
Functions
Hash functions
Convert raw values into hll_hashval before inserting into an HLL sketch.
| Function | Input type | Example |
|---|---|---|
hll_hash_boolean(val) | boolean | SELECT hll_hash_boolean(true); |
hll_hash_smallint(val) | smallint | — |
hll_hash_integer(val) | integer | SELECT hll_hash_integer(1); |
hll_hash_bigint(val) | bigint | — |
Aggregate and set functions
| Function | Description | Example |
|---|---|---|
hll_add_agg(hll_hashval) | Aggregate function. Accepts pre-hashed hll_hashval inputs and inserts each into an HLL sketch. | SELECT hll_add_agg(1::hll_hashval); |
hll_union(hll, hll) | Returns the union of two HLL sketches. Use this to merge exactly two pre-aggregated sketches. | SELECT hll_union(hll_add_agg(1::hll_hashval), hll_add_agg(2::hll_hashval)); |
hll_set_defaults(log2m, regwidth, expthresh, sparseon) | Sets the default HLL parameters for the session. log2m controls the number of registers (higher = more accurate, more memory); regwidth controls bits per register; expthresh controls the threshold for switching from explicit to probabilistic mode; sparseon enables sparse representation. | SELECT hll_set_defaults(15, 5, -1, 1); |
hll_print(hll) | Returns debug information about an HLL sketch. | SELECT hll_print(hll_add_agg(1::hll_hashval)); |
Example: track daily unique visitors
This example shows the full workflow — from storing daily HLL aggregates to querying distinct visitor counts across multiple time windows.
Create the table and insert aggregated data:
CREATE TABLE access_date (acc_date DATE UNIQUE, userids hll);
-- Day 0: users 1-10,000
INSERT INTO access_date
SELECT current_date, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(1, 10000) t(user_id);
-- Day -1: users 5,000-20,000
INSERT INTO access_date
SELECT current_date - 1, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(5000, 20000) t(user_id);
-- Day -2: users 9,000-40,000
INSERT INTO access_date
SELECT current_date - 2, hll_add_agg(hll_hash_integer(user_id))
FROM generate_series(9000, 40000) t(user_id);Query distinct visitors per day using the `#` (cardinality) operator:
SELECT #userids FROM access_date WHERE acc_date = current_date;
-- ?column?
-- ------------------
-- 9725.852733707077
SELECT #userids FROM access_date WHERE acc_date = current_date - 1;
-- ?column?
-- ------------------
-- 14968.65968832792
SELECT #userids FROM access_date WHERE acc_date = current_date - 2;
-- ?column?
-- ------------------
-- 29361.520914991113Merge HLL sketches across days to query distinct visitors over a multi-day window:
-- Distinct visitors over days 0 and -1 combined
SELECT #hll_union(
(SELECT userids FROM access_date WHERE acc_date = current_date),
(SELECT userids FROM access_date WHERE acc_date = current_date - 1)
);Uninstall the extension
DROP EXTENSION hll;