20180801_01.md

PostgreSQL 设计优化case - 多对多 转 一对多(数组)

作者

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 - 公益是一辈子的事.

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、生态合作、写心得拿奖品

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