PostgreSQL 实践 - 内容社区(如论坛)图式搜索应用

标签

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 $$
declare
  res int[];  -- 结果
  v1 int[];   -- 文章被哪些用户阅读了
begin  

  -- 文章被哪些用户阅读了
  select (uids - i2) into v1 from art_uids_view where artid = i1 ;  

  -- 阅读了这篇文章的其他用户,阅读了哪些文章,排除当前用户已阅读的,按重复率排序,返回N条。
  -- 如果阅读了该文章的其他人,还阅读了很多其他文章,排序可能会略耗时。
  return query
  select t1.arts from
  (
    select unnest(arts) arts, count(*) cnt from uid_arts_view where uid = any (v1) group by 1
  ) t1
  left join
  (
    select unnest(arts) arts, 1 cnt from uid_arts_view where uid= i2
  ) t2
  on (t1.arts=t2.arts)
  where t2.* is null
  order by t1.cnt desc
  limit i3;  

end;
$$ language plpgsql strict;

2、与我(阅读文章)爱好相近的人有哪些,走GIN索引,性能很赞。

create extension smlar;  

set smlar.type='overlap';
set smlar.threshold=?;             -- 设置重叠阈值  

select arts into v1 from uid_arts_view where uid = ?;       -- 我阅读了哪些文章    

select
    *,
    smlar( arts, v1, 'N.i' )       -- 其他人与我阅读的文章的重叠数是多少
  from
    uid_arts_view
  where
    arts % v1                      -- where cosine similarity >= smlar.threshold
;

3、与我(阅读文章标签)爱好相近的人有哪些。

与2类似,略。

4、与我(收藏文章)爱好相近的人有哪些。

与2类似,略。

5、与我(收藏文章标签)爱好相近的人有哪些。

与2类似,略。

生成正反向关系的UDF

使用UDF,减少交互次数,完成以下几类业务逻辑的操作。UDF可以使用plpgsql编写,很简单,本文略:

https://www.postgresql.org/docs/10/static/plpgsql.html

1、新建文章的行为,自动产生标签,并更新或追加标签表。

insert into tags values ();  

insert into arts values ();

2、阅读行为,修改正向反向关系。

文章的tags信息从arts里获取  

insert into art_uids_view values ();  

insert into uid_arts_view values ();

3、收藏行为,修改正向反向关系。

文章的tags信息从arts里获取  

insert into art_uids_like values ();  

insert into uid_arts_like values ();

索引

-- smlar 相似查询
create index idx_gin_1 on art_uids_view using gin ( uids _int4_sml_ops );
create index idx_gin_2 on art_uids_like using gin ( uids _int4_sml_ops );    

create index idx_gin_3 on uid_arts_view using gin ( arts _int4_sml_ops );
create index idx_gin_4 on uid_arts_view using gin ( tags _int4_sml_ops );    

create index idx_gin_5 on uid_arts_like using gin ( arts _int4_sml_ops );
create index idx_gin_6 on uid_arts_like using gin ( tags _int4_sml_ops );    

create index idx_gin_7 on art_uids_view using gin ( uids _int4_sml_ops );
create index idx_gin_8 on art_uids_like using gin ( uids _int4_sml_ops );

可选索引

-- 数组相交、包含查询
create index idx_gin_01 on art_uids_view using gin ( uids gin__int_ops );
create index idx_gin_02 on art_uids_like using gin ( uids gin__int_ops );    

create index idx_gin_03 on uid_arts_view using gin ( arts gin__int_ops );
create index idx_gin_04 on uid_arts_view using gin ( tags gin__int_ops );    

create index idx_gin_05 on uid_arts_like using gin ( arts gin__int_ops );
create index idx_gin_06 on uid_arts_like using gin ( tags gin__int_ops );    

create index idx_gin_07 on art_uids_view using gin ( uids gin__int_ops );
create index idx_gin_08 on art_uids_like using gin ( uids gin__int_ops );

填充测试数据

1、生成1000万用户

