Using RDS for PostgreSQL varbitx Extension for Real-Time Profile Applications

By Digoal

PostgreSQL built-in varbit and bit operation functions are relatively simple. Similarly, varbitx is an Alibaba Cloud ApsaraDB for RDS PostgreSQL extension for these functions. Varbitx supports more bit operations, and can cover more extensive application scenarios, such as real-time user profile recommendation systems, access control advertisement systems, and ticketing systems.

Introduction to Alibaba Cloud Varbitx

Varbitx provides additional functional interfaces as follows:

1. bit_count

bit_count (  
  varbit,   
  int,   -- (0|1)  
  int,   -- (n)  
  int    -- (N)  
) returns int  
  
  From the nth bit (start bit=0), counts the number of 0|1 bits. If N exceeds the length, only existing bits are counted.    
  For example, bit_count('1111000011110000', 1, 5, 4) returns 1   -- (0001)

2. bit_count

bit_count (  
  varbit,   
  int  
) returns int   
  
  Counts the number of 0|1 bits in the entire bit string.    
  For example, bit_count('1111000011110000', 1) returns 8

3. bit_count_array

bit_count_array (  
  varbit,   
  int,   
  int[]   -- position array, (start bit=0)  
) returns int    
  
  Counts the number of 0|1 bits in the bit string at the specified position.    
  For example, bit_count_array('1111000011110000', 1, array[1,2,7,8]) returns 3   -- (1,1,0,1)

4. bit_fill

bit_fill (  
  int,   -- (0|1)  
  int    -- BIT string length  
) returns varbit   
  
  Fills a specified length with 0 or 1  
  For example, bit_fill(0,10) returns '0000000000'

5. bit_posite

bit_posite (  
  varbit,   
  int,      -- (0|1)  
  boolean   
) returns int[]    
  
  Returns positions of 0|1 (start bit=0) in a positive sequence if true, and in a negative sequence if false      
  For example, bit_posite ('11110010011', 1, true) returns [0,1,2,3,6,9,10]    
       bit_posite ('11110010011', 1, false) returns [10,9,6,3,2,1,0]

6. bit_posite

bit_posite (  
  varbit,  
  int,    -- (0|1)  
  int,    -- N  
  boolean   
) returns int[]    
  
  Returns positions of 0|1 (start bit=0) in a positive sequence if true, and in a negative sequence if false until N bits are returned    
  For example, bit_posite ('11110010011', 1, 3, true) returns [0,1,2]    
       bit_posite ('11110010011', 1, 3, false) returns [10,9,6]

7. get_bit

get_bit (  
  varbit,   
  int,    -- n  
  int     -- N  
) returns varbit  
  
  Obtains N bits (start bit=0) from a specified position and returns varbit  
  For example, get_bit('111110000011', 3, 5) returns 11000

8. get_bit_array

get_bit_array (  
  varbit,   
  int,    -- n  
  int,    -- N  
  int     -- (0|1)  
) returns int[]  
 
  Obtains N bits from a specified position and returns the subscript array of 0|1 (start bit=0)   
  For example, get_bit_array('111110000011', 3, 5, 1)   returns subscript array[3,4] of 11000

9. get_bit_array

get_bit_array (  
varbit,   
int,     -- (0|1)  
int[]    -- position array  
) returns int[]  
  
  Queries for bits at the specified position. Returns positions of bits having values of 0|1 (start bit=0), returns subscripts, and excess is not counted   
  For example, get_bit_array('111110000011', 1, array[1,5,6,7,10,11])   returns array[1,10,11]

10. set_bit_array

set_bit_array (  
  varbit,   
  int,   -- Target BIT (0|1)  
  int,   -- Filled BIT (0|1)  
  int[]  -- Target position  
) returns varbit   
  
  Sets bits at the specified position to 0|1 (start bit=0), and fills digits exceeding the original length with 0|1    
  For example, set_bit_array('111100001111', 0, 1, array[1,15]) returns 1011000011111110

10.1. set_bit_array

set_bit_array ( 
  varbit,  
  int,     -- Target BIT 1 (0|1) 
  int[]    -- Target position 1 
  int,     -- Target BIT 2 (0|1) 
  int[],   -- Target position 2 
  int      -- Filled BIT (0|1) 
) returns varbit  
 
  Sets bits at the specified position to 0|1 (start bit=0), and fills digits exceeding the original length with 0|1   
  For example, set_bit_array('111100001111', 0, array[1,15], 1, array[0,4], 0) returns 1011100011111110

11. set_bit_array

set_bit_array (  
  varbit,   
  int,   -- Target BIT (0|1)   
  int,   -- Filled BIT (0|1)   
  int[], -- Target position   
  int    -- Several bits are set successfully  
) returns varbit   
  
  Sets bits at the specified position to 0|1 (start bit=0), and fills digits exceeding the original length with 0|1. After the length has been filled, returns varbit after N bits have been set    
  For example, set_bit_array('111110001111', 1, 0, array[4,5,6,15], 2) returns 1111111011110000   (Start bit set to 1, and exceeding digits are filled with 0. Returns varbit after successfully setting 2 bits (successfully setting means that the original value 0 has been set to 1 or vice versa; not counted if the original value is already the same as the target value))

