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

标签

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的特性，可以给业务上带来非常大的惊喜，比如成本压缩，性能提升。