postgresql-hll extension

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

The postgresql-hll extension adds a new data type hll, which is a HyperLogLog data structure. HyperLogLog is a fixed-size, set-like structure used for distinct value counting with tunable precision. For example, in 1280 bytes hll can estimate the count of tens of billions of distinct values with only a few percent error.

First, enable the extension:

CREATE EXTENSION "hll";

To run the helloworld example from the postgresql-hll repository, connect using ysqlsh and run the following:

CREATE TABLE helloworld (id integer, set hll);
CREATE TABLE

Insert an empty HLL as follows:

INSERT INTO helloworld(id, set) VALUES (1, hll_empty());
INSERT 0 1

Add a hashed integer to the HLL as follows:

UPDATE helloworld SET set = hll_add(set, hll_hash_integer(12345)) WHERE id = 1;
UPDATE 1

Add a hashed string to the HLL as follows:

UPDATE helloworld SET set = hll_add(set, hll_hash_text('hello world')) WHERE id = 1;
UPDATE 1

Get the cardinality of the HLL as follows:

SELECT hll_cardinality(set) FROM helloworld WHERE id = 1;
 hll_cardinality
-----------------
               2
(1 row)

For a more advanced example, see the Data Warehouse Use Case.