清理向量数据库的“重复相似”内容
文中参考文档可点击阅读原文打开, 推荐《最好的PostgreSQL学习镜像》。
清理向量数据库的“重复相似”内容
背景
向量数据库是大模型的神仙伴侣, 通常被用于RAG业务, 存储文本、图片、视频以及对应的特征向量. 在应用时通常根据向量进行相似检索, 返回原始的文本、图片、视频等给大模型进行辅助推理.
还有一种应用是相似去重: 找出微创、抄袭、开放用户上传内容功能后堆积的相似内容, 并进行清理.
如何去除向量数据库的重复相似内容
方法步骤: 1、找出相似内容, 2、定义保留规则, 3、删除相似内容.
1、生成测试数据
创建向量插件
create extension vector;
创建测试表
create table tbl (
id int primary key,
v vector(32) not null,
ts timestamp not null,
-- 原始内容字段, 略.
filesize float8 not null
);
创建生成随机向量的函数
create or replace function gen_vec(int) returns vector as $$
select array_to_vector(array(select (random()*100)::real from generate_series(1,$1) t), $1, true);
$$ language sql strict;
插入1万条测试数据.
insert into tbl select generate_series(1,10000), gen_vec(32), clock_timestamp(), random()*100;
2、创建存储重复记录ID的表
create table tbl_duplicate (
id int
);
3、生成笛卡尔积, 找出相似重复ID.
为了加速执行, 可以按ID进行分段, 并行处理.
定义保留规则:
保留filesize最大/最小的? 保留ts最新/最老的?
本例假设保留最新的, 删除旧的.
先关闭单一sql并行, 为什么不用单一SQL并行功能? 见文末.
set max_parallel_workers_per_gather=0;
使用ID进行分段, 如果没有连续ID, 可以使用ctid(行号)进行分段. 例如总共1000个数据块, 按块进行分片 0-100, 101-200, ...
insert into tbl_duplicate
select case when ts1 > ts2 then id2 else id1 end -- 本例假设保留最新的, 删除旧的.
from (
select a.id id1, b.id id2, a.ts ts1, b.ts ts2, a.filesize filesize1, b.filesize filesize2,
-- 距离算子自己定义这里测试使用<=>, 阈值自己定义这里测试用0.1, 0 表示重复, 1表示不重复
(case when a.v <=> b.v < 0.1 then 0 else 1 end) as ds
from
(select * from tbl where id >= 1 and id < 1001) a, -- 分段并行执行, 这个SQL分段为1-1000
tbl b
where a.id <> b.id
) t
where ds=0
;
其他分片自行修改以上SQL.
如果不分片的话, 1万条的笛卡尔积大概耗时6秒.
insert into tbl_duplicate
select case when ts1 > ts2 then id2 else id1 end -- 本例假设保留最新的, 删除旧的.
from (
select a.id id1, b.id id2, a.ts ts1, b.ts ts2, a.filesize filesize1, b.filesize filesize2,
-- 距离算子自己定义这里测试使用<=>, 阈值自己定义这里测试用0.1, 0 表示重复, 1表示不重复
(case when a.v <=> b.v < 0.1 then 0 else 1 end) as ds
from tbl a, tbl b
where a.id <> b.id
) t
where ds=0
;
INSERT 0 55996
Time: 6024.504 ms (00:06.025)
4、删除相似重复ID.
delete from tbl where exists (select 1 from tbl_duplicate t where tbl.id=t.id);
DELETE 5549
Time: 57.987 ms
为什么不用PG自带的并行功能?
因为gather前并行gather后不并行没有意义, 需要的是gather后计算并行, 所以注释内的不可取.
以下是说明
/*
set max_parallel_workers=32;
set max_parallel_workers_per_gather=6;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set parallel_leader_participation =false;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
alter table tbl set (parallel_workers =6);
explain
select id1,id2,ds from (
select a.id id1, b.id id2,
(case when a.v <=> b.v < 0.1 then 0 else 1 end) as ds -- 距离算子自己定义这里测试使用<=>, 阈值自己定义这里测试用0.1, 0 表示重复, 1表示不重复
from
tbl a,
tbl b
where a.id <> b.id
) t
where ds=0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..2252984.08 rows=499950 width=12)
Join Filter: ((a.id <> b.id) AND (CASE WHEN ((a.v <=> b.v) < '0.1'::double precision) THEN 0 ELSE 1 END = 0))
-> Gather (cost=0.00..229.67 rows=10000 width=140)
Workers Planned: 6
-> Parallel Seq Scan on tbl a (cost=0.00..229.67 rows=1667 width=140)
-> Materialize (cost=0.00..279.67 rows=10000 width=140)
-> Gather (cost=0.00..229.67 rows=10000 width=140)
Workers Planned: 6
-> Parallel Seq Scan on tbl b (cost=0.00..229.67 rows=1667 width=140)
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
(12 rows)
*/
为什么不使用向量索引
1、建向量索引很耗时,
2、向量索引用牺牲精度换取查询效率, 要完美的去重复可以暴力计算,
3、分段并行暴力计算目的就是充分利用机器的cpu资源
参考
以下是数据库端异步并行操作相关实践, 实际上在程序中实现会更加简单.
202309/20230910_03.md 《PostgreSQL pg_later async SQL, 异步SQL调用, (非dblink实现)》
202002/20200215_01.md 《PostgreSQL 分析型SQL优化case一例 - 分析业务逻辑,分区,dblink异步并行》
201809/20180904_03.md 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)》
201809/20180903_01.md 《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》
201806/20180621_03.md 《在PostgreSQL中跑后台长任务的方法 - 使用dblink异步接口》
201804/20180427_03.md 《PostgreSQL 批量导入性能 (采用dblink 异步调用)》
201804/20180427_01.md 《阿里云RDS PostgreSQL OSS 外部表实践 - (dblink异步调用封装并行) 从OSS并行导入数据》
201804/20180410_03.md 《PostgreSQL 变态并行拉取单表的方法 - 按块并行(按行号(ctid)并行) + dblink 异步调用》
201802/20180210_01.md 《PostgreSQL VOPS 向量计算 + DBLINK异步并行 - 单实例 10亿 聚合计算跑进2秒》
201802/20180205_03.md 《PostgreSQL 相似搜索分布式架构设计与实践 - dblink异步调用与多机并行(远程 游标+记录 UDF实例)》
201802/20180201_02.md 《PostgreSQL dblink异步调用实现 并行hash分片JOIN - 含数据交、并、差 提速案例 - 含dblink VS pg 11 parallel hash join VS pg 11 智能分区JOIN》
201712/20171223_01.md 《惊天性能！单RDS PostgreSQL实例 支撑 2000亿 - 实时标签透视案例 (含dblink异步并行调用)》
201709/20170906_01.md 《阿里云RDS PostgreSQL OSS 外部表实践 - (dblink异步调用封装并行) 数据并行导出到OSS》
本期彩蛋 - 开源Clup: PostgreSQL&PolarDB高可用与日常管理软件
clup由《PostgreSQL从小工到专家》作者唐成乘数科技出品, 包含开源版本和企业版本, 是非常成熟的PostgreSQL&PolarDB集群管理软件.
官网: https://www.csudata.com/clup
开源项目地址: https://gitee.com/csudata
使用CLup可以轻松管理几十套至上百套PostgreSQL、PolarDB高可用的数据库集群，发生故障自动切换，不影响生产系统的运行。故障切换后有详细的故障日志，方便定位故障原因，还可以手工一键切换。CLup还提供了数据库的一些基本监控和TOP SQL的监控，CLup后续版本还会增加更多的功能。
管理基于PostgreSQL流复制的集群
管理基于共享存储的PolarDB集群
产品优势
最后推荐2本大佬的新书
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 及视频号: