Functions and usage of the varbitx extension - PolarDB

varbitx is a PolarDB for PostgreSQL extension that adds advanced bit-string operation functions beyond what the standard PostgreSQL varbit extension provides. Use it for high-performance bit manipulation in scenarios such as real-time user profile tagging, advertising access control, and ticketing systems.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL cluster running PostgreSQL 11

Install the extension

CREATE EXTENSION varbitx;

To remove the extension:

DROP EXTENSION varbitx;

Functions

All functions accept and return standard PostgreSQL varbit values. Bit positions use zero-based indexing (the leftmost bit is position 0).

Bit extraction

Function	Description	Example
`get_bit(bits varbit, start int, count int) returns varbit`	Returns `count` bits starting at position `start`.	`get_bit('111110000011', 3, 5)` → `11000`
`get_bit_array(bits varbit, start int, count int, value int) returns int[]`	Gets `count` bits starting at `start`, then returns the positions of bits equal to `value` (0 or 1).	`get_bit_array('111110000011', 3, 5, 1)` → `[3,4]`
`get_bit_array(bits varbit, value int, positions int[]) returns int[]`	From the bits at the specified `positions`, returns the positions of bits equal to `value` (0 or 1). Bits not listed in `positions` are ignored.	`get_bit_array('111110000011', 1, array[1,5,6,7,10,11])` → `[1,10,11]`

Bit modification

Function	Description	Example
`set_bit_array(bits varbit, value int, fill int, positions int[]) returns varbit`	Sets bits at the given `positions` to `value` (0 or 1). Extends the bit string as needed, filling new bits with `fill` (0 or 1).	`set_bit_array('111100001111', 0, 1, array[1,15])` → `1011000011111110`
`set_bit_array(bits varbit, value int, fill int, positions int[], limit int) returns varbit`	Same as above, but stops after processing `limit` positions.	`set_bit_array('111100001111', 1, 0, array[4,5,6,7], 2)` → `111111001111`
`set_bit_array_record(bits varbit, value int, fill int, positions int[]) returns (varbit, int[])`	Sets bits at the given `positions` to `value` (0 or 1), extends with `fill` as needed, and returns both the updated bit string and the positions that were changed.	`set_bit_array_record('111100001111', 0, 1, array[1,15])` → `1011000011111110`, `[1,15]`
`set_bit_array_record(bits varbit, value int, fill int, positions int[], limit int) returns (varbit, int[])`	Same as above, but stops after changing `limit` bits. Returns the updated bit string and the positions that were actually changed.	`set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2)` → `111111001111`, `[4,5]`

Bit generation

Function	Description	Example
`bit_fill(value int, count int) returns varbit`	Returns a bit string of `count` bits all set to `value` (0 or 1).	`bit_fill(0, 10)` → `0000000000`
`bit_rand(count int, value int, ratio float) returns varbit`	Returns a bit string of `count` bits where approximately `ratio` of the bits are set to `value` (0 or 1), randomly distributed.	`bit_rand(10, 1, 0.3)` may return `0101000001`

Bit counting

Function	Description	Example
`bit_count(bits varbit, value int) returns int`	Counts all bits equal to `value` (0 or 1).	`bit_count('1111000011110000', 1)` → `8`
`bit_count(bits varbit, value int, start int, count int) returns int`	Counts bits equal to `value` within `count` bits starting at `start`. Bits beyond the bit string length are not counted.	`bit_count('1111000011110000', 1, 5, 4)` → `1`
`bit_count_array(bits varbit, value int, positions int[]) returns int`	Counts the bits at the specified `positions` that are equal to `value` (0 or 1).	`bit_count_array('1111000011110000', 1, array[1,2,7,8])` → `3`

Position lookup

Function	Description	Example
`bit_posite(bits varbit, value int, ascending boolean) returns int[]`	Returns the positions of all bits equal to `value` (0 or 1). Pass `true` for ascending order, `false` for descending.	`bit_posite('11110010011', 1, true)` → `[0,1,2,3,6,9,10]`; `bit_posite('11110010011', 1, false)` → `[10,9,6,3,2,1,0]`
`bit_posite(bits varbit, value int, limit int, ascending boolean) returns int[]`	Returns up to `limit` positions of bits equal to `value` (0 or 1). Pass `true` for ascending order, `false` for descending.	`bit_posite('11110010011', 1, 3, true)` → `[0,1,2]`; `bit_posite('11110010011', 1, 3, false)` → `[10,9,6]`

Usage examples

The examples below show a typical workflow for a user tagging system: create a table with a varbit column, insert data, and query it using varbitx functions.

Create a table with a varbit column

CREATE TABLE user_tags (
    user_id   integer,
    tag_bits  varbit
);

Insert varbit data

INSERT INTO user_tags VALUES (1, '1111000011110000');
INSERT INTO user_tags VALUES (2, '1011000011111110');
INSERT INTO user_tags VALUES (3, '0000111100001111');

Count active tags per user

Count the bits set to 1 (active tags) for each user:

SELECT user_id, bit_count(tag_bits, 1) AS active_tag_count
FROM user_tags;

Expected output:

 user_id | active_tag_count
---------+------------------
       1 |                8
       2 |               10
       3 |                8
(3 rows)

Filter users by tag position

Find users who have the tag at position 0 set:

SELECT user_id
FROM user_tags
WHERE get_bit(tag_bits, 0, 1) = B'1';

Enable specific tags and record changes

Use set_bit_array_record to enable tags at specific positions and return which positions changed:

SELECT user_id,
       set_bit_array_record(tag_bits, 1, 0, array[1,4,5,6,7], 2)
FROM user_tags
WHERE user_id = 1;

Expected output:

 user_id | set_bit_array_record
---------+----------------------
       1 | (1111110011110000,"{4,5}")
(1 row)