insert into users select id, md5(id::text), now() from generate_series(1,10000000) t(id);

2、生成10万标签

insert into tags select id, md5(id::text), now() from generate_series(1,100000) t(id);

3、生成5000万文章

create or replace function gen_arr(int,int) returns int[] as $$
  select array(select ceil(random()*$1) from generate_series(1,$2))::int[];
$$ language sql strict;
insert into arts select id, md5(id::text),
  gen_arr(10000000 ,3),
  gen_arr(100000 ,10)
from generate_series(1,50000000) t(id);

4、生成正向关系,平均每篇文章被500人阅读,被50人收藏。

insert into art_uids_view select id, gen_arr(10000000, 500) from generate_series(1,50000000) t(id);  

insert into art_uids_like select id, gen_arr(10000000, 50) from generate_series(1,50000000) t(id);

5、生成反向关系(按理说,反向关系和正向关系应该一一对应,为了测试方便,我这里就不对应了,测试效果是一样的)

平均每人阅读1000篇文章,涉及500个标签。收藏100篇文章,涉及50个标签。

insert into uid_arts_view select id, gen_arr(50000000, 1000), gen_arr(100000, 500) from generate_series(1,10000000) t(id);  

insert into uid_arts_like select id, gen_arr(50000000, 100), gen_arr(100000, 50) from generate_series(1,10000000) t(id);

性能测试

1、阅读了此篇文章的其他人还在阅读什么其他文章,(过滤当前文章、以及我阅读过的文章)。

select rec_arts_view(1,2,10);    -- 文章ID=1, 当前用户ID=2,  返回10条推荐文章给当前用户。

其他人一共阅读了约50万其他文章,获取加排序耗时:200毫秒。

postgres=# select count(*) from rec_arts_view(1,4,1000000);
 count
--------
 497524
(1 row)

Time: 565.524 ms

postgres=# select count(*) from rec_arts_view(1,4,10);
 count
-------
    10
(1 row)

Time: 198.368 ms

2、与我(阅读文章)爱好相近的人有哪些。

set smlar.type='overlap';
set smlar.threshold=10;             -- 设置重叠阈值  

select arts into v1 from uid_arts_view where uid = 1;       -- 我阅读了哪些文章  

select
    *,
    smlar( arts, v1, 'N.i' )       -- 其他人与我阅读的文章的重叠数是多少
  from
    uid_arts_view
  where
    arts % v1                      -- where cosine similarity >= smlar.threshold
;

