All Products
Search

This Product

    AnalyticDB:Roaring Bitmap

    Document Center

    AnalyticDB:Roaring Bitmap

    Last Updated:Mar 30, 2026

    Roaring bitmaps are compressed bitmaps optimized for set operations such as intersection, union, difference, and deduplication. They use significantly less memory than conventional bitmaps while delivering faster performance, making them well-suited for user profiling, personalized recommendation, and precision marketing workloads.

    Prerequisites

    Before you begin, ensure that you have:

    How it works

    A roaring bitmap encodes each 32-bit integer by splitting it into 16 most significant bits (stored as a chunk key) and 16 least significant bits (stored in a container). Each chunk holds up to 2^16 integers, and chunks are sorted in a dynamically expanding array so that binary search can locate any value quickly.

    Containers are chosen automatically based on data characteristics:

    Container type

    When used

    Capacity

    Array container

    Sparse, non-consecutive values

    Fewer than 4,096 integers

    Bitmap container

    Dense, consecutive integers

    4,096 or more integers

    Run container

    Long runs of consecutive increasing values

    Smaller than both array and bitmap containers

    This adaptive storage layout keeps the compression ratio high and enables AND, OR, and XOR operations directly between container types. For more details on the underlying data structure, see the CRoaring repository.

    Get started

    The following steps walk through a complete workflow: install the extension, create a table, insert data, and run bitmap queries.

    1. Install the extension.

    On the Extensions page of your instance, install the roaringbitmap extension.

    2. Create a table with a `roaringbitmap` column.

    CREATE TABLE t1 (id integer, bitmap roaringbitmap);

    3. Insert roaring bitmap data.

    Use RB_BUILD to build a bitmap from an explicit integer array, or RB_BUILD_AGG to aggregate a series of integers into a bitmap.

    -- Build a bitmap from an explicit array (sets bits at positions 1-9 and 200)
INSERT INTO t1 SELECT 1, RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);

-- Build a bitmap by aggregating a generated series of integers 1-100
INSERT INTO t1 SELECT 2, RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;

    4. Query bit positions.

    RB_ITERATE returns each integer position where a bit is set to 1.

    SELECT RB_ITERATE(bitmap) FROM t1 WHERE id = 1;

    5. Perform bitmap set operations.

    Use function syntax or the equivalent operator syntax—both produce the same result.

    -- OR: union of two bitmaps (function syntax)
SELECT RB_OR(a.bitmap, b.bitmap)
FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,
     (SELECT bitmap FROM t1 WHERE id = 2) AS b;

-- OR: union of two bitmaps (operator syntax)
SELECT a.bitmap | b.bitmap
FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,
     (SELECT bitmap FROM t1 WHERE id = 2) AS b;

    6. Aggregate bitmaps across rows.

    SELECT RB_OR_AGG(bitmap)  FROM t1;   -- Union of all bitmaps
