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

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 | 25 followers
Follow

PolarDB for PostgreSQL

Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.

Learn More