Skip to main content

Bitmap Type

Relyt supports the roaringbitmap data type, which is an efficient, compressed bitmap structure designed for storing and retrieving large sets of non-negative integers. It is particularly useful for use cases such as user profiling and targeted marketing.

info

Currently, the roaringbitmap type stores data using the integer data type.


Usage examples​

This section provides examples of creating tables, inserting data, and performing queries with the roaringbitmap data type.

Create a table​

The following example creates a table called roaringbitmap_test, where the bitmap column is of the roaringbitmap data type.

CREATE TABLE roaringbitmap_test (
  id integer,
  bitmap roaringbitmap
);

Insert data​

Once the table is created, you can insert data using several methods to generate roaringbitmap data.

  • Call the RB_BUILD function to directly convert an integer array to a roaringbitmap:

    INSERT INTO roaringbitmap_test VALUES(1, RB_BUILD(ARRAY [1,2,3]));
    INSERT INTO roaringbitmap_test VALUES(2, RB_BUILD('{-1,2,555555,-4}'::int[]));

  • Call the RB_FROM_STRING or ROARINGBITMAP function to convert strings in the following formats into a roaringbitmap:

    INSERT INTO roaringbitmap_test VALUES(3, RB_FROM_STRING('{1,2}'));
    INSERT INTO roaringbitmap_test VALUES(4, RB_FROM_STRING('\x3a3000000100000000000000100000000100'));
    INSERT INTO roaringbitmap_test VALUES(5, ROARINGBITMAP('{1,2,4}'));
    INSERT INTO roaringbitmap_test VALUES(6, ROARINGBITMAP('\x3a3000000100000000000000100000000100'));

  • Call the RB_FROM_BYTEA function to convert a bytea value into a roaringbitmap:

    INSERT INTO roaringbitmap_test VALUES(7, RB_FROM_BYTEA ('\x3a3000000100000000000000100000000100'::bytea));

  • Call the RB_BUILD_AGG function to aggregate all values of an integer column into a single roaringbitmap value.

Query data​

  • Call RB_TO_ARRAY to convert a roaring bitmap to an integer array:

    SELECT id, rb_to_array(bitmap) FROM roaringbitmap_test ORDER BY id;
    id |   rb_to_array    
    ----+------------------
      1 | {1,2,3}
      2 | {2,555555,-4,-1}
      3 | {1,2}
      4 | {1}
      5 | {1,2,4}
      6 | {1}
      7 | {1}
    (7 rows)

  • Call RB_TO_STRING to convert a roaring bitmap to a string:

    SELECT id, rb_to_string(bitmap) FROM roaringbitmap_test ORDER BY id;
    id |   rb_to_string   
    ----+------------------
      1 | {1,2,3}
      2 | {2,555555,-4,-1}
      3 | {1,2}
      4 | {1}
      5 | {1,2,4}
      6 | {1}
      7 | {1}
    (7 rows)

  • Call RB_TO_BYTEA to convert a roaring bitmap to a bytea value:

    SELECT id, rb_to_bytea(bitmap) FROM roaringbitmap_test ORDER BY id;
      id |                                    rb_to_bytea                                     
    ----+------------------------------------------------------------------------------------
      1 | \x3a300000010000000000020010000000010002000300
      2 | \x3a300000030000000000000008000000ffff01002000000022000000240000000200237afcffffff
      3 | \x3a30000001000000000001001000000001000200
      4 | \x3a3000000100000000000000100000000100
      5 | \x3a300000010000000000020010000000010002000400
      6 | \x3a3000000100000000000000100000000100
      7 | \x3a3000000100000000000000100000000100
    (7 rows)