Near real-time unique visitor analytics with roaring bitmaps

COUNT(DISTINCT ...) queries deduplicate at query time, so memory pressure and latency grow linearly with data volume—a bottleneck when you need subsecond UV counts at high QPS over hundreds of millions of records. Hologres solves this with a pre-aggregation approach built on roaring bitmaps: aggregate UIDs into compressed bitmaps during off-peak periods, then apply a fast OR operation at query time. This shifts the heavy work off the critical path and keeps query latency in the millisecond range regardless of underlying data volume.

Trade-off: Pre-aggregation requires periodic updates to the aggregation sink table, which increases maintenance overhead.

How it works

The roaring bitmap approach has two phases:

Pre-aggregation (off-peak): Read UIDs from the fact table, compress them into roaring bitmaps with RB_BUILD_AGG, and write one bitmap per dimension combination per day into an aggregation sink table. After aggregation, the sink table holds only millions of rows per day—not billions.
Query time: Apply RB_OR_AGG to merge bitmaps across the relevant date range, then call RB_CARDINALITY to count distinct UIDs. Because the sink table is small, results return in milliseconds.

This is more efficient than traditional COUNT(DISTINCT ...) because it avoids a full-dataset shuffle and deduplication at query time.

Roaring bitmaps support only 32-bit integers. The right method depends on your UID data type. Roaring bitmaps apply to any accurate cardinality estimation scenario—not just UV analytics. For example, you can count distinct products or brands on a livestream dashboard. For a full function reference, see roaringbitmap functions.

Choose a method

Method	UID type	Query pattern	Key requirement
Method 1: INT-type UIDs	Integer	Single-tag filtering	None—roaring bitmaps work directly on INT fields
Method 2: TEXT-type UIDs	String	Single-tag filtering	A mapping table to convert text UIDs to 32-bit integers
Method 3: Bucket-based (advanced)	Integer or string	Multi-tag intersection, union, or XOR (profile analysis)	Bucket column; use with Method 2 if UIDs are text

Method 1: UV analytics on INT-type UIDs

Use this method when UIDs are already integers and you filter on a single tag before counting distinct users.

Prerequisites

A Hologres instance with an active database
Write access to the database

Step 1: Set up tables

1. Enable the roaring bitmap extension.

Run this once per database in the public schema:

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Create the fact table.

The fact table stores all raw user events. Partition by day to keep writes and updates efficient:

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
    uid int,
    country text,
    prov text,
    city text,
    channel text,
    operator text,
    brand text,
    ip text,
    click_time text,
    year text,
    month text,
    day text,
    ymd text NOT NULL
)
PARTITION BY LIST (ymd);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
-- Distribute rows by uid to collocate data for per-user queries.
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
-- Set ymd as the clustering key and event time column for efficient date filtering.
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

3. Create the aggregation sink table.

The aggregation sink table stores one pre-built roaring bitmap per dimension combination per day. After aggregation, each day holds only millions of rows.

Design guidelines:

Guideline	Reason
Use a non-partitioned table, or partition by month or quarter	Day-level partitions produce many small files and increase memory usage
Set dimension fields (country, prov, city) as the distribution key	Accelerates dimension-based queries
If GROUP BY has more than three fields, use the most frequently queried fields as the distribution key	Avoids over-partitioning the distribution
Set dimension fields plus the date field as the primary key	Prevents duplicate inserts
Set the date field as the clustering key and event time column	Enables efficient date filtering

