PostgreSQL 亿级实时人群圈选、随机文章推荐 - roaringbitmap

pg_roaringbitmap是PostgreSQL 12开始支持的bitmap扩展包，可以高效生成、压缩、解析位图数据，支持最常见的位图聚合操作（与、或、非、异或），适用于亿级以上用户、千万级标签的大数据量下精准营销、快速圈选用户的需求。

案例1-根据标签筛选用户

用户和标签为多对多关系，一个用户可以打多个标签，一个标签也可以归属多个用户。

需要支持以下查询：

• 查询标签是否包含某个用户
• 同时包含多个指定标签的用户群体（取交）
• 多个标签中包含任意一个标签的用户群体（取并）

创建相关数据表

标签-用户关联表

-- 标签-用户关联表
create table t_tag_users (
  id serial PRIMARY KEY,
  tagid int,   -- 标签id
  uid_offset int,          -- 由于userid是int8类型，roaringbitmap内部仅支持int4，需要通过offset转换处理。   
  userbits roaringbitmap,     -- 用户ID聚合的bitmap  
  mod_time timestamp       -- 时间 
);
CREATE INDEX idx_tagid ON t_tag_users (tagid);

用户-标签关联表（原始）

存储了用户关联的所有标签，以数组方式存储（原始数据），需要将其转换成bitmap存储，后续业务查询不需要用到该表

create table t_user_tags (
  id serial PRIMARY KEY, 
  uid  int8, -- COMMENT '用户id',
  tags int[] -- COMMENT '标签数组'
);

--  插入数据
INSERT INTO t_user_tags (uid, tags) VALUES 
(1, '{1,2,3,4,5,6,7}'),
(4394967296, '{1,2}'),
(2, '{1}'),
(3, '{1,2}');


-- t_user_tags原始数据
id|uid       |tags           |
--+----------+---------------+
 1|         1|{1,2,3,4,5,6,7}|
 2|4394967296|{1,2}          |
 3|         2|{1}            |
 4|         3|{1,2}          |

将原始表数组组装为roaringbitmap所需数据的方法

-- 聚合标签-用户数据
select tagid, uid_offset::int4yu, rb_build_agg(uid::int) as userbits from 
(
select 
  unnest(tags) as tagid, 
  (uid / (2^31)::int8) as uid_offset, 
  mod(uid, (2^31)::int8) as uid 
from t_user_tags 
) t 
group by tagid, uid_offset; 

插入数据到t_tag_users表（后续查询统一使用该表）

-- 从原始表组装为bitmap数据插入到t_tag_users表中
insert into t_tag_users(tagid,uid_offset,userbits)
select tagid, uid_offset::int4, rb_build_agg(uid::int) as userbits from 
(
select 
  unnest(tags) as tagid, 
  (uid / (2^31)::int8) as uid_offset, 
  mod(uid, (2^31)::int8) as uid 
from t_user_tags 
) t 
group by tagid, uid_offset; 

-- 插入后 t_tag_users现有数据
tagid|uid_offset|userbits   |
-----+----------+-----------+
    1|         0|{1,2,3}    |
    1|         2|{100000000}|
    2|         0|{1,3}      |
    2|         2|{100000000}|
    3|         0|{1}        |
    4|         0|{1}        |
    5|         0|{1}        |
    6|         0|{1}        |
    7|         0|{1}        |

查询包含标签1和3的人群：

-- 人群总数
select sum(ub) from 
(
select uid_offset,rb_and_cardinality_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,3) 
group by uid_offset having count(id) > 1
) t;

-- 提取符合条件的user_id
select uid_offset,rb_and_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,3) 
group by uid_offset having count(id) > 1;


---上面增加having count(id) > 1的原因是：
语句根据uid_offset聚合了数据，当相同uid_offset只有一行数据时，rb_and_agg会直接返回那一行的数据默认当做聚合成功。

举个例子，上面提取人群id不带having时，结果返回如下：
uid_offset|ub         |
----------+-----------+
         2|{100000000}|
         0|{1}        |
         
实际上100000000这个只包含了标签1，并没有包含标签3

查询包含标签1或2或3的人群

-- 人群总数
select sum(ub) from 
(
select uid_offset,rb_or_cardinality_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,3,10,200) 
group by uid_offset
) t;

-- 提取符合条件的user_id
select uid_offset,rb_or_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,2,3) 
group by uid_offset;

-- 提取人群id，并且返回集合中是否包含uid=3的用户(这里只考虑了小于int8的用户id)
select tagid,uid_offset,rb_or_agg(userbits)  as ub,rb_or_agg(userbits) @> 3 as is_contain
from t_tag_users 
where tagid in (1,2,3) and uid_offset = 0 
group by tagid,uid_offset;

tagid|uid_offset|ub     |is_contain|
-----+----------+-------+----------+
    1|         0|{1,2,3}|true      |
    2|         0|{1,3}  |true      |
    3|         0|{1}    |false     |
    

案例2-随机推荐文章

以社区为例，每天会一批首页推荐精选文章，用户访问时根据精选文章随机推荐20条（过滤已经阅读过的）

假设精选文章有2000篇