SELECT RB_AND_AGG(bitmap) FROM t1;   -- Intersection of all bitmaps
SELECT RB_XOR_AGG(bitmap) FROM t1;   -- Symmetric difference of all bitmaps

    7. Count set bits (cardinality).

    SELECT RB_CARDINALITY(bitmap) FROM t1;

    Bitmap calculation functions

    Function

    Input

    Output

    Description

    Example

    Result

    rb_build

    integer[]

    roaringbitmap

    Creates a roaring bitmap from an integer array.

    rb_build('{1,2,3,4,5}')

    {1,2,3,4,5}

    rb_and

    roaringbitmap, roaringbitmap

    roaringbitmap

    Performs an AND operation.

    rb_and(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    {3}

    rb_or

    roaringbitmap, roaringbitmap

    roaringbitmap

    Performs an OR operation.

    rb_or(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    {1,2,3,4,5}

    rb_xor

    roaringbitmap, roaringbitmap

    roaringbitmap

    Performs an XOR operation.

    rb_xor(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    {1,2,4,5}

    rb_andnot

    roaringbitmap, roaringbitmap

    roaringbitmap

    Performs an ANDNOT operation.

    rb_andnot(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    {1,2}

    rb_cardinality

    roaringbitmap

    integer

    Returns the number of set bits.

    rb_cardinality(rb_build('{1,2,3,4,5}'))

    5

    rb_and_cardinality

    roaringbitmap, roaringbitmap

    integer

    Returns the cardinality of the AND result.

    rb_and_cardinality(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    1

    rb_or_cardinality

    roaringbitmap, roaringbitmap

    integer

    Returns the cardinality of the OR result.

    rb_or_cardinality(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    5

    rb_xor_cardinality

    roaringbitmap, roaringbitmap

    integer

    Returns the cardinality of the XOR result.

    rb_xor_cardinality(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    4

    rb_andnot_cardinality

    roaringbitmap, roaringbitmap

    integer

    Returns the cardinality of the ANDNOT result.

    rb_andnot_cardinality(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    2

    rb_is_empty

    roaringbitmap

    boolean

    Checks whether a roaring bitmap is empty.

    rb_is_empty(rb_build('{1,2,3,4,5}'))

    false

    rb_equals

    roaringbitmap, roaringbitmap

    boolean

    Checks whether two roaring bitmaps are equal.

    rb_equals(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    false

    rb_intersect

    roaringbitmap, roaringbitmap

    boolean

    Checks whether two roaring bitmaps intersect.

    rb_intersect(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    true

    rb_remove

    roaringbitmap, integer

    roaringbitmap

    Removes a specific offset.

    rb_remove(rb_build('{1,2,3}'), 3)

    {1,2}

    rb_remove

    roaringbitmap, integer, integer

    roaringbitmap

    Removes a specific offset range.

    rb_remove(rb_build('{1,2,3,4,6,7,8}'), 6, 8)

    {1,2,3,4}

    rb_flip

    roaringbitmap, integer

    roaringbitmap

    Flips the bit at a specific offset.

    rb_flip(rb_build('{1,2,3}'), 3)

    {1,2}

    rb_flip

    roaringbitmap, integer, integer

    roaringbitmap

    Flips all bits in a specific offset range.

    rb_flip(rb_build('{1,2,3}'), 2, 3)

    rb_minimum

    roaringbitmap

    integer

    Returns the smallest set offset. Returns -1 if the bitmap is empty.

    rb_minimum(rb_build('{1,2,3}'))

    1

    rb_maximum

    roaringbitmap

    integer

    Returns the largest set offset. Returns 0 if the bitmap is empty.

    rb_maximum(rb_build('{1,2,3}'))

    3

    rb_rank

    roaringbitmap, integer

    integer

    Returns the count of set offsets less than or equal to the specified value.

    rb_rank(rb_build('{1,2,3}'), 3)

    3

    rb_iterate

    roaringbitmap

    setof integer

    Returns each set offset as a row.

    rb_iterate(rb_build('{1,2,3}'))

    1, 2, 3 (one per row)

    rb_iterate_decrement

    roaringbitmap

    integer[]

    Returns all set offsets in descending order as an array.

    rb_iterate_decrement(rb_build('{1,2,3,4}'))

    {4,3,2,1}

    rb_contains

    roaringbitmap, integer

    boolean

    Checks whether the bitmap contains a specific offset.

    rb_contains(rb_build('{1,2,3}'), 1)

    true

    rb_contains

    roaringbitmap, integer, integer

    boolean

    Checks whether the bitmap contains a specific offset range.

    rb_contains(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    rb_contains

    roaringbitmap, roaringbitmap

    boolean

    Checks whether a bitmap contains another bitmap.

    rb_contains(rb_build('{1,2,3}'), rb_build('{3,4,5}'))

    false

    rb_becontained

    integer, roaringbitmap

    boolean

    Checks whether a specific offset is contained by the bitmap.

    rb_becontained(1, rb_build('{1,2,3}'))

    true

    rb_becontained

    roaringbitmap, roaringbitmap

    boolean

    Checks whether a bitmap is contained by another bitmap.

    rb_becontained(rb_build('{1}'), rb_build('{1,2,3}'))

    true

    rb_add

    roaringbitmap, integer

    roaringbitmap

    Adds a specific offset.

    rb_add(rb_build('{1,2,3,4}'), 5)

    {1,2,3,4,5}

    rb_add

    roaringbitmap, integer, integer

    roaringbitmap

    Adds a specific offset range.

    rb_add(rb_build('{1,2,3,4}'), 6, 8)

    {1,2,3,4,6,7,8}

    rb_add_2

    integer, roaringbitmap

    roaringbitmap

    Adds a specific offset to a bitmap (argument order reversed).

    rb_add_2(5, rb_build('{1,2,3,4}'))

    {1,2,3,4,5}

    rb_jaccard_index

    roaringbitmap, roaringbitmap

    float8

    Calculates the Jaccard similarity coefficient between two bitmaps.

    rb_jaccard_index(rb_build('{1,2,3,4}'), rb_build('{1,2}'))

    0.5

    rb_to_array

    roaringbitmap

    integer[]

    Converts a roaring bitmap to an integer array.

    rb_to_array(rb_build('{1,2,3,4}'))

    {1,2,3,4}

    Bitmap aggregate functions

    Function

    Input

    Output

    Description

    Example

    Result

    rb_build_agg

    integer

    roaringbitmap

    Aggregates a set of offsets into a roaring bitmap.

    rb_build_agg(1)

    {1}

    rb_or_agg

    roaringbitmap

    roaringbitmap

    Performs an OR aggregate operation across rows.

    rb_or_agg(rb_build('{1,2,3}'))

    {1,2,3}

    rb_and_agg

    roaringbitmap

    roaringbitmap

    Performs an AND aggregate operation across rows.

    rb_and_agg(rb_build('{1,2,3}'))

    {1,2,3}

    rb_xor_agg

    roaringbitmap

    roaringbitmap

    Performs an XOR aggregate operation across rows.

    rb_xor_agg(rb_build('{1,2,3}'))

    {1,2,3}

    rb_or_cardinality_agg

    roaringbitmap

    integer

    Returns the cardinality of the OR aggregate result.

    rb_or_cardinality_agg(rb_build('{1,2,3}'))

    3

    rb_and_cardinality_agg

    roaringbitmap

    integer

    Returns the cardinality of the AND aggregate result.

    rb_and_cardinality_agg(rb_build('{1,2,3}'))

    3

    rb_xor_cardinality_agg

    roaringbitmap

    integer

    Returns the cardinality of the XOR aggregate result.

    rb_xor_cardinality_agg(rb_build('{1,2,3}'))

    3

    Operators

    All operators have equivalent function forms listed in the previous section. Operator syntax is more concise for inline SQL expressions.

    Operator

    Left

    Right

    Output

    Description

    Example

    &

    roaringbitmap

    roaringbitmap

    roaringbitmap

    AND operation.

    rb_build('{1,2,3}') & rb_build('{1,2,4}')

    |

    roaringbitmap

    roaringbitmap

    roaringbitmap

    OR operation.

    rb_build('{1,2}') | rb_build('{1,3}')

    #

    roaringbitmap

    roaringbitmap

    roaringbitmap

    XOR operation.

    rb_build('{1,2}') # rb_build('{1,3}')

    ~

    roaringbitmap

    roaringbitmap

    roaringbitmap

    ANDNOT operation.

    rb_build('{2,3}') ~ rb_build('{2,4}')

    +

    roaringbitmap

    integer

    roaringbitmap

    Adds a specific offset.

    rb_build('{2,3}') + 1

    -

    roaringbitmap

    integer

    roaringbitmap

    Removes a specific offset.

    rb_build('{1,2,3}') - 1

    =

    roaringbitmap

    roaringbitmap

    boolean

    Checks whether two bitmaps are equal.

    rb_build('{2,3}') = rb_build('{2,3}')

    <>

    roaringbitmap

    roaringbitmap

    boolean

    Checks whether two bitmaps differ.

    rb_build('{2,3}') <> rb_build('{1,2,3}')

    &&

    roaringbitmap

    roaringbitmap

    boolean

    Checks whether two bitmaps intersect.

    rb_build('{2,3}') && rb_build('{3,4}')

    @>

    roaringbitmap

    roaringbitmap

    boolean

    Checks whether the left bitmap contains the right bitmap.

    rb_build('{2,3}') @> rb_build('{2}')

    @>

    roaringbitmap

    integer

    boolean

    Checks whether the bitmap contains a specific offset.

    rb_build('{2,3}') @> 2

    <@

    roaringbitmap

    roaringbitmap

    boolean

    Checks whether the left bitmap is contained by the right bitmap.

    rb_build('{2,3}') <@ rb_build('{1,2,3}')

    <@

    integer

    roaringbitmap

    boolean

    Checks whether the offset is contained by the bitmap.

    2 <@ rb_build('{2,3}')