标签

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、查询单个会员时，由于数据冗余的问题，涉及大量离散扫描。

背景知识参考

设计优化

使用数组类型，将多条合并为一条

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