阿里云RDS for PostgreSQL varbitx插件与实时画像应用场景介绍
背景
PostgreSQL 内置的varbit, bit类型的操作函数比较简单，阿里云RDS for PostgreSQL对其进行了扩展。
支持更多的bit操作，可以覆盖更广的应用场景，例如实时用户画像推荐系统、门禁广告系统、购票系统等。
阿里云 varbitx 插件介绍
增加的函数接口如下
1. bit_count
bit_count (
varbit,
int, -- (0|1)
int, -- (n)
int -- (N)
) returns int
从第n位开始(起始位=0)，统计N个BIT位中有多少个0|1，如果N超出长度，则只计算已经存在的。
例如 bit_count('1111000011110000', 1, 5, 4) 返回 1 -- (0001)
2. bit_count
bit_count (
varbit,
int
) returns int
统计整个bit string中0|1的个数。
例如 bit_count('1111000011110000', 1) 返回 8
3. bit_count_array
bit_count_array (
varbit,
int,
int[] -- 位置数组, (起始位=0)
) returns int
统计指定位置bit string中0|1的个数。
例如 bit_count_array('1111000011110000', 1, array[1,2,7,8]) 返回 3 -- (1,1,0,1)
4. bit_fill
bit_fill (
int, -- (0|1)
int -- BIT string 长度
) returns varbit
填充指定长度的0 或 1
例如 bit_fill(0,10) 返回 '0000000000'
5. bit_posite
bit_posite (
varbit,
int, -- (0|1)
boolean
) returns int[]
返回 0|1 的位置，(起始位=0), true时正向返回，false时反向返回
例如 bit_posite ('11110010011', 1, true) 返回 [0,1,2,3,6,9,10]
bit_posite ('11110010011', 1, false) 返回 [10,9,6,3,2,1,0]
6. bit_posite
bit_posite (
varbit,
int, -- (0|1)
int, -- N
boolean
) returns int[]
返回 0|1 的位置，(起始位=0)，true时正向返回，false时反向返回，返回N个为止
例如 bit_posite ('11110010011', 1, 3, true) 返回 [0,1,2]
bit_posite ('11110010011', 1, 3, false) 返回 [10,9,6]
7. get_bit
get_bit (
varbit,
int, -- n
int -- N
) returns varbit
从指定位置n开始获取N个BIT位，(起始位=0)，返回varbit
例如 get_bit('111110000011', 3, 5) 返回11000
8. get_bit_array
get_bit_array (
varbit,
int, -- n
int, -- N
int -- (0|1)
) returns int[]
从指定位置n开始获取N个BIT位，返回0|1的位置下标，(起始位=0)
例如 get_bit_array('111110000011', 3, 5, 1) 返回11000的下标 array[3,4]
9. get_bit_array
get_bit_array (
varbit,
int, -- (0|1)
int[] -- 位置数组
) returns int[]
查询指定位置的BIT，返回其中是0|1的BIT的位置(起始位=0) ，返回下标，超出不统计
例如 get_bit_array('111110000011', 1, array[1,5,6,7,10,11]) 返回array[1,10,11]
10. set_bit_array
set_bit_array (
varbit,
int, -- 目标BIT (0|1)
int, -- 填充BIT (0|1)
int[] -- 目标位置
) returns varbit
将指定位置的BIT设置为0|1，(起始位=0)，超出原始长度的部分填充0|1
例如 set_bit_array('111100001111', 0, 1, array[1,15]) 返回 1011000011111110
10.1. set_bit_array
set_bit_array (
varbit,
int, -- 1目标BIT (0|1)
int[] -- 1目标位置
int, -- 2目标BIT (0|1)
int[], -- 2目标位置
int -- 填充BIT (0|1)
) returns varbit
将指定位置的BIT设置为0|1，(起始位=0)，超出原始长度的部分填充0|1
例如 set_bit_array('111100001111', 0, array[1,15], 1, array[0,4], 0) 返回 1011100011111110
11. set_bit_array
set_bit_array (
varbit,
int, -- 目标BIT (0|1)
int, -- 填充BIT (0|1)
int[], -- 目标位置
int -- 成功设置若干位
) returns varbit
将指定位置的BIT设置为0|1，(起始位=0)，超出原始长度的部分填充0|1 , 首先填充，设置N位即返回
例如 set_bit_array('111110001111', 1, 0, array[4,5,6,15], 2) 返回 1111111011110000 ( 设置为1, 超出补0, 成功设置满2位即返回 (成功设置指将原来的0设置为1或反之,如果原来已经是目标值则不算数) )
12. set_bit_array_record
set_bit_array_record (
varbit,
int, -- 目标BIT (0|1)
int, -- 填充BIT (0|1)
int[] -- 目标位置
) returns (varbit,int[])
将指定位置的BIT设置为0|1，(起始位=0)，超出原始长度的部分填充0|1
返回设置后的varbit
同时返回(原来不是0|1)此次被设置为0|1的位置数组
例如 set_bit_array_record('111100001111', 0, 1, array[1,15]) 返回 1011000011111110 (设置为0, 超出补1)
同时返回array[1,15] (超出原始长度的不返回)
13. set_bit_array_record
set_bit_array_record (
varbit,
int, -- 目标BIT (0|1)
int, -- 填充BIT (0|1)
int[], -- 目标位置
int -- 成功设置若干位
) returns (varbit,int[])
将指定位置的BIT设置为0|1，(起始位=0)，超出原始长度的部分填充0|1 , 设置N位即返回
返回设置后的varbit
同时返回(原来不是0|1)此次被设置为0|1的位置数组
例如 set_bit_array_record('111100001111', 1, 0, array[1,4,5,6,7], 2) 返回 111111001111 (设置为1, 超出补0, 设置满2位即返回 (成功设置指将原来的0设置为1或反之,如果原来已经是目标值则不算数) )
同时返回array[4,5] (超出原始长度的不返回)
使用 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)
字典翻译 - 从bit位置得到字典中的VALUE.
假设字典为imei+id(id为无缝自增ID)， 如何从bit位置得到对应的imei呢.
create table imei_dict(
id int primary key,
imei text
);
select imei from imei_dict where id = any (bit_posite(....));
你也可以用游标慢慢返回，提高瞬间响应速度。
这个SQL很快，走索引扫描，1亿记录中in 100万，只需要380毫秒。
《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》
至于字典怎么生成？可以参考这里：
《PostgreSQL 无缝自增ID的实现 - by advisory lock》
案例
使用varbitx+阅后即焚, 实现高效增量
《HTAP数据库 PostgreSQL 场景与性能测试之 27 - (OLTP) 物联网 - FEED日志, 流式处理 与 阅后即焚 (CTE)》