Docs

RDS for PostgreSQL

Extensions

HyperLogLog algorithm extension (postgresql-hll)

Introduction

postgresql-hll is a PostgreSQL extension based on the HyperLogLog algorithm. It introduces a custom data type hll to solve large-scale data cardinality estimation (count distinct) issues, such as billions or more, with minimal storage space and very fast computation speed.

Core advantages

Efficient storage and performance: hll data objects require very small and fixed storage space (usually less than 16KB), which no longer increases linearly with the growth of raw data volume. It has an extremely fast calculation speed, typically completing within milliseconds.

Native aggregation capability: it supports lossless merging (union) of hll objects at any dimension. For example, it can easily merge daily user visit records (DAU) into weekly (WAU) or monthly (MAU) unique user counts without rescanning the raw data.

Reliable estimation accuracy: the default configuration achieves a standard error of about 0.8%, balancing accuracy and resource consumption effectively. The extension also includes sparse and dense storage modes and can automatically switch based on data volume to optimize storage efficiency.

Typical uses

Website/App activity statistics: calculate key indicators such as daily active users (DAU) and monthly active users (MAU).

large-scale deduplication counting: count unique IP addresses in massive logs and the users visiting different products/contents.

Real-time data analysis: use its efficient merging features to perform sliding window analysis, such as calculating the rolling UV of the past 7 days.

Precautions

Before using the hll extension, understand and evaluate the following key limitations to avoid unexpected situations in production environments.

warning

It is a probabilistic data structure, not an exact storage.

Imprecise calculation

hll provides an estimated value of cardinality, not an exact value. Although the error rate is low, it is essentially a probabilistic statistical model. Therefore, it is strictly prohibited to use it in any scenario requiring 100% accuracy, such as financial accounting, inventory management, and order counting.

Irreversible data

hll stores digest information after hashing the original data, making it impossible to restore any original values from it. For example, you cannot recover a specific list of user IDs from an hll object aggregating user IDs. It is only used for "counting," not for "tracing."

Strong extension dependency

If your data table contains hll columns, reading and writing to that table will strongly depend on the hll extension. If this extension is uninstalled (DROP EXTENSION hll;), all data in hll columns will become unreadable (appearing as garbled or errors), which is equivalent to data loss.

Hash function consistency

The merging (Union) of hll objects requires their hash configurations to be completely consistent. If you use hll in different systems or environments (for example, computing hash values on the Java server or aggregating in PostgreSQL), you must ensure that the hash algorithm and seed used are the same on both sides. Otherwise, the generated hll objects cannot be merged, and the calculated union result will be incorrect.

Data migration and synchronization risks

When using DTS (Data Transmission Service) or other tools for database migration, synchronization, or backups, you must ensure that the target database instance has the hll extension installed in advance. Otherwise, during data import, the hll data columns will cause migration failure or data corruption due to an unrecognized type.

Installation and uninstallation

Install extension

In the database where the hll function is required, execute the following SQL command to install the extension.

After successful installation, you can confirm it by querying the pg_extension system table.

If the query returns results, the extension is installed and available for use.

Uninstall extension

danger

Uninstalling the extension will disable all hll data columns, making the data unreadable. Before performing this operation, confirm that no business depends on hll data or that data migration is complete.

If you are sure the hll extension is no longer needed, execute the following command to uninstall.

Usage

The core workflow of the postgresql-hll extension generally includes three steps:

Hash: convert the original values requiring distinct counting (e.g., user ID and IP address) to hash values using a hash function.

Aggregate: add these hash values into an hll data object for aggregation.

Calculate: estimate cardinality (distinct count) through the hll object, or merge multiple hll objects to compute the union.

Hash and aggregation

hll extension does not directly process the original value but processes its hash values. The most common method is to combine the use of hll_hash_* series functions and the hll_add_agg aggregation function.

Example: counting unique visitors (UV) by days

Assuming we have a raw log table recording user access behaviors called page_views:

Now, we want to pre-calculate daily UV data and store it in a result table called daily_uniques.

Create a pre-aggregation table

First, create a table for storing daily UV data, where the type of the users column is hll.

CREATE TABLE daily_uniques (
stat_date DATE PRIMARY KEY,
users HLL
);

Hash and aggregation data

Read data from the page_views table, and use hll_hash_text to convert user_id to hash values. Then, aggregate by day using hll_add_agg to generate hll objects, and insert into the daily_uniques table.

INSERT INTO daily_uniques(stat_date, users)
SELECT
DATE(log_time) AS stat_date,
hll_add_agg(hll_hash_text(user_id)) AS users -- core actions
FROM
page_views
GROUP BY
stat_date;

tip

hll provides various hash functions to support different data types, such as hll_hash_bigint, hll_hash_int, and hll_hash_bytea. Select the appropriate hash function based on your data type.

Cardinality estimation

After data is stored as hll type, you can use the hll_cardinality function to retrieve its estimated cardinality (i.e., the deduplicated quantity).

Example: query the UV of a specific day

Lossless merging

Lossless merging is one of postgresql-hll’s most powerful features. You can merge hll objects stored in different rows (e.g., records from different dates) to calculate the deduplicated count over the entire time range without rescanning massive raw data.

Example: calculate monthly active users (MAU)

Use the hll_union_agg function to merge all daily hll objects within a month into one, then calculate its total cardinality.

Sliding window analysis

Due to the high performance of hll merging, it is ideal for calculating metrics within a rolling time window, such as "rolling active users in the past 7 days".

Example: calculating 7-day rolling UV

By combining window functions and hll_union_agg, sliding window calculations can be efficiently implemented.

Default configuration description

When creating an hll object, the extension uses a set of default parameters (e.g., log2m=11 and regwidth=5) that determine the precision and storage size of the hll object. In most scenarios, the default configuration (with an error rate of about ±0.8%) already offers the best balance of performance and precision, users do not need to adjust it manually.

hll_empty(): this function creates an empty hll object based on the default parameters.

hll_add_agg(...): this aggregation function also creates an hll object using the default parameters.

danger

hll objects with different parameter configurations cannot be merged. To ensure data mergeability, it is strongly recommended to consistently use the unified default configuration throughout the database and avoid manually specifying different parameters during creation or aggregation.

FAQs and troubleshooting

Q: Why is there a deviation between the hll calculation result and the accurate COUNT(DISTINCT ...) result? A: This is normal. hll is a probabilistic data structure designed to trade minimal storage space for a sufficiently accurate estimate. It has inherent statistical error (about ±0.8% under the default configuration). If you need 100% accurate results, use the traditional COUNT(DISTINCT ...), but be prepared for significant computation and memory overhead.

Q: Why can't two hll objects be merged? A: merge failure usually occurs because the internal configuration parameters of the two hll objects differ. For example, one hll object was created with a non-default value of log2m, while the other used the default value. Make sure all hll objects involved in the merge share the same configuration. The simplest approach is to always use the default behavior of hll_empty() and hll_add_agg without manually specifying parameters.

Q: why hll data columns appear garbled or query errors? A: The most common reason for this is that the hll extension is not installed or enabled in the current database connection. Execute CREATE EXTENSION IF NOT EXISTS hll; to ensure the extension is available. Another possibility is that the extension was accidentally uninstalled, which will cause data to be unrecoverable. Refer to the risks described in the "Precautions" section.

Q: after data migration (DTS), data in hll column is unusable. Why? A: This usually occurs because the hll extension is not installed in the target database when importing data. The migration tool cannot recognize the hll type and may mistakenly import the data as ordinary binary data. Ensure the hll extension is installed in the target database before starting the migration task.

Last updated: 2026.05.09 16:35:06