一 前言

大模型的 RAG 架构，不论是入门还是某个环节的深入，已经有不少文章都做了介绍。我在大模型 RAG：文档分块方案与 RAG 全流程中也做了阐述。本篇在 大模型 RAG：基于 PgSql 的向量检索的基础上，介绍基于 postgresql 的向量检索和全文检索基础环境搭建及检索示例，为后续的多路检索召回、重排序做好准备。

本篇基于 mac 操作系统和 PostgreSQL13.2.1 版本，安装 pgvector、pg_trgm 扩展，并演示向量化检索和全文检索实现过程。

二 pgvector-向量存储与相似性检索

2.1 pgvector 简介

pgvector 是一个开源的 PostgreSQL 扩展，为 PostgreSQL 添加了对向量相似性搜索的支持，使得在 PostgreSQL 中存储和查询向量数据变得可能，尤其是对于那些需要执行高效近似最近邻（Approximate Nearest Neighbor, ANN）搜索的应用场景特别有用。

用户可以用 pgvector 在 PostgreSQL 数据库中定义向量类型，并使用专门的索引来加速相似度查询。该扩展支持多种距离函数来计算向量之间的相似性或距离，比如欧氏距离（L2）、内积（Inner Product）和余弦相似度（Cosine Similarity）等。

2.2 向量化相关概念

所谓"向量"，其实就是一个 float/double 数组，例如[0.5, 0.6, 0.7,...]。在 RAG 或 NLP 领域中，代表着一段/文本的“特征”，通过对文本向量化（embedding），把每段文本转化成一个向量。用户的问题提过来时也会被转化成向量，并且用这个向量到 PostgreSQL 向量库中通过向量的相似度计算找到距离最近的向量，从而也就找到了语义最接近的几个文本，接下来让大模型参考这些文本生成回答，这就是最简化的 RAG 过程。

文本向量化，就是将文本数据转换为数值向量的过程，这是自然语言处理（NLP）中非常重要的一步。有许多方法可以实现文本的向量化，包括但不限于词袋模型（Bag of Words, BoW）、TF-IDF（Term Frequency-Inverse Document Frequency）、Word2Vec、GloVe（Global Vectors for Word Representation）以及 BERT（Bidirectional Encoder Representations from Transformers）等。除了上述模型，在实际使用中也可以选择用一些平台提供的公有云 embedding 接口，例如通义千问的 text-embedding-v1/v2/v3，能够简化自己反复训练调优模型的过程，准确率也相对更高一些（毕竟大平台的训练语料数量要远大于自行训练能承担的语料库）。

2.3 pgvector 安装

PostgreSQL16 以上的版本已经附带了 pgvector 扩展，不必从 git 上下载源码安装，只要在 sql 中创建即可。但由于本人的 mac 比较老旧，基于 16 版本安装扩展时一些依赖无法满足，所以降级到 13.2.1 版本。不过扩展安装过程和问题排查解决思路是一致的，有问题也可以评论区留言共同探讨。

2.3.1 pgvector 源码下载编译

pgvector 官方 git 地址：https://github.com/pgvector/pgvector。git 中也给出了安装的简要介绍（但问题排查和解决内容很少）。Linux 或 mac 下：

cd /tmp  #进入要下载源码和编译的目录git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git  #clone代码，这里切换到v0.8.0版本分支cd pgvectormakemake install # 权限不足可能需要sudo

windows 下需要先确认 C++ support in Visual Studio 已经完成安装：

call "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat"

然后设置 pgsql 路径并下载源码编译：

set "PGROOT=C:\Program Files\PostgreSQL\16"cd %TEMP%git clone --branch v0.8.0 https://github.com/pgvector/pgvector.gitcd pgvectornmake /F Makefile.winnmake /F Makefile.win install

在我 2017 版的小破 mac 上执行 make 时，抛出了如下报错信息：

