PostgreSQL 实践.docVIP

PostgreSQL 实践 - 内容社区(如论坛)图式搜索应用 作者 digoal 日期 2017-10-09 标签 PostgreSQL , 图数据库 , 论坛搜索 , 推荐 , 数组 , smlar相似搜索 背景 通常一个内容社区网站可能需要记录这么一些数据: 文章，用户，标签。 还有三者之间的关系，包括，文章的标签，用户阅读了文章，用户收藏了文章，用户关注了某用户，用户是某篇文章的作者。 最终要实现毫无人道的查询，例如： 阅读了此篇文章的人还在阅读什么其他文章，和我爱好相近的人都有哪些等等等等。 其中文章数量几千万，用户数量接近一千万。 如何实现这样的需求呢？ 实际上PostgreSQL里面的数组、smlar实现这个需求非常的方便。下面开始设计和压测。 数组用于存储正向和反向关系，标签等。 smlar用于查询相似的数组（找出爱好相似的人）。 设计 元数据 1、用户表 create table users( uid int primary key, -- 用户ID info text, -- 附加信息 crt_time timestamp -- 时间 ); 2、标签表 create table tags( tagid int primary key, -- 标签ID info text, -- 附加信息 crt_time timestamp -- 时间 ); 3、文章表 create table arts( artid int primary key, -- 文章ID info text, -- 附加信息、内容 uids int[], -- 用户IDs（作者可能是多人，所以使用数组） tags int[] -- 标签 ); 关系数据 1、正向关系 1.1、文章被谁看过 create table art_uids_view ( artid int primary key, uids int[] ); 1.2、文章被谁收藏过 create table art_uids_like ( artid int primary key, uids int[] ); 2、反向关系 2.1、用户看过哪些文章，包含哪些标签 create table uid_arts_view ( uid int primary key, arts int[], tags int[] ); 2.2、用户收藏了哪些文章，包含哪些标签 create table uid_arts_like ( uid int primary key, arts int[], tags int[] ); 查询 1、阅读了此篇文章的其他人还在阅读什么其他文章，(过滤当前文章、以及我阅读过的文章)。 逻辑如下，写成UDF即可： create extension intarray ; -- 创建intarry插件，简化数组的加减 select (uids - $current_uid) into v1 from art_uids_view where artid = $current_artid ; -- 取出阅读了当前文章的所有用户(减去当前用户) select (unnest(arts) as arts, count(*) as cnt) into v2 from uid_arts_view where uid = any (v1) group by 1 ; -- 获取阅读了同样文章的人，还阅读了哪些文章 select arts into v3 from uid_arts_view where uid= $current_uid ; -- 当前用户阅读了哪些文章 result = v2.arts - v3 ; -- 其他人阅读的所有文章 减去 当前用户阅读的文章，得到其他人阅读的文章。 按重叠数从大到小排序，推荐给用户 UDF如下，都能使用索引，都是聚合后的点查，性能很赞： create or replace function rec_arts_view( i1 int, -- 文章ID i2 int, -- 当前用户ID i3 int -- limit ) returns setof int as $$ dec