×
Community Blog Solution for the PostgreSQL Roaringbitmap UID Overflows Beyond Int4 (32 Bytes) – Offset

Solution for the PostgreSQL Roaringbitmap UID Overflows Beyond Int4 (32 Bytes) – Offset

This short article explains formulas for the bitmap extension package of PostgreSQL.

By Digoal

This short article explains formulas for the bitmap extension package of PostgreSQL.

Background

Pg_raringbitmap is a bitmap extension package of PostgreSQL (PG) and is widely used in targeted advertisement marketing and user selection scenarios. There are 2 bits for each value in the bitmap and int4 arrays for input and output. Therefore, there is a problem, and the structure is listed below:

create table t_tag_userids (  
  tagid int,  
  uidbitmaps roaringbitmap  
);  

The uidbitmaps can store four billion User Identifications (UID). What if the UID exceeds four billion? An int8 value can be converted into an offset or two int4 values.

source_uid_int8  
  
x = (2^31)::int8   # int4 最大值 + 1   
  
转换为2个部分如下:  
  
target_uid_int4 = mod(source_uid_int8, x)     
  
offset = source_uid_int8/x    

Revert to int8

source_uid_int8 = offset * x + target_uid_int4  

Example

The conversion of 85899345992381 is listed below:

target_uid_int4 = mod(85899345992381::int8, (2^31)::int8) = 72381  
  
offset = 85899345992381::int8 / (2^31)::int8 = 40000  

The reversion is listed below:

40000 * (2^31)::int8 + 72381 = 85899345992381  
0 0 0
Share on

digoal

282 posts | 24 followers

You may also like

Comments