PostgreSQL 图式搜索(graph search)实践 - 百亿级图谱，毫秒响应

最新推荐文章于 2025-06-19 09:08:49 发布

转载

最新推荐文章于 2025-06-19 09:08:49 发布 · 1.3k 阅读

2 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/328141

文章标签：

#数据库 #人工智能 #json

本文介绍了在PostgreSQL中利用CTE进行图式搜索的实践，涉及50亿规模的关系网生成、去除循环点、控制深度、展示路径等。通过示例展示了N度搜索、最短路径查询以及如何生成绘图数据，强调了性能优化，如限制返回记录数以提升响应速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

图式搜索是PostgreSQL在（包括流计算、全文检索、图式搜索、K-V存储、图像搜索、指纹搜索、空间数据、时序数据、推荐等）诸多特性中的一个。

采用CTE语法，可以很方便的实现图式搜索（N度搜索、最短路径、点、边属性等）。

其中图式搜索中的：层级深度，是否循环，路径，都是可表述的。

例子

创建1000万用户，每5万作为一个有牵连的群体，平均每个用户牵连500个用户，形成50亿的大规模关系网。

在此基础上，演示如下

1、如何实现N度搜索，边的属性查看，以及最短路径搜索等需求。

2、如何去除循环点，如何控制深度，如何展示路径等。

3、如何生成绘图数据。

创建50亿测试数据

创建1000万用户，每5万作为一个有牵连的群体，平均每个用户牵连500个用户，形成50亿的大规模关系网。

1、建表，表结构如下，可以描述点、边。

create table a(    
  c1 int,                -- 点1    
  c2 int,                -- 点2    
  prop jsonb,            -- 点1,2对应的边的属性，使用JSON存储，包括权重，关系等等。    
  primary key (c1,c2)    -- 主键    
);    
    
create index idx_a_2 on a(c1, COALESCE(((prop ->> 'weight'::text))::float8, 0));

2、生成测试数据：

vi test.sql    
    
\set id random(1,10000000)    
insert into a select :id, ((width_bucket(:id,1,10000000,2000)-1)*50000 + (random()*50000)::int) from generate_series(1,1000) on conflict (c1,c2) do nothing;

pgbench -M prepared -n -r -P 5 -f ./test.sql -c 50 -j 50 -t 100000

3、数据约340GB

如何去除循环点、控制深度、展示路径

1、当路径中重复出现某个点时，说明发生了循环。

2、每递归一次，深度加1。

3、使用数组存储路径。单列数组，或多列（ROW数组），多列路径参考: https://www.postgresql.org/docs/10/static/queries-with.html

SQL如下：

WITH RECURSIVE search_graph(    
  c1,   -- 点1    
  c2,   -- 点2    
  prop, -- 边的属性    
  depth, -- 深度，从1开始    
  path,  -- 路径，数组存储    
  cycle  -- 是否循环    
) AS (    
        SELECT    -- ROOT节点查询    
          g.c1,   -- 点1    
          g.c2,   -- 点2    
          g.prop,   -- 边的属性    
          1,        -- 初始深度=1    
          ARRAY[g.c1],   -- 初始路径    
          false          -- 是否循环(初始为否)    
        FROM a AS g     
        WHERE     
          c1 = ?         -- ROOT节点=?    
      UNION ALL    
        SELECT     -- 递归子句    
          g.c1,    -- 点1    
          g.c2,    -- 点2    
          g.prop,          -- 边的属性    
          sg.depth + 1,    -- 深度+1    
          path || g.c1,    -- 路径中加入新的点    
          g.c1 = ANY(path)    -- 是否循环，判断新点是否已经在之前的路径中    
        FROM a AS g, search_graph AS sg   -- 循环 INNER JOIN    
        WHERE     
          g.c1 = sg.c2         -- 递归JOIN条件    
          AND NOT cycle        -- 防止循环    
          AND sg.depth <= ?    -- 搜索深度=?       
)    
SELECT * FROM search_graph;    -- 查询递归表，可以加LIMIT输出，也可以使用游标