耗时:2.4毫秒。

 Bitmap Heap Scan on public.uid_arts_view  (cost=933.50..29296.93 rows=10000 width=72) (actual time=1.955..2.351 rows=2 loops=1)
   Output: uid, arts, tags, smlar(arts, '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[], 'N.i'::text)
   Recheck Cond: (uid_arts_view.arts % '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[])
   Heap Blocks: exact=2
   Buffers: shared hit=107
   ->  Bitmap Index Scan on idx_gin_3  (cost=0.00..931.00 rows=10000 width=0) (actual time=1.506..1.506 rows=2 loops=1)
         Index Cond: (uid_arts_view.arts % '{25213311,49886221,45108456,27929256,20760231,35023889,17507871,43947072,48578113,41561690,39557908,26852171,29310889,5029778,24892381,12174141,9191797,41397570,25795440,27806324,28635176}'::integer[])
         Buffers: shared hit=85
 Planning time: 0.110 ms
 Execution time: 2.378 ms
(10 rows)

预计算与性能优化

前面的推荐文章、找相似的人。指的是实时查询的性能,而实际这些操作都可以预计算的(因为文章增量不会太大、而且文章的阅读人群变化不会太大),例如一天刷新一次,那么像用户推荐相似用户,推荐相似文章时,有预计算则直接查询结果,那性能会提升到0.0N毫秒级响应。没有预计算的新文章,则实时查询(并更新到预计算的表中),也能够毫秒级响应。

预计算还可以做成另一种模式,当有人查询这篇文章时,根据上次预计算的时间,决定是否需要重新查询,并更新它。 (也就是说,实时计算 + 缓存 + 缓存超时 的模式。)

逻辑如下

select xxx from pre_view_tbl where xxx=xxx;  -- 查询缓存,return

-- 写入或更新缓存
if not found then
  -- 同步写入
  insert into pre_view_tbl select xxxx returning *; -- 实时计算, 并返回
else if mod_time < (now() - 超时阈值) then
  -- 异步
  delete from pre_view_tbl where xxx=xxx;
  insert into pre_view_tbl select xxxx; -- 实时计算
end if;

小结

3分开发,7分运营。内容网站与社交软件类似,运营是重头戏。运营中关键的一环是圈子,圈子可以聚人气,形成圈子往往靠的是推荐,推荐的源头又是行为,推荐什么样的内容、人给目标,靠的是行为。所谓物以类聚,人以群居,就是这个理。

PostgreSQL 的数组、smlar实现高效的归类查询、推荐需求非常的方便。

1、数组用于存储正向和反向关系,标签等。

2、smlar用于查询相似的数组(找出爱好相似的人)。

在社交运营、内容运营场景中,非常方便、高效。

热点人、热点文章也不在话下,在其他案例中已经测试过,可以参考本文末尾。

参考

https://www.postgresql.org/docs/10/static/plpgsql.html

《电商内容去重\内容筛选应用(实时识别转载\盗图\侵权?) - 文本、图片集、商品集、数组相似判定的优化和索引技术》

https://www.postgresql.org/docs/10/static/intarray.html

计数、实时需求也口可以使用流计算,案例参考:

《三体高可用PCC大赛 - facebook\微博 like场景 - 数据库设计与性能压测》

https://github.com/bitnine-oss/agensgraph

时间: 2024-10-30 18:06:14

PostgreSQL 实践 - 内容社区(如论坛)图式搜索应用的相关文章

哪些内容社区能拓展你的品牌

中介交易 http://www.aliyun.com/zixun/aggregation/6858.html">SEO诊断 淘宝客 云主机 技术大厅 "内容为王"这种奇妙的过度使用的声明,使得很多站长近乎抓狂的地步,包括笔者在内,因为这的确是一个长期的持久战,业内称之为"红军长征二代",路途艰辛啊.但是独特的内容除了有明显的seo好处之外,还有助于客户记住你的品牌,哪些社会渠道将推动内容获得拓展品牌的动力? 内容社区拓展品牌的主要类型 1.视频渠道

浅谈内容社区设计之一: 内容生产

这篇文章主要是对近期面临的产品困惑的梳理,根据已有的浅显经验和观察对内容社区的设计进行了整理.部分观点和多位同事进行了讨论,谢谢大家的指正.谬误之处欢迎拍砖吐槽. 个人的定义:以沉淀用户内容为核心价值的网站.以这个标准来看,微博,人人核心不在内容,他们更倾向关系社区.传统的论坛,贴吧不是以"沉淀"内容为主,我倾向于叫他们资讯社区. 内容社区主要特征:内容源自用户,内容被组织后作为核心服务进一步服务用户.典型的有豆瓣书影音,百度百科/知道,蚂蜂窝,大众点评,下厨房,美丽说/蘑菇街等.下文

爱帮网马海平:内容广告互通是生活搜索的特色

和讯科技消息 9月11日-14日,2012(第十一届)中国互联网大会在北京国际会议中心举行.本届会议以"开放·诚信·融合--迎接移动互联新时代"为主题,并针对互联网.移动互联网.电子商务.网络营销.云计算.物联网.网络安全.创新创业等十多个细分领域进行深入探讨.大会前夕,爱帮网COO马海平受邀接受了和讯科技的采访. 马海平表示,"移动互联网或者生活搜索最典型的特点就是内容就是广告,广告就是内容,用户来到这儿就是搜广告的.一个商户信息,一个促销和优惠信息本身就是广告,广告供给和

为什么要让用户创造内容,小脑袋百度搜索智能优化软件告诉你原因

为什么要让http://www.aliyun.com/zixun/aggregation/8259.html">用户创造内容,小脑袋百度搜索智能优化软件告诉你原因   因为baidu的不断改进,首创文章都成为了自个站长的缺点,自个站长没有这么多本钱能够发明这么多的首创文章.这个现已逐步成为了大网站跟小网站的分水岭.在伪首创逐步失效的今天,怎样发明更多的首创文章是 各位站长最为头疼的事. 但是大多数站长都是着力于怎样挖掘搜索引擎认为的首创文章,而不去研讨怎样让用户为自个的网站发明内容.今天,

用qt做GUI,求交流社区或论坛网站

问题描述 用qt做GUI,求交流社区或论坛网站 我是个新手,刚入行做开发,现在的项目是用qt做的一个车载娱乐系统平台,我们公司负责GUI这层.现在我接手了一个短信模块,入职才一个月-- 感觉好沮丧,女生本来就少,不能让人看不起-不仅bug一个没解决,改页面之后现在第二个页面一进去就黑的,折腾了两三天还没解决,请问有没有GUI相关的社区论坛?或者有没有前辈愿意指导一下新手该如何立足于行业 解决方案 自从有了stackoverflow以后,我觉得没必要去别的论坛提问了.其它论坛的回答质量还不如goo

首届中国互联网社区高峰论坛在杭举行

本报讯(记者 张丽华)11月5日,由杭州日报报业集团.浙江大学人文学院.杭州市网络文化协会共同主办,中国五大互联网社区之一的19楼空间承办的"第一届中国互联网社区高峰论坛"在杭州举行.本次峰会邀请了包括天涯.猫扑.西祠.新浪.篱笆等在内的国内知名社区,以及社区平台与服务提供商康盛创想.艾瑞研究院.浙江大学相关领域的专家学者. 圆桌会谈是本次峰会的亮点,各个知名社区的掌门人就"网络社区和信息传播变革"."网络社区舆论的正面合力"."新媒体

人人视频转型海外短视频内容社区

如今短视频成为不少资本争相布局的领域.其中在4月11日,刚刚完成B轮融资的人人视频首次对外发布品牌战略,并称要通过"翻译+字幕"向海外短视频内容社区转型. 人人视频CEO周为民表示,早在2016年2月,人人视频就已确定做短视频,随后根据公司创立之初在美剧细分市场布局,积累了海外内容观看人群的实际情况,选择了海外的短视频内容.据悉,目前人人视频已购买了包括KSI.SevenSuperGirls.Cameron Dalla等在内的数百位海外热门网红的近10万条视频内容版权,并和美联社达成合

PostgreSQL 空间st_contains,st_within空间包含搜索优化 - 降IO和降CPU(bound box)

标签 PostgreSQL , st_contains , st_within , 空间包含 , 空间bound box , GiST索引 , 空间索引结构 , IO放大 , BOUND BOX放大 背景 点面判断.按面圈选点或其他对象,是GIS几何应用中非常典型的需求. 在PostgreSQL中通过建立GiST索引可以加速这类判断,然而,建立索引就够了吗? 很多时候建立索引是不够的,性能没有到达巅峰,如果要更低的延迟,更少的CPU开销,还有什么优化手段呢? 实际上我以前写过一篇类似的文章,讲的

茂名在线重视社区建设 论坛采用Discuz!改版成功

中介交易 SEO诊断 淘宝客 云主机 技术大厅 最新消息,广东省西南知名地方网站茂名在线长期重视社区建设,近日茂名社区(http://bbs.gdmm.com)成功改版.改版后的茂名在线努力打造粤西南地区最有影响力城市消费社区门户,采用康盛创想(Comsenz)旗下开源社区产品推出了更加贴近城市门户服务的社区元素,通过开放平台引入了贴近地方社区用户的社交应用,深受茂名当地网民的喜爱. 图 01 茂名在线采用Discuz!打造本地化社区 茂名在线是广东西南部一家城市区域门户网站,提供以本地信息应用