roaringbitmap
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_roaringbitmap | 1.2.0 | TYPE | Apache-2.0 | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3630 | roaringbitmap | No | Yes | No | Yes | No | Yes | - |
| Related | rum prefix semver unit pgpdf pglite_fusion md5hash asn1oid |
|---|---|
| Depended By | pgfaceting |
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.2.0 | 1817161514 | pg_roaringbitmap | - |
| RPM | PGDG | 1.2.0 | 1817161514 | pg_roaringbitmap_$v | - |
| DEB | PGDG | 1.2.0 | 1817161514 | postgresql-$v-roaringbitmap | - |
Build
You can build the RPM packages for pg_roaringbitmap using pig build:
pig build pkg pg_roaringbitmap # build RPM packages
Install
You can install pg_roaringbitmap directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_roaringbitmap; # Install for current active PG version
pig ext install -y pg_roaringbitmap -v 18 # PG 18
pig ext install -y pg_roaringbitmap -v 17 # PG 17
pig ext install -y pg_roaringbitmap -v 16 # PG 16
pig ext install -y pg_roaringbitmap -v 15 # PG 15
pig ext install -y pg_roaringbitmap -v 14 # PG 14
dnf install -y pg_roaringbitmap_18 # PG 18
dnf install -y pg_roaringbitmap_17 # PG 17
dnf install -y pg_roaringbitmap_16 # PG 16
dnf install -y pg_roaringbitmap_15 # PG 15
dnf install -y pg_roaringbitmap_14 # PG 14
apt install -y postgresql-18-roaringbitmap # PG 18
apt install -y postgresql-17-roaringbitmap # PG 17
apt install -y postgresql-16-roaringbitmap # PG 16
apt install -y postgresql-15-roaringbitmap # PG 15
apt install -y postgresql-14-roaringbitmap # PG 14
Create Extension:
CREATE EXTENSION roaringbitmap;
Usage
Sources:
pg_roaringbitmap installs the PostgreSQL extension roaringbitmap, which provides compressed bitmap types and set-operation functions backed by Roaring Bitmaps. Use it for compact integer-set storage, fast unions/intersections, cohort filters, faceting, and bitmap aggregation.
v1.2.0 adds rb_runoptimize() / rb64_runoptimize() to shrink bitmap binary size, preserves the legacy rb_exsit spelling for backward compatibility, adds PostgreSQL 19 compatibility, and validates untrusted bitmap input in receive functions.
Create the Extension
CREATE EXTENSION IF NOT EXISTS roaringbitmap;
SET roaringbitmap.output_format = 'array';
SELECT rb_build(ARRAY[1, 2, 3, 4, 5]);
roaringbitmap.output_format can be bytea or array. The default output format is bytea, which is better for large bitmaps; array is easier to read interactively.
Data Types
roaringbitmapstores unsigned 32-bit integer sets over the logical range[0, 4294967296).roaringbitmap64stores unsigned 64-bit integer sets and uses therb64_function family.
CREATE TABLE cohorts (
segment text PRIMARY KEY,
users32 roaringbitmap,
users64 roaringbitmap64
);
Build and Convert
INSERT INTO cohorts(segment, users32)
VALUES ('trial', rb_build(ARRAY[1, 2, 3, 100, 200]));
INSERT INTO cohorts(segment, users32)
SELECT 'active', rb_build_agg(id)
FROM generate_series(1, 100000) AS id;
SELECT rb_cardinality(users32) FROM cohorts WHERE segment = 'active';
SELECT rb_to_array(users32) FROM cohorts WHERE segment = 'trial';
SELECT rb_iterate(users32) FROM cohorts WHERE segment = 'trial';
For 64-bit values, use rb64_build(), rb64_build_agg(), rb64_to_array(), and rb64_iterate().
Set Operations
SELECT rb_build(ARRAY[1,2,3]) | rb_build(ARRAY[3,4,5]); -- union
SELECT rb_build(ARRAY[1,2,3]) & rb_build(ARRAY[3,4,5]); -- intersection
SELECT rb_build(ARRAY[1,2,3]) # rb_build(ARRAY[3,4,5]); -- xor
SELECT rb_build(ARRAY[1,2,3]) - rb_build(ARRAY[3,4,5]); -- difference
SELECT rb_build(ARRAY[1,2,3]) | 9; -- add element
SELECT rb_build(ARRAY[1,2,3]) - 2; -- remove element
Containment and overlap operators are available:
SELECT rb_build(ARRAY[1,2,3]) @> rb_build(ARRAY[2,3]);
SELECT rb_build(ARRAY[2,3]) <@ rb_build(ARRAY[1,2,3]);
SELECT rb_build(ARRAY[1,2,3]) && rb_build(ARRAY[3,4,5]);
Cardinality and Range Helpers
SELECT rb_and_cardinality(a.users32, b.users32);
SELECT rb_or_cardinality(a.users32, b.users32);
SELECT rb_xor_cardinality(a.users32, b.users32);
SELECT rb_andnot_cardinality(a.users32, b.users32);
SELECT rb_range(users32, 100, 1000);
SELECT rb_range_cardinality(users32, 100, 1000);
SELECT rb_fill(users32, 100, 200);
SELECT rb_clear(users32, 100, 200);
SELECT rb_flip(users32, 100, 200);
SELECT rb_min(users32), rb_max(users32), rb_rank(users32, 500), rb_index(users32, 500);
SELECT rb_jaccard_dist(a.users32, b.users32);
The 64-bit range helpers use the rb64_ prefix. Since v1.1.0, range_end = 0 means unlimited for several rb64_ range/select functions.
Aggregate Functions
SELECT rb_build_agg(user_id) FROM events;
SELECT rb_or_agg(users32) FROM cohorts;
SELECT rb_and_agg(users32) FROM cohorts;
SELECT rb_xor_agg(users32) FROM cohorts;
SELECT rb64_build_agg(user_id::bigint) FROM events;
SELECT rb64_or_agg(users64) FROM cohorts;
Optimize Serialized Size
UPDATE cohorts
SET users32 = rb_runoptimize(users32);
UPDATE cohorts
SET users64 = rb64_runoptimize(users64);
rb_runoptimize() and rb64_runoptimize() can reduce serialized bitmap size for suitable data distributions. Measure before using them in hot write paths.
Caveats
- Pigsty uses extension file name
roaringbitmap.md; the upstream package name ispg_roaringbitmap. - Source builds require PostgreSQL headers and CRoaring dependencies. The README notes regression testing before release covers PostgreSQL 13 and above.
Makefile_nativecan compile with SIMD instructions and may be faster on matching CPUs, but binaries built that way can crash withSIGILLon machines without the required CPU features.- Use
byteaoutput for large bitmaps andarrayoutput for human inspection.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.