12. set_bit_array_record

set_bit_array_record (  
  varbit,   
  int,   -- Target BIT (0|1)  
  int,   -- Filled BIT (0|1)  
  int[]  -- Target position  
) returns (varbit,int[])   
  
  Sets bits at the specified position to 0|1 (start bit=0), and fills digits exceeding the original length with 0|1   
  Once set, returns varbit  
  Also returns position arrays that have been changed to 0|1   
  For example, set_bit_array_record('111100001111', 0, 1, array[1,15]) returns 1011000011111110   (start bit set to 0, and the exceeding digits are filled with 1)  
  Also returns array[1,15]  (digits exceeding the original length are not returned)

13. set_bit_array_record

set_bit_array_record (  
  varbit,   
  int,   -- Target BIT (0|1)   
  int,   -- Filled BIT (0|1)   
  int[], -- Target position   
  int    -- Several bits are set successfully  
) returns (varbit,int[])  
  
  Sets bits at the specified position to 0|1 (start bit=0), and fills digits exceeding the original length with 0|1. Returns varbit after N bits have been set   
  Once set, returns varbit  
  Also returns position arrays that have been changed to 0|1   
  For example, set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2) returns 111111001111   (Start bit set to 1, the exceeding digits are filled with 0. Returns varbit after successfully setting 2 bits (successfully setting means that the original value 0 has been set to 1 or vice versa; not counted if the original value is already the same as the target value))  
  Also returns array[4,5]  (digits exceeding the original length are not returned)

Using Varbitx

Let's look at a quick example of using all of the functions for varbitx.

test=> create extension varbitx;  
CREATE EXTENSION  
  
test=> select bit_count('1111000011110000', 1, 5, 4);  
 bit_count   
-----------  
         1  
(1 row)  
  
test=> select bit_count('1111000011110000', 1);;  
 bit_count   
-----------  
         8  
(1 row)  
  
test=> select bit_count_array('1111000011110000', 1, array[1,2,7,8]);  
 bit_count_array   
-----------------  
               3  
(1 row)  
  
test=> select bit_fill(0,10);  
  bit_fill    
------------  
 0000000000  
(1 row)  
  
test=> select bit_posite ('11110010011', 1, true);  
    bit_posite      
------------------  
 {0,1,2,3,6,9,10}  
(1 row)  
  
test=> select bit_posite ('11110010011', 1, false);  
    bit_posite      
------------------  
 {10,9,6,3,2,1,0}  
(1 row)  
  
test=> select bit_posite ('11110010011', 1, 3, true);  
 bit_posite   
------------  
 {0,1,2}  
(1 row)  
  
test=> select bit_posite ('11110010011', 1, 3, false);  
 bit_posite   
------------  
 {10,9,6}  
(1 row)  
  
test=> select get_bit('111110000011', 3, 5);  
 get_bit   
---------  
 11000  
(1 row)  
  
test=> select get_bit_array('111110000011', 3, 5, 1);  
 get_bit_array   
---------------  
 {3,4}  
(1 row)  
  
test=> select get_bit_array('111110000011', 1, array[1,5,6,7,10,11]);  
 get_bit_array   
---------------  
 {1,10,11}  
(1 row)  
  
test=> select set_bit_array('111100001111', 0, 1, array[1,15]);  
  set_bit_array     
------------------  
 1011000011111110  
(1 row)  
  
test=> select set_bit_array('111110001111', 1, 0, array[4,5,6,15], 2);  
  set_bit_array     
------------------  
 1111111011110000  
(1 row)  
  
test=> select set_bit_array_record('111100001111', 0, 1, array[1,15]);  
    set_bit_array_record       
-----------------------------  
 (1011000011111110,"{1,15}")  
(1 row)  
  
test=> select set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2);  
  set_bit_array_record    
------------------------  
 (111111001111,"{4,5}")  
(1 row)

Dictionary Translation – Obtaining Values in Dictionary from Bit Position

Assume that the dictionary ID is imei+id (id is a seamless auto-incrementing ID). How can we obtain the corresponding imei from the bit position?

create table imei_dict(
  id int primary key,
  imei text
);
select imei from imei_dict where id = any (bit_posite(....));
  
You may also use the cursor to improve the instant response speed.

This SQL is fast. It uses index scanning, and only takes 380 milliseconds to query 1 million records from 100 million.

To learn more about PostgreSQL on Alibaba Cloud, visit https://www.alibabacloud.com/product/apsaradb-for-rds-postgresql

To read more blogs from Digoal or to find related source codes, visit https://github.com/digoal/blog/blob/master/README.md

Community

Using RDS for PostgreSQL varbitx Extension for Real-Time Profile Applications

Introduction to Alibaba Cloud Varbitx

Using Varbitx

Dictionary Translation – Obtaining Values in Dictionary from Bit Position

Read previous post:

Read next post:

digoal

You may also like

Comments

Raja_KT March 14, 2019 at 11:27 am

digoal

Related Products

ApsaraDB RDS for PostgreSQL

PolarDB for PostgreSQL

ApsaraDB RDS for MySQL

ApsaraDB RDS for MariaDB