---
title: "HyperLogLog"
description: "HyperLogLog is a probabilistic algorithm for estimating the number of distinct elements in a large dataset (cardinality) using a small, fixed amount of memory, at the cost of a small, predictable error margin."
canonical: "https://motherduck.com/glossary/hyperloglog/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
  - title: "DuckLake Lakehouse: Getting Started to Going Fast | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-lakehouse-getting-started/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# HyperLogLog

> HyperLogLog is a probabilistic algorithm for estimating the number of distinct elements in a large dataset (cardinality) using a small, fixed amount of memory, at the cost of a small, predictable error margin.

## Overview

Counting exact distinct values over a huge dataset normally requires tracking every unique value seen, which uses memory proportional to the number of distinct items — potentially gigabytes for a large enough dataset. HyperLogLog (HLL) is an algorithm that estimates that same distinct count using a small, fixed-size data structure (often just a few kilobytes) regardless of how many total or distinct values are processed, by cleverly hashing values and tracking statistical patterns in the hash bits. The trade-off is a small amount of estimation error, typically around 1-2% for standard configurations, in exchange for massive memory savings and the ability to merge cardinality estimates across data partitions (useful in distributed systems).

## Approximate distinct counting in DuckDB

DuckDB implements `approx_count_distinct`, which uses HyperLogLog internally to estimate `COUNT(DISTINCT ...)` without materializing every distinct value.

```sql
SELECT approx_count_distinct(user_id) AS estimated_unique_users
FROM events;
```

Against a large table, this can be dramatically faster and use far less memory than the exact `COUNT(DISTINCT user_id)`, particularly when the column has very high cardinality. It's well suited to dashboards and monitoring use cases where an estimate within a percent or two is perfectly acceptable, and less suited to contexts that require an exact count, such as financial reporting or billing.

## Why it matters

HyperLogLog (and its variants) is widely used across analytics systems — including Redis's `PFCOUNT`, Postgres extensions, Spark, and BigQuery's `APPROX_COUNT_DISTINCT` — as the standard technique for fast, low-memory approximate cardinality estimation at scale.