BEGIN;
CREATE TABLE dws_app_rb (
    rb_uid roaringbitmap,       -- Stores pre-aggregated UIDs for UV counting.
    country text,
    prov text,
    city text,
    ymd text NOT NULL,          -- Date field.
    pv integer,                 -- Pre-aggregated page view count.
    PRIMARY KEY (country, prov, city, ymd)
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;

Step 2: Build the aggregation sink table

Read from the fact table, convert UIDs to roaring bitmaps with RB_BUILD_AGG, and write the results to the aggregation sink table. The following example loads six months of data:

INSERT INTO dws_app_rb
SELECT
    RB_BUILD_AGG(uid),
    country,
    prov,
    city,
    ymd,
    COUNT(1)
FROM ods_app_detail
WHERE ymd >= '20231201' AND ymd <= '20240502'
GROUP BY country, prov, city, ymd;

Keep the aggregation sink table current using one of two update modes:

Update mode	When to use	How
Incremental update	The fact table is updated on a regular schedule (for example, a new partition lands each day)	Run `INSERT` for the new partition only
Full update	The fact table is updated irregularly, or you cannot identify which rows changed	Run `hg_insert_overwrite` to overwrite the entire sink table

Incremental update (append one day's data):

INSERT INTO dws_app_rb
SELECT
    RB_BUILD_AGG(uid),
    country,
    prov,
    city,
    ymd,
    COUNT(1)
FROM ods_app_detail
WHERE ymd = '20240503'
GROUP BY country, prov, city, ymd;

Full update (overwrite all data):

CALL hg_insert_overwrite('public.dws_app_rb', $$
    SELECT
        RB_BUILD_AGG(uid),
        country,
        prov,
        city,
        ymd,
        COUNT(1)
    FROM ods_app_detail
    WHERE ymd >= '20231201' AND ymd <= '20240503'
    GROUP BY country, prov, city, ymd
$$);

Step 3: Query UV and PV counts

All queries use RB_CARDINALITY(RB_OR_AGG(rb_uid)) to merge bitmaps across rows and count distinct UIDs. Results return in milliseconds.

Daily UV and PV:

SELECT
    RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    country,
    prov,
    city,
    SUM(pv) AS pv
FROM dws_app_rb
WHERE ymd = '20240329'
GROUP BY country, prov, city;

Monthly UV and PV:

SELECT
    RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    country,
    prov,
    city,
    SUM(pv) AS pv
FROM dws_app_rb
WHERE ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country, prov, city;

Method 2: UV analytics on TEXT-type UIDs

Most production systems store UIDs as strings. Because roaring bitmaps only accept 32-bit integers, this method adds a mapping table that assigns a stable auto-increment integer to each text UID. Everything else follows the same pattern as Method 1.

Prerequisites

A Hologres instance with an active database
Write access to the database

Step 1: Set up tables

1. Enable the roaring bitmap extension.

Run this once per database in the public schema:

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Create the fact table.

Same structure as Method 1, except uid is text:

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
    uid text,
    country text,
    prov text,
    city text,
    channel text,
    operator text,
    brand text,
    ip text,
    click_time text,
    year text,
    month text,
    day text,
    ymd text NOT NULL
)
PARTITION BY LIST (ymd);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

3. Create the aggregation sink table.

Same structure and design rules as Method 1:

BEGIN;
CREATE TABLE dws_app_rb (
    rb_uid roaringbitmap,
    country text,
    prov text,
    city text,
    ymd text NOT NULL,
    pv integer,
    PRIMARY KEY (country, prov, city, ymd)
);
CALL set_table_property('dws_app_rb', 'orientation', 'column');
CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
END;

4. Create the UID mapping table.

The uid_int32 column uses SERIAL, which auto-assigns a consecutive 32-bit integer to each new text UID. Use row-oriented storage (orientation: row) for fast point lookups by uid:

BEGIN;
CREATE TABLE uid_mapping (
    uid text NOT NULL,
    uid_int32 serial,
    PRIMARY KEY (uid)
);
-- Use uid as both the clustering key and distribution key for fast lookups.
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;

Important

TRUNCATE does not reset the SERIAL sequence. If you truncate and re-insert the mapping table multiple times, uid_int32 values can exceed the 32-bit limit. After each initialization, verify the range:

SELECT MAX(uid_int32), MIN(uid_int32) FROM uid_mapping;

Step 2: Build roaring bitmaps and populate the aggregation sink table

2a. Initialize the UID mapping table.

Import all distinct UIDs from the fact table. The following example loads six months of data:

INSERT INTO uid_mapping (uid)
SELECT DISTINCT uid FROM ods_app_detail
WHERE ymd >= '20231201' AND ymd <= '20240502';

Verify the row count:

SELECT COUNT(*) FROM uid_mapping;

2b. Keep the mapping table current.

When new UIDs appear in the fact table, add them without overwriting existing mappings:

INSERT INTO uid_mapping (uid)
SELECT DISTINCT uid FROM ods_app_detail
WHERE ymd = '20240503'
ON CONFLICT DO NOTHING;

2c. Populate the aggregation sink table.

Join the fact table with the mapping table to get 32-bit integers, then aggregate into roaring bitmaps:

WITH aggregation_src AS (
    SELECT
        B.uid_int32,
        A.country,
        A.prov,
        A.city,
        A.ymd
    FROM ods_app_detail A
    INNER JOIN uid_mapping B ON A.uid = B.uid
    WHERE A.ymd >= '20231201' AND A.ymd <= '20240502'
)
INSERT INTO dws_app_rb
SELECT
    RB_BUILD_AGG(uid_int32),
    country,
    prov,
    city,
    ymd,
    COUNT(1)
FROM aggregation_src
GROUP BY country, prov, city, ymd;

2d. Keep the aggregation sink table current.

Use the same update modes as Method 1:

Incremental update: Run INSERT for the latest partition after updating the mapping table.
Full update: Run hg_insert_overwrite to rebuild the entire sink table.

Step 3: Query UV and PV counts

Query patterns are identical to Method 1.

Daily UV and PV:

SELECT
    country,
    prov,
    city,
    RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    SUM(pv) AS pv
FROM dws_app_rb
WHERE ymd = '20240329'
GROUP BY country, prov, city;

Monthly UV and PV:

SELECT
    country,
    prov,
    RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    SUM(pv) AS pv
FROM public.dws_app_rb
WHERE ymd >= '20240301' AND ymd <= '20240331'
GROUP BY country, prov, city;

Method 3: Bucket-based UV analytics (advanced)

Use this method for profile analysis that requires joining multiple large tables—for example, a behavior table and a user property table—and computing cardinality across the intersection or union of different tag combinations. Splitting bitmaps into buckets distributes work across shards, enabling parallel execution and reducing I/O.

How it works

Each UID is assigned to a bucket based on its bit pattern. UIDs in the same bucket land on the same shard, so cross-table joins on the bucket column become local joins with no data shuffle.

Bucketing formula for 32-bit integers:

uid >> 24 AS bucket
-- The most significant 8 bits become the bucket ID (0-255).
-- The least significant 24 bits are stored as the bitmap value.

This distributes UIDs evenly across 256 buckets, which maps well to instances with up to 256 shards. If the number of shards exceeds 256, calculate the number of bits required as the round-up of log2(n) where n is the shard count, and adjust the formula accordingly.

Alternative formula for small UIDs (where the top 8 bits are often 0):

(uid >> 16) % 256 AS bucket
-- Keeps 256 buckets; each bucket holds 65,536 values.

If UIDs are TEXT-type, build a mapping table first using Method 2, then apply the bucketing formula to the mapped uid_int32 values.

Prerequisites

A Hologres instance with an active database
Write access to the database

Step 1: Set up tables

1. Enable the roaring bitmap extension.

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Create the source tables.

This example uses a user behavior table and a user property table:

-- User behavior table
BEGIN;
CREATE TABLE IF NOT EXISTS ods_user_behaviour_detail (
    uid int,
    operator text,
    channel text,
    shop_id text,
    time text,
    ymd text NOT NULL
);
CALL set_table_property('ods_user_behaviour_detail', 'orientation', 'column');
CALL set_table_property('ods_user_behaviour_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_user_behaviour_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_user_behaviour_detail', 'event_time_column', 'ymd');
COMMIT;

-- User property table
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase (
    uid int,
    age text,
    gender text,
    country text,
    prov text,
    city text
);
CALL set_table_property('dim_userbase', 'orientation', 'column');
CALL set_table_property('dim_userbase', 'distribution_key', 'uid');
COMMIT;

3. Create the aggregation sink tables.

Each sink table includes a bucket column. Set bucket as the distribution key so rows from both tables with the same bucket ID land on the same shard, enabling local joins:

-- Aggregation sink table for behavior data
BEGIN;
CREATE TABLE dws_user_behaviour_rb (
    rb_uid roaringbitmap,
    bucket int NOT NULL,
    operator text,
    channel text,
    shop_id text,
    time text,
    ymd text NOT NULL,
    PRIMARY KEY (operator, channel, shop_id, time, ymd, bucket)
);
CALL set_table_property('dws_user_behaviour_rb', 'orientation', 'column');
CALL set_table_property('dws_user_behaviour_rb', 'clustering_key', 'ymd');
CALL set_table_property('dws_user_behaviour_rb', 'event_time_column', 'ymd');
CALL set_table_property('dws_user_behaviour_rb', 'distribution_key', 'bucket');
END;

-- Aggregation sink table for property data
BEGIN;
CREATE TABLE IF NOT EXISTS dim_userbase_rb (
    rb_uid roaringbitmap,
    bucket int NOT NULL,
    age text,
    gender text,
    country text,
    prov text,
    city text,
    PRIMARY KEY (age, gender, country, prov, city, bucket)
);
CALL set_table_property('dim_userbase_rb', 'orientation', 'column');
CALL set_table_property('dim_userbase_rb', 'distribution_key', 'bucket'); -- Local joins on bucket.
COMMIT;

Step 2: Build roaring bitmaps and populate the aggregation sink tables

Compute the bucket ID with uid >> 24 and aggregate UIDs into roaring bitmaps using RB_BUILD_AGG.

Populate from the behavior table:

INSERT INTO dws_user_behaviour_rb
SELECT
    RB_BUILD_AGG(uid),
    uid >> 24 AS bucket, -- Most significant 8 bits = bucket ID; least significant 24 bits = bitmap.
    operator,
    channel,
    shop_id,
    time,
    ymd
FROM ods_user_behaviour_detail
WHERE ymd >= '20231201' AND ymd <= '20240503';

Populate from the property table:

INSERT INTO dim_userbase_rb
SELECT
    RB_BUILD_AGG(uid),
    uid >> 24 AS bucket,
    age,
    gender,
    country,
    prov,
    city
FROM dim_userbase
GROUP BY age, gender, country, prov, city, bucket;

Use the same incremental or full update modes as Method 1 to keep both sink tables current.

Step 3: Query UV counts with multi-tag filtering

Join the two sink tables on bucket and apply rb_and to compute the intersection. RB_OR_AGG merges bitmaps within each table before the intersection, and SUM(RB_CARDINALITY(...)) counts the distinct UIDs across all buckets.

Example: Count users where gender = 'man', country = 'Beijing', operator = 'Purchase', and shop_id = '1':

SELECT
    SUM(RB_CARDINALITY(rb_and(t1.rb_uid, t2.rb_uid)))
FROM (
    SELECT
        rb_or_agg(rb_uid) AS rb_uid,
        bucket
    FROM dws_user_behaviour_rb
    WHERE operator = 'Purchase'
      AND shop_id = '1'
      AND ymd = '20240501'
    GROUP BY bucket
) t1
JOIN (
    SELECT
        rb_or_agg(rb_uid) AS rb_uid,
        bucket
    FROM dim_userbase_rb
    WHERE gender = 'man'
      AND country = 'Beijing'
    GROUP BY bucket
) t2 ON t1.bucket = t2.bucket;