AI 助理
备案控制台
开发者社区 数据库 关系型数据库 文章 正文

惊天性能！单实例RDS PostgreSQL 支撑 2000亿 实时标签透视案例

2017-12-23 3782
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 标签 PostgreSQL , varbitx , 标签 , 任意圈选 , 阿里云 , RDS , bitmap , 分段 , 并行计算 , 异步调用 , dblink , 异步dblink , 游标 背景 20亿用户，每个用户1000个标签，基于任意标签组合圈选、透视（业务上的需求是一次最多计算100个标签的组合）。

标签

PostgreSQL , varbitx , 标签 , 任意圈选 , 阿里云 , RDS , bitmap , 分段 , 并行计算 , 异步调用 , dblink , 异步dblink , 游标

背景

20亿用户，每个用户1000个标签，基于任意标签组合圈选、透视（业务上的需求是一次最多计算100个标签的组合）。

相当于要处理2000亿记录。

1、实时求标签组合的记录数。（即满足标签组合的用户有多少）

2、用户ID。（级满足标签组合的用户ID。）

要求实时响应。

通常你肯定会想，这个至少需要上百台机器来支撑。

但是我要给你一个惊喜，这个数据量，一台RDS PG实例即可。怎么做呢？听我道来，用最少的资源解决业务问题，用到RDS PG黑科技。

RDS PG 解决方案

方案如下：

《阿里云RDS PostgreSQL varbitx实践 - 流式标签 (阅后即焚流式批量计算) - 万亿级，任意标签圈人，毫秒响应》

优化方案，提高响应速度

1、bitmap切段

2、计算满足条件的USER COUNT值时，并行计算(使用dblink异步调用)

3、求用户ID时，使用游标，流式返回。

DEMO

1、需要用到的插件

create extension dblink;  
create extension varbitx;

2、创建标签表，切段，例如20亿个用户，切成400段，每一段5000万个用户BIT。

postgres=# create table t_bitmap (  
  tagid int,   -- 标签ID  
  ofid int,    -- 偏移值, 乘以5000万  
  v varbit     -- userid 比特  
);  
CREATE TABLE

3、创建索引(约束)

create unique index idx_t_bitmap_1 on t_bitmap (tagid, ofid);

4、创建1000个标签的BITMAP数据，每一个标签400条，每条的BIT长度为5000万位。

postgres=# do language plpgsql $$  
declare v varbit := repeat('1',5000000)::varbit;  
begin  
  for i in 1..100 loop  
    for x in 0..399 loop  
      insert into t_bitmap values (i, x, v);                            
    end loop;  
  end loop;  
end;  
$$;  
  
  
DO  
Time: 150468.359 ms (02:30.468)

5、创建生成dblink连接的函数，重复创建不报错。

create or replace function conn(  
  name,   -- dblink名字  
  text    -- 连接串,URL  
) returns void as $$    
declare    
begin    
  perform dblink_connect($1, $2);   
  return;    
exception when others then    
  return;    
end;    
$$ language plpgsql strict;

6、AND标签组合的并行计算函数(dblink 异步并行)，返回USERID透视数。

