By Digoal
This short article explains formulas for the bitmap extension package of PostgreSQL.
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
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
digoal - May 9, 2020
digoal - January 18, 2021
Alibaba Clouder - December 11, 2017
digoal - March 20, 2019
digoal - July 4, 2019
digoal - January 14, 2019
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreLeverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by digoal