创建相关数据表

-- 文章表
create table tbl_art (  
  artid int8,  -- 文章ID  
  content text,  -- 文章内容  
  crt_time timestamp  -- 文章创建时间  
);  

-- 推荐文章列表
create table tbl_art_list (  
  list_time timestamp primary key,  -- 列表生成时间  
  artid int8[] not null,   -- 包含哪些文章（ID），使用数组存储  
  min_crt_time timestamp not null,  -- 时间最老的文章时间。
  arrlen int not null   -- artid 的长度（包含几篇精选文章）  
);  

-- 插入记录
insert into tbl_art_list values (  
  now(),   
  array(select id from generate_series(1,2000) as id),   
  now(),   
  2000  
);  

-- 用户原始阅读记录 
create table tbl_read_rec (  
  uid int8,  -- 会员ID  
  crt_time timestamp,  -- 阅读时间  
  artid int8,  -- 文章ID  
  primary key(uid,artid)  -- 主键   
);  
  
create index idx_crt_time_1 on tbl_read_rec (crt_time); 

-- 用户阅读记录 roaringbitmap方案
create table user_article_record (
  id serial PRIMARY KEY,
  uid int,   -- 用户id
  articlebits roaringbitmap,     -- roaringbitmap存储阅读过的文章
  mod_time timestamp       -- 最后一次修改时间 
);

-- 插入阅读记录
INSERT INTO user_article_record(uid,articlebits,mod_time) SELECT 1,rb_build_agg(e),NOW() FROM generate_series(1,1000) e

随机获取推荐文章id函数

-- 使用roaringbitmap
create or replace function get_artid(  
  i_uid int8,   -- 用户ID  
  rows int  -- 随机获取数量
) returns int8[] as $$  
declare   
  v_artid int8[];  -- 精选ID列表  
  len int;  -- 精选ID列表文章个数  
  res int8[] :=    -- 结果集
  tmp int8;  -- 中间变量，从精选ID列表中得到的随机文章ID  
  loopi int := 0;  -- 循环变量，已获取到多少篇符合条件的ID  
  loopx int := 0;  -- 循环上限  
  bitmap roaringbitmap; -- bitmap string数据
  artid_bitmap roaringbitmap; -- 随机到的文章id转换为roaringbitmap格式
begin   
  select artid,arrlen into v_artid,len   
    from tbl_art_list order by list_time desc limit 1;  -- 从精选列表，获取最后一条。
  select articlebits into bitmap from user_article_record where uid=i_uid limit 1; --用户已经阅读过的记录，记录bitmap变量，每次函数调用只查询一次，后续从bitmap变量进行匹配
  loop   
    if loopi >= rows or loopx >= 1.5*len then    -- 如果已经遍历完所有文章，或超出循环上限直接返回
      return res;    
    end if;  
    tmp := v_artid[floor(random()*len)+1];   -- 从精选文章IDs 获取随机ID  
    SELECT  roaringbitmap('{}') | tmp::int4 INTO artid_bitmap;
    IF not(bitmap @> artid_bitmap) THEN  -- 如果文章id不在bitmap中，表示未读
      res := array_append(res, tmp);  -- 未读的文章
      loopi := loopi +1 ;  
    end if;  
    loopx := loopx +1 ;  
  end loop;   
  return res;  
end;  
$$ language plpgsql strict; 

调用函数，获取随机文章id

select get_artid(1,20);  

get_artid                                                                                            |
-----------------------------------------------------------------------------------------------------+
{1297,1675,1606,1341,1571,1956,1021,1973,1575,1875,1407,1382,1485,1458,1902,1428,1660,1040,1376,1827}|

id溢出(超出32字节)的处理办法

roaringbitmap使用int4(32位)存储，超出int4，可以利用offset，支持到int8(64位)

表结构如下

create table t_tag_users (
  id serial PRIMARY KEY,
  tagid int,   -- 用户id
  uid_offset int,          -- 下面的offset值
  userbits roaringbitmap,     -- 用户ID聚合的bitmap  
);

将int8的值拆分为offset、int4两个值

uid_int8 

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

还原为int8

uid_int8 = offset * x + uid_int4  

例子

74280097891234 转换:

uid_int4 = mod(74280097891234::int8,(2^31)::int8) = 785990562
offset = 74280097891234::int8 / (2^31)::int8 = 34589

还原

34589 * (2^31)::int8 + 785990562 = 74280097891234

系统id小于2^31时，一个tag只占用一行记录，随着id的增加，行数也会增加，最大行数(offset)= 2^63 / 2^31

总结

相比常规的数组或多行存储方案，roaringbitmap方案优势非常明显，是一个低成本、高效率的解决方案，需要注意roaringbitmap只支持int类型且使用32位存储，超出32位需要业务进行特殊处理。

roaringbitmap相比普通的bitmap进行了改进，解决了bitmap一些问题，例如单个大型整数，就需要申请等量长度空间，造成空闲bit位过多，有兴趣的同学可以了解下roaringbitmap的底层存储原理。

参考

• pgxn.org/dist/pg_roa…
• roaringbitmap.org/about/
• github.com/digoal/blog…