create or replace function get_bitcount_and(  
  and_tagids int[],   -- 输入标签ID数组  
  v_bit int,          -- 求1或0的比特个数  
  conn text,          -- 连接串
  OUT cnt int8        -- 返回值, 多少个1或0  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   -- 生成400个链接，因为每行5000万，20亿个BIT，刚好400条。并LOOP  
  perform conn('link'||i,  conn);   -- 连接  
  perform dblink_get_result('link'||i);                        -- 消耗掉上一次异步连接的结果，否则会报错。  
    
  -- 发送异步DBLINK调用  
  -- 每次操作一个bit分段，返回BIT为0或1的位数  
  perform dblink_send_query('link'||i, format('select bit_count(bit_and(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s', v_bit, and_tagids, i));    
end loop;  
  
for i in 0..399 loop  
  -- 返回异步调用结果，包括所有分段  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;

7、OR标签组合的并行计算函数(dblink 异步并行)，返回USERID透视数。

create or replace function get_bitcount_or(  
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 连接串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn('link'||i,  conn);   
  perform dblink_get_result('link'||i);  
  perform dblink_send_query('link'||i, format('select bit_count(bit_or(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s', v_bit, or_tagids, i));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;

8、AND，OR 标签组合的并行计算函数(dblink 异步并行)，返回USERID透视数。

create or replace function get_bitcount_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 连接串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn('link'||i,  conn);   
  perform dblink_get_result('link'||i);  
  perform dblink_send_query('link'||i, format('  
    with t1 as (select bit_and(v) b from t_bitmap where tagid = any (%L) and ofid=%s),   
         t2 as (select bit_or(v) b from t_bitmap where tagid = any (%L) and ofid=%s)   
    select bit_count(bitor(t1.b, t2.b), %s) from t1,t2',   
    and_tagids, i, or_tagids, i, v_bit));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;  

-- 更复杂的QUERY，可以自行修改函数。实际业务中这种需求较少。  
-- (a and b andc or d) or (a and c) or (d and not b)..........

9、计数透视的性能如下，50个标签组合，仅1.5秒，100个标签组合，仅2.6秒：

我们统计2000亿个user_tags组合（每个用户一条记录，每条记录1000个标签时的换算），仅仅需要2.6秒。

一个标签：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,1) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 791.392 ms  
  
10个标签组合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 847.427 ms  
  
50个标签组合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1478.847 ms (00:01.479)  
  
100个标签组合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2574.761 ms (00:02.575)

10、AND 、 OR组合性能如下，性能一样：

postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,1) t(id)) t;  
 sum   
-----  
      
(1 row)  
  
Time: 854.934 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 889.472 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1519.031 ms (00:01.519)  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2597.701 ms (00:02.598)

11、求USERID，AND 函数如下，我们为了达到高速响应，使用游标返回。

create or replace function get_pos_and(  
  and_tagids int[],    -- 标签组合  
  v_bit int            -- 求1或0的BIT位，返回游标，游标包含ofid与位置下标（当然了，这个翻译动作也可以交给程序，那么返回BIT和ofid即可）  
) returns setof refcursor as $$  
declare  
  ref refcursor[];    -- 返回游标数组  
  res refcursor;      -- 返回游标  
  sql text;           -- 游标对应的SQL，即取USERID位置的SQL  
