PolarDB:pg_roaringbitmap

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.

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

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

-- 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.

Functions