“Undefined symbols for architecture x86_64”表示不支持的 x86_64 架构指令，但实际上通过 uname -a 能够看到是 x86 架构（RELEASE_X86_64 x86_64），所以推测是指令集版本问题。最好的办法是升级 xcode，但由于系统过于老旧，所以这条路不通。只能选择降级 pgvector 版本，降到了 0.7.0 版本后，再次 make install 后安装成功。

2.3.2 数据库内创建（加载）扩展

上述操作完成后，还需要在 PostgreSQL 创建扩展类型，好在建表时能够选择 vector 类型。这一步需要先连接 pgsql 数据库，然后在 sql 中执行。

--创建vector扩展类型CREATE EXTENSION vector;

如果执行失败，一般会报找不到 pgvector 扩展。根本原因都是上一步的 pgvector 扩展安装失败，或者版本不一致导致。一定要特别注意，自己安装并使用的 pgsql 版本，和

2.3.3 建表、插入数据、向量查询

执行成功后，通过一个建表语句验证是否可正常使用：

CREATE TABLE items (    id bigserial PRIMARY KEY,    embedding vector(3));

插入示例数据：

INSERT INTO public.items(id, embedding)VALUES(1, '[0.111,0.2333,0.555]'::public.vector);INSERT INTO public.items(id, embedding)VALUES(2, '[0.113,0.2313,0.15]'::public.vector);
#批量插入INSERT INTO items (embedding) VALUES ('[1,2,3]'),('[1.1,2.1,3.1]'),('[4,5,6]'),('[0,0,0]');

执行向量检索：

在 pgvector 中，常用的向量相似度计算方法有：

• <->: 欧几里得距离（L2 距离）

• <=>: 余弦相似度

• <+>: 内积（点积）

通常我们使用 余弦相似度 来比较两个向量的方向是否一致。

 示例：查找与向量 [1,2,3] 最相似的前 5 个 items

SELECT id, embedding <=> '[1,2,3]' AS cosine_similarityFROM itemsORDER BY cosine_similarity ASCLIMIT 5;

注意：余弦相似度范围是 [0, 1]，值越小表示两个向量越相似（方向越接近），所以用 ASC 排序。

使用 L2 距离（欧氏距离）查找最近邻

SELECT id, embedding <-> '[1,2,3]' AS l2_distanceFROM itemsORDER BY l2_distance ASCLIMIT 5;

这里示例使用的只有 3 维，但实际应用中向量维度通常都很大（比如 768 或更高，一般维数越多，对文本特征的表示越准确，区分越精准），需要对 embedding 列建立索引以加速查询：

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

三 pg_trgm-文本模糊匹配和相似度计算

PostgreSQL 的 pg_trgm 扩展是一个用于支持文本模糊匹配和相似度计算的工具，基于「三元组（Trigram）」算法。它广泛应用于模糊查询、拼写纠错、重复数据检测等场景。

3.1 pg_trgm 的核心原理

1. 三元组（Trigram）定义将文本拆分为连续的三字符片段（包含空格和特殊符号）。例如："hello" 会被拆分为 {" h"," he","hel","ell","llo","lo "}。

注意：长度不足 3 的字符串通过填充空格处理

2. 相似度计算基于两个文本的三元组集合的交集与并集的比例，计算相似度。公式：相似度 = 共有三元组数量 / 总三元组数量
   

核心函数与操作符

• similarity(text1, text2)：计算相似度（0-1，1 表示完全匹配）。

• text % text：判断相似度是否超过阈值（默认 0.3）

• <->：计算“距离”（1 - 相似度），用于排序

• 其他操作符：<%（词边界相似）、<->（严格词相似）等，支持阈值参数调整

3. 支持的相似度算法：

similarity()（余弦相似度）、word_similarity()（重叠度）等。

3.2 安装与启用

1. 安装扩展

-- 安装前需确保已安装 postgresql-contrib 包CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 验证安装

SELECT show_trgm('hello');  -- 输出: ["  h"," he","ell","hel","llo","lo "]

3.3 核心功能与示例