begin  
  for x in 1..400 loop   -- 生成400个游标名  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    -- 使用0到399的偏移值， 乘以5000万系数。  
      
    -- 赋予游标名  
    res := ref[i+1];   
    -- 生成游标对应的动态SQL(ofid, bit位置)，注意bit位置可以不翻译，交给程序翻译也没问题。程序翻译的话，翻译好之后，再使用in查询字典  
    -- select uid from uid_mapping where pos in (pos_array);  
    -- 1亿，in 100万, 380毫秒  
    -- [《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》](201711/20171107_26.md)    
    sql := format('select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, and_tagids, i);   
    -- 打开游标  
    open res for execute sql ;  
    -- 返回游标  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

12、求USERID，OR 函数如下，我们为了达到高速响应，使用游标返回。

create or replace function get_pos_or(  
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format('select %s, bit_posite(bit_or(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, or_tagids, i);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

13、求USERID，AND OR 函数如下，我们为了达到高速响应，使用游标返回。

create or replace function get_pos_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format('with t1 as  
                   (select bit_and(v) v from t_bitmap where tagid = any (%L) and ofid=%s),  
                   t2 as  
                   (select bit_or(v) v from t_bitmap where tagid = any (%L) and ofid=%s)  
                   select %s, bit_posite(bitor(t1.v, t2.v), %s, true) from t1,t2',   
                  and_tagids, i, or_tagids, i, i, v_bit);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

14、求USERID例子，88毫秒响应，极端速度。

postgres=# begin;  
BEGIN  
Time: 0.031 ms  
postgres=# select * from get_pos_and_or(array[1,2,3], array[4,5,6], 1);  
 get_pos_and_or   
----------------  
 cur1  
 cur2  
 cur3  
 cur4  
 cur5  
 cur6  
 cur7  
 ....  
 cur399  
 cur400  
(400 rows)  
  
Time: 88.069 ms

获取游标值，5000万ID，仅692毫秒：

fetch 1 from cur1;  
Time: 692.408 ms

15、如果我们把位置翻译放到客户端做，那么只需要获取结果BITMAP，那就更快了，224毫秒就可以获取5000万BIT走。 这块也能做成并发，每个客户端获取不同的ofid。

CREATE OR REPLACE FUNCTION public.get_pos_and(and_tagids integer[])
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
 STRICT
AS $function$
declare
  ref refcursor[];  
  res refcursor; 
  sql text;
begin
  for x in 1..400 loop
    ref[x] := 'cur'||x;
  end loop;

  for i in 0..399 loop 
    res := ref[i+1]; 
    -- sql := format('select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, and_tagids, i);
    sql := format('select %s, bit_and(v) from t_bitmap where tagid = any (%L) and ofid=%s', i, and_tagids, i);
    open res for execute sql ;
    return next res;
  end loop;
end;
$function$;


postgres=# \timing
Timing is on.
postgres=# begin;
BEGIN
Time: 0.045 ms
postgres=# select get_pos_and(array_agg(id)) from generate_series(1,100) t(id);
 get_pos_and 
-------------
 cur1
 cur2
 cur3
...
 cur397
 cur398
 cur399
 cur400
(400 rows)


fetch 1 from cur1;
Time: 224.776 ms

小结

varbitx是阿里云RDS PG提供的一个插件，使用它，单个RDS PG就可以实现万亿级别USER_TAGS的实时圈选。

使用BITMAP分段、DBLINK异步查询、游标等技术，提高性能。

性能指标：

1、求COUNT，2000亿（20亿用户，100个标签组合）USER_IDS，响应速度2.6秒。

2、求USERID明细，返回5000万用户ID位置，仅692毫秒。

3、求USERID明细，如果只返回BITMAP，5000万个BIT仅需224毫秒。

参考

《阿里云RDS PostgreSQL varbitx实践 - 流式标签 (阅后即焚流式批量计算) - 万亿级，任意标签圈人，毫秒响应》

《阿里云RDS for PostgreSQL varbitx插件与实时画像应用场景介绍》

《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统(varbitx)》

文章标签：
云数据库 RDS MySQL 版
云原生数据库 PolarDB
关系型数据库
SQL
RDS
PostgreSQL
并行计算
关键词：
云数据库 RDS MySQL 版性能
云数据库 RDS MySQL 版案例
云数据库 RDS postgresql
rds PostgreSQL
云数据库 RDS MySQL 版postgresql
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
德哥
目录
相关文章
顾翔
|
3月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
165 3
游客vphb4ae2je2zi
|
3月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB，多种引擎优惠上线！MySQL倚天版88元/年，SQL Server 2核4G仅299元/年，PostgreSQL 227元/年起。高可用、可弹性伸缩，安全稳定。详情见官网活动页。
游客vphb4ae2je2zi
803 152
青云交（Java大数据AI云原生Python）
|
7月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计：揭秘MySQL集群架构负载均衡核心算法：从理论到Java代码实战，让你的数据库性能飙升！
本文聚焦 MySQL 集群架构中的负载均衡算法，阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法，分析各自优缺点及适用场景。并提供 Java 语言代码实现示例，助力直观理解。文章结构清晰，语言通俗易懂，对理解和应用负载均衡算法具有实用价值和参考价值。
青云交（Java大数据AI云原生Python）
440 14
大数据大厂之MySQL数据库课程设计：揭秘MySQL集群架构负载均衡核心算法：从理论到Java代码实战，让你的数据库性能飙升！
上云就上阿狸云
|
3月前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型，包括关系型与NoSQL，主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起，支持按需付费与优惠套餐，适用于各类应用场景。
上云就上阿狸云
858 4
顾翔
|
3月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（上）
最终建议：当前系统是完美的读密集型负载模型，优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存，预期可降低30%的CPU负载，同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
顾翔
247 6
顾翔
|
3月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（中）
使用MYSQL Report分析数据库性能
顾翔
170 1
程序员爱钓鱼
|
6月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程：使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口，支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容，涵盖实际开发中常用的技巧与注意事项，适合快速掌握Go语言数据库编程基础。
程序员爱钓鱼
506 62
上云就上阿狸云
|
3月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎，提供高性价比、稳定安全的云数据库服务，适用于多种行业与业务场景。
上云就上阿狸云
320 0
蓝易云
|
4月前
|
缓存 关系型数据库 MySQL
MySQL数据库性能调优：实用技术与策略
通过秉持以上的策略实施具体的优化措施，可以确保MySQL数据库的高效稳定运行。务必结合具体情况，动态调整优化策略，才能充分发挥数据库的性能潜力。
蓝易云
216 0
阿里云瑶池数据库_
|
6月前
|
关系型数据库 MySQL 分布式数据库
Super MySQL｜揭秘PolarDB全异步执行架构，高并发场景性能利器
阿里云瑶池旗下的云原生数据库PolarDB MySQL版设计了基于协程的全异步执行架构，实现鉴权、事务提交、锁等待等核心逻辑的异步化执行，这是业界首个真正意义上实现全异步执行架构的MySQL数据库产品，显著提升了PolarDB MySQL的高并发处理能力，其中通用写入性能提升超过70%，长尾延迟降低60%以上。
阿里云瑶池数据库_
531 17
数据库

关系型数据库

热门文章

最新文章

  • 1
    德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
  • 2
    什么场景应该用 MongoDB ？
  • 3
    PostgreSQL 如何实现批量更新、删除、插入
  • 4
    PostgreSQL upsert功能(insert on conflict do)的用法
  • 5
    Linux 性能诊断 perf使用指南
  • 6
    PostgreSQL 9种索引的原理和应用场景
  • 7
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
  • 8
    Jedis常见异常汇总
  • 9
    PostgreSQL 9.5+ 高效分区表实现 - pg_pathman
  • 10
    PostgreSQL 百亿数据 秒级响应 正则及模糊查询
  • 1
    CAXA 电子图板 2025 下载安装指南：小白也能轻松搞定
    68
  • 2
    【赵渝强老师】国产金仓数据库的体系架构
    133
  • 3
    阿里巴巴双肩包带回家！参与「RDS AI助手」体验反馈赢好礼
    116
  • 4
    RDS AI助手深度测评-场景4：CPU压测指引
    91
  • 5
    【Java架构师体系课 | MySQL篇】⑦ 深入理解MySQL事务隔离级别与锁机制
    146
  • 6
    TRUNCATE、DELETE、DROP 的区别？
    304
  • 7
    为什么MySQL会选错索引，如何解决？
    138
  • 8
    【Java架构师体系课 | MySQL篇】⑤ 索引优化实战一
    115
  • 9
    什么是脏读、幻读、不可重复读？Mysql的隔离级别是什么？
    337
  • 10
    【Java架构师体系课 | MySQL篇】② 深入理解MySQL索引底层数据结构与算法
    149

    • 相关产品

  • 云数据库 RDS
    文档详情 产品详情
  • 云数据库 RDS PostgreSQL 版
    文档详情 产品详情
  • 云数据库 RDS MySQL 版
    文档详情 产品详情

    • 相关课程

    更多
  • Java Spring Boot 2.6.0开发实战-1024程序员节创造营公益课
  • MySQL实操课程
  • MySQL高级应用 - 索引和锁
  • Linux MySQL服务器搭建与应用
  • MySQL完全自学手册
  • MySQL数据库入门学习

    • 相关电子书

    更多
  • TcpRT：阿里云RDS智能诊断系统云上大规模部署自动化服务的客户实践经验
  • TcpRT：面向大规模海量云数据库的服务质量实时采集与诊断系
  • 袋鼠云基于阿里云RDS的数据库架构实践

    • 推荐镜像

    更多
  • mysql
  • postgresql
    • 下一篇
    安全设备篇——WAF