Use the hll extension to estimate unique counts - PolarDB

COUNT DISTINCT rescans the entire dataset every time you ask a new aggregation question—whether per day, per week, or across a rolling window. For large tables with hundreds of millions of rows, each query can take minutes. The hll (HyperLogLog) extension stores a compact probabilistic sketch of your data instead. A single hll value uses 1,280 bytes and can estimate billions of distinct elements within a configurable accuracy bound. Once built, sketches can be merged instantly with hll_union, letting you answer multi-day or rolling-window cardinality queries—such as unique visitor (UV) counts—without re-reading raw data.

How it works

HyperLogLog is a probabilistic algorithm for cardinality estimation. Rather than recording every distinct value, it maintains a fixed-size register array that encodes a statistical summary. You hash each input value into an hll_hashval, then aggregate those hash values into a sketch using hll_add_agg. To read the estimated count of distinct elements (the cardinality), apply the # operator to the sketch.

Install the hll extension

CREATE EXTENSION hll;

Key concepts

Concept	Description
`hll`	The sketch data type. Stores a compact, fixed-size representation of a set.
`hll_hashval`	An intermediate hash value produced by an hll hash function. All inputs must be hashed before being added to a sketch.
Cardinality	The estimated count of distinct elements in an hll sketch.

Operators and functions

Operators by data type

hll

Operator	Description
`=`	Equality
`!=`, `<>`	Inequality
`\|\|`	Add an `hll_hashval` element, or merge two hll sketches
`#`	Return the estimated cardinality

hll_hashval

Operator	Description
`=`	Equality
`!=`, `<>`	Inequality

Hash functions

Hash every input value before adding it to a sketch. Choose the function that matches your column type.

Function	Input type
`hll_hash_boolean`	boolean
`hll_hash_smallint`	smallint
`hll_hash_integer`	integer
`hll_hash_bigint`	bigint

SELECT hll_hash_boolean(true);
SELECT hll_hash_integer(1);

Aggregate and utility functions

Function	Description
`hll_add_agg(hll_hashval)`	Aggregates `hll_hashval` values into a single hll sketch
`hll_union(hll, hll)`	Merges two hll sketches into one
`hll_set_defaults(log2m, regwidth, expthresh, sparseon)`	Sets global accuracy and storage defaults
`hll_print(hll)`	Displays debug information about an hll value

Estimate daily UVs

The following example tracks daily unique visitors (UVs) to a site. Each row stores one day's visitor set as an hll sketch. Because sketches can be merged without re-reading the source rows, this pattern answers per-day cardinality questions far faster than COUNT DISTINCT on the raw table.

Set up the table

CREATE TABLE access_date (acc_date DATE UNIQUE, userids hll);

Populate sample data

Each INSERT hashes integer user IDs and aggregates them into an hll sketch with hll_add_agg.

-- Today: 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);

-- Yesterday: users 5,000-20,000 (overlapping with today)
INSERT INTO access_date
    SELECT current_date - 1, hll_add_agg(hll_hash_integer(user_id))
    FROM generate_series(5000, 20000) t(user_id);

-- Two days ago: 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 per-day UV estimates

SELECT #userids FROM access_date WHERE acc_date = current_date;
--  9725.852733707077

SELECT #userids FROM access_date WHERE acc_date = current_date - 1;
--  14968.65968832792

SELECT #userids FROM access_date WHERE acc_date = current_date - 2;
--  29361.520914991113

Tune accuracy and storage

hll_set_defaults controls the tradeoff between estimation accuracy and storage size. The two primary parameters are log2m (number of registers, as a power of 2) and regwidth (bits per register).

Higher log2m and regwidth values improve accuracy and raise the maximum estimable cardinality, at the cost of more storage per sketch.

-- Set log2m=15, regwidth=5
SELECT hll_set_defaults(15, 5, -1, 1);

Parameter reference

Parameter	Description
`log2m`	Log base-2 of the number of registers. Higher values improve accuracy.
`regwidth`	Bits per register. Controls the maximum cardinality that can be estimated.
`expthresh`	Threshold for switching from sparse to explicit representation. Use `-1` for automatic.
`sparseon`	Enables (`1`) or disables (`0`) sparse representation for small sets.

Debug an hll value

SELECT hll_print(hll_add_agg(1::hll_hashval));

hll_print displays debug information about an hll value. Use it to verify configuration or inspect unexpected estimates.

Remove the hll extension

DROP EXTENSION hll;