建表测试：

CREATE TABLE public.text_search_test (id int8 NULL,sku1 varchar NULL,sku2 varchar NULL,sku3 varchar NULL,sku4 varchar NULL,create_time timestamp NULL);# 以下sql需要逐条执行，同时执行会报错CREATE INDEX idx_gin_test_sku1 ON public.text_search_test USING gin (sku1 gin_trgm_ops);CREATE INDEX idx_gin_test_sku2 ON public.text_search_test USING gin (sku2 gin_trgm_ops);CREATE INDEX idx_gin_test_sku3 ON public.text_search_test USING gin (sku3 gin_trgm_ops);

1. 模糊匹配查询

-- 查找与 "apple" 相似度 > 0.5 的词汇SELECT word, similarity(word, 'apple') AS scoreFROM wordsWHERE word % 'apple'  -- % 操作符表示相似度超过阈值（默认 0.3）ORDER BY score DESC;

2. 设置相似度阈值

-- 临时调整阈值（范围 0~1）SET pg_trgm.similarity_threshold = 0.6;
-- 查找与 "example" 相似度 >= 0.6 的文本SELECT * FROM documents WHERE content % 'example';

3. 索引优化

为加速模糊查询，可创建 GIN 或 GiST 索引：

-- 使用 GIN 索引（适合高频读操作）CREATE INDEX idx_gin_trgm ON table_name USING gin (column_name gin_trgm_ops);
-- 使用 GiST 索引（适合写入频繁场景）CREATE INDEX idx_gist_trgm ON table_name USING gist (column_name gist_trgm_ops);

3.4 典型应用场景

1. 姓名模糊匹配

-- 查找与 "Jhon Doe" 相似的用户名SELECT username FROM users WHERE username % 'Jhon Doe';

2、地址标准化

-- 合并相似地址（如 "St." vs "Street"）SELECT a1.address, a2.address, similarity(a1.address, a2.address)FROM addresses a1, addresses a2WHERE a1.address <> a2.address   AND similarity(a1.address, a2.address) > 0.8;

3、拼写纠错

-- 找出与 "accomodation" 最接近的正确拼写SELECT word FROM dictionary ORDER BY similarity(word, 'accomodation') DESC LIMIT 3;

3.5 全文检索性能优化建议

1. 索引选择

（1）GIN 索引：查询速度快，但占用存储更大，适合读多写少的场景。

（2）GiST 索引：写入更快，但查询性能略低，适合频繁更新的字段。

索引适用场景

• 加速 LIKE、ILIKE、\~（正则表达式）等模糊查询。

• 支持中文字符，但需至少 3 个字符才能触发索引（与英文一致）

2. 调整阈值

通过 SET pg_trgm.similarity_threshold 动态调整过滤精度，平衡结果集大小与性能。

3. 文本预处理

对长文本使用 substring() 截取关键部分计算相似度。

统一大小写（lower()）和去除标点（regexp_replace()）。

3.6 常见问题

1. 扩展安装失败

确保已安装 postgresql-contrib 包。

检查用户权限：CREATE EXTENSION 需要数据库管理员权限。

2. 相似度计算不准确

短文本（<3 字符）无法生成三元组，需结合其他方法（如 Levenshtein 距离）。

调整阈值或使用 word_similarity() 处理部分匹配。

3. 性能瓶颈

未创建索引时，全表扫描会显著降低速度。

避免对大文本字段直接使用模糊匹配，可提取特征值存储。

3.7 pg_trgm 与其他扩展的对比

通过合理使用 pg_trgm，可以在 PostgreSQL 中高效实现模糊匹配需求，但需结合业务场景选择索引策略和参数调优。

四、小结

本篇详细介绍了 PostgreSQL 下向量检索扩展 pgvector 和全文检索所需的 pg_trgm 扩展，包括概念、安装过程、使用示例。下一篇将进一步介绍向量检索与全文检索结果聚合与重排序(rerank)，欢迎随时留言探讨。