Use the pg_roaringbitmap extension - PolarDB - Alibaba Cloud Documentation Center

The pg_roaringbitmap extension adds the roaringbitmap data type to PolarDB for PostgreSQL, enabling efficient bitmap operations directly in SQL. Roaring bitmaps outperform traditional compressed bitmap formats such as WAH, EWAH, and Concise—in some scenarios delivering indexing speeds hundreds of times faster, and even faster than uncompressed bitmaps.

Typical use cases: user segmentation, tag-based filtering, deduplication, and set membership queries at scale.

Prerequisites

Before you begin, make sure you have:

A PolarDB for PostgreSQL instance
Permission to run CREATE EXTENSION on the target database

Enable the extension

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

Verify the installation:

SELECT extname, extversion FROM pg_extension WHERE extname = 'roaringbitmap';

Expected output:

    extname    | extversion
---------------+------------
 roaringbitmap | 0.5
(1 row)

Quick start

The following steps walk through a complete workflow: create a table, insert bitmap data, run calculations, and retrieve results.

Step 1: Create a table with a `roaringbitmap` column.

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

Step 2: Insert bitmap data.

-- rb_build: sets bits at the positions listed in the integer array
INSERT INTO t1 SELECT 1, rb_build(ARRAY[1,2,3,4,5,6,7,8,9,200]);

-- rb_build_agg: aggregates multiple row values into a single bitmap,
-- setting a bit for each row value
INSERT INTO t1 SELECT 2, rb_build_agg(e) FROM generate_series(1,100) e;

Step 3: Run bitmap calculations (OR, AND, XOR, ANDNOT).

SELECT roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}');  -- OR
SELECT roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}');  -- AND
SELECT roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}');  -- XOR
SELECT roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');  -- ANDNOT

Step 4: Run bitmap aggregate calculations.

SELECT rb_or_agg(bitmap)  FROM t1;
SELECT rb_and_agg(bitmap) FROM t1;
SELECT rb_xor_agg(bitmap) FROM t1;
SELECT rb_build_agg(e)    FROM generate_series(1,100) e;

Step 5: Get the cardinality (count of set bits).

SELECT rb_cardinality('{1,2,3}');

Step 6: Retrieve the integer values stored in a bitmap.

-- Convert to an integer array
SELECT rb_to_array(bitmap) FROM t1 WHERE id = 1;

-- Expand to a set of rows
SELECT unnest(rb_to_array('{1,2,3}'::roaringbitmap));

-- Alternatively, use rb_iterate
SELECT rb_iterate('{1,2,3}'::roaringbitmap);

Input and output formats

PolarDB supports array and bytea as input and output formats.

Input

-- array input
SELECT roaringbitmap('{1,100,10}');
--                    roaringbitmap
-- ------------------------------------------------
--  \x3a30000001000000000002001000000001000a006400

-- bytea input
SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
--                    roaringbitmap
-- ------------------------------------------------
--  \x3a30000001000000000002001000000001000a006400

Output

The default output format is bytea. Use roaringbitmap.output_format to switch formats.

-- Switch to array output
SET roaringbitmap.output_format = 'array';
SELECT '{1}'::roaringbitmap;
--  roaringbitmap
-- ---------------
--  {1}

-- Switch back to bytea output
SET roaringbitmap.output_format = 'bytea';
SELECT '{1}'::roaringbitmap;
--              roaringbitmap
-- ----------------------------------------
--  \x3a3000000100000000000000100000000100

Operators

All operators return roaringbitmap unless the output type is listed as bool.

Operator	Input	Output	Description	Example	Result
`&`	roaringbitmap, roaringbitmap	roaringbitmap	Bitwise AND	`roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}')`	`{3}`
`\|`	roaringbitmap, roaringbitmap	roaringbitmap	Bitwise OR	`roaringbitmap('{1,2,3}') \| roaringbitmap('{3,4,5}')`	`{1,2,3,4,5}`
`\|`	roaringbitmap, integer	roaringbitmap	Add element	`roaringbitmap('{1,2,3}') \| 6`	`{1,2,3,6}`
`\|`	integer, roaringbitmap	roaringbitmap	Add element	`6 \| roaringbitmap('{1,2,3}')`	`{1,2,3,6}`
`#`	roaringbitmap, roaringbitmap	roaringbitmap	Bitwise XOR	`roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}')`	`{1,2,4,5}`
`<<`	roaringbitmap, bigint	roaringbitmap	Bitwise left shift	`roaringbitmap('{1,2,3}') << 2`	`{0,1}`
`>>`	roaringbitmap, bigint	roaringbitmap	Bitwise right shift	`roaringbitmap('{1,2,3}') >> 3`	`{4,5,6}`
`-`	roaringbitmap, roaringbitmap	roaringbitmap	ANDNOT (difference)	`roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}')`	`{1,2}`
`-`	roaringbitmap, integer	roaringbitmap	Remove element	`roaringbitmap('{1,2,3}') - 3`	`{1,2}`
`@>`	roaringbitmap, roaringbitmap	bool	Contains	`roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}')`	`f`
`@>`	roaringbitmap, integer	bool	Contains element	`roaringbitmap('{1,2,3,4,5}') @> 3`	`t`
`@>`	roaringbitmap, integer	bool	Contains element	`roaringbitmap('{1,2,3}') @> 4`	`f`
`@>`	integer, roaringbitmap	bool	Contains	`3 @> roaringbitmap('{1,2,3,4,5}')`	`t`
`&&`	roaringbitmap, roaringbitmap	bool	Intersects (logical AND)	`roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}')`	`t`
`=`	roaringbitmap, roaringbitmap	bool	Equal	`roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}')`	`f`
`<>`	roaringbitmap, roaringbitmap	bool	Not equal	`roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}')`	`t`

Functions

Functionality functions

Function	Input	Output	Description	Example	Result
`rb_build`	integer[]	roaringbitmap	Create a bitmap from an integer array	`rb_build('{1,2,3,4,5}')`	`{1,2,3,4,5}`
`rb_index`	roaringbitmap, integer	bigint	Return the 0-based index of an element, or -1 if it does not exist	`rb_index('{1,2,3}', 3)`	`2`
`rb_cardinality`	roaringbitmap	bigint	Return the cardinality	`rb_cardinality('{1,2,3,4,5}')`	`5`
`rb_and_cardinality`	roaringbitmap, roaringbitmap	bigint	Cardinality of the AND of two bitmaps	`rb_and_cardinality('{1,2,3}', rb_build('{3,4,5}'))`	`1`
`rb_or_cardinality`	roaringbitmap, roaringbitmap	bigint	Cardinality of the OR of two bitmaps	`rb_or_cardinality('{1,2,3}', '{3,4,5}')`	`5`
`rb_xor_cardinality`	roaringbitmap, roaringbitmap	bigint	Cardinality of the XOR of two bitmaps	`rb_xor_cardinality('{1,2,3}', '{3,4,5}')`	`4`
`rb_andnot_cardinality`	roaringbitmap, roaringbitmap	bigint	Cardinality of the ANDNOT of two bitmaps	`rb_andnot_cardinality('{1,2,3}', '{3,4,5}')`	`2`
`rb_is_empty`	roaringbitmap	boolean	Check if the bitmap is empty	`rb_is_empty('{1,2,3,4,5}')`	`f`
`rb_fill`	roaringbitmap, range_start bigint, range_end bigint	roaringbitmap	Add all integers in [range_start, range_end)	`rb_fill('{1,2,3}', 5, 7)`	`{1,2,3,5,6}`
`rb_clear`	roaringbitmap, range_start bigint, range_end bigint	roaringbitmap	Remove all integers in [range_start, range_end)	`rb_clear('{1,2,3}', 2, 3)`	`{1,3}`
`rb_flip`	roaringbitmap, range_start bigint, range_end bigint	roaringbitmap	Negate all bits in [range_start, range_end)	`rb_flip('{1,2,3}', 2, 10)`	`{1,4,5,6,7,8,9}`
`rb_range`	roaringbitmap, range_start bigint, range_end bigint	roaringbitmap	Return elements in [range_start, range_end)	`rb_range('{1,2,3}', 2, 3)`	`{2}`
`rb_range_cardinality`	roaringbitmap, range_start bigint, range_end bigint	bigint	Cardinality of elements in [range_start, range_end)	`rb_range_cardinality('{1,2,3}', 2, 3)`	`1`
`rb_min`	roaringbitmap	integer	Smallest element; NULL if empty	`rb_min('{1,2,3}')`	`1`
`rb_max`	roaringbitmap	integer	Largest element; NULL if empty	`rb_max('{1,2,3}')`	`3`
`rb_rank`	roaringbitmap, integer	bigint	Count of elements less than or equal to the given value	`rb_rank('{1,2,3}', 3)`	`3`
`rb_jaccard_dist`	roaringbitmap, roaringbitmap	double precision	Jaccard distance (Jaccard similarity coefficient) of two bitmaps	`rb_jaccard_dist('{1,2,3}', '{3,4}')`	`0.25`
`rb_select`	roaringbitmap, bitset_limit bigint, bitset_offset bigint=0, reverse boolean=false, range_start bigint=0, range_end bigint=4294967296	roaringbitmap	Return subset [offset, offset+limit) of elements in [range_start, range_end)	`rb_select('{1,2,3,4,5,6,7,8,9}', 5, 2)`	`{3,4,5,6,7}`
`rb_to_array`	roaringbitmap	integer[]	Convert to an integer array	`rb_to_array(roaringbitmap('{1,2,3}'))`	`{1,2,3}`
`rb_iterate`	roaringbitmap	SET of integer	Return elements as a set of rows	`SELECT rb_iterate(rb_build('{1,2,3}'))`	`1`, `2`, `3`

Aggregate functions

Function	Input	Output	Description	Example	Result
`rb_build_agg`	integer	roaringbitmap	Build a bitmap from a set of integer rows	`SELECT rb_build_agg(id) FROM (VALUES (1),(2),(3)) t(id)`	`{1,2,3}`
`rb_or_agg`	roaringbitmap	roaringbitmap	OR aggregate across a set of bitmaps	`SELECT rb_or_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`{1,2,3,4}`
`rb_and_agg`	roaringbitmap	roaringbitmap	AND aggregate across a set of bitmaps	`SELECT rb_and_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`{2,3}`
`rb_xor_agg`	roaringbitmap	roaringbitmap	XOR aggregate across a set of bitmaps	`SELECT rb_xor_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`{1,4}`
`rb_or_cardinality_agg`	roaringbitmap	bigint	OR aggregate, return cardinality	`SELECT rb_or_cardinality_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`4`
`rb_and_cardinality_agg`	roaringbitmap	bigint	AND aggregate, return cardinality	`SELECT rb_and_cardinality_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`2`
`rb_xor_cardinality_agg`	roaringbitmap	bigint	XOR aggregate, return cardinality	`SELECT rb_xor_cardinality_agg(bitmap) FROM (VALUES (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	`2`