作者
digoal
日期
2018-08-01
标签
PostgreSQL , 数组 , 多对多 , 一对多 , udf , JOIN
背景
某个系统存储了会员的标签，以及标签的描述信息。业务上需要通过会员ID得到会员的标签，再得到描述信息。
每个会员有若干标签，原来是这么存储的
1、会员标签表，人数5亿左右，每个人平均有几百个标签，1500亿行左右。
create table a(uid int8, tag int);
2、标签描述表。
几百上千条
create table b(tag int primary key, info text);
查询如下
SQL
select a.*,b.* from a join b using(tag) where a.uid=?;
问题
1、会员标签表，存在大量冗余数据，人数5亿，放大到了1500亿记录。
2、查询单个会员时，由于数据冗余的问题，涉及大量离散扫描。
背景知识参考
《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》
设计优化
使用数组类型，将多条合并为一条
```
create table a (uid int8 primary key, tag int[]);
create table b (tag int primary key, info text);
```
使用UDF获取标签描述
create or replace function get_desc(int[]) returns text[] as $$
select array_agg(info) from b where tag = any ($1); -- 可以走索引
$$ language sql strict ;
查询如下
SQL
select uid,get_desc(tag) from a where uid=?;
优势
1、数据下降到5亿条，消除了多个UID的离散扫描。同时节约了空间。
2、消除JOIN，性能好。预计能到几十万QPS。
小结
利用好PG的特性，可以给业务上带来非常大的惊喜，比如成本压缩，性能提升。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
