AI Search By PostgreSQL

1. AI 检索架构说明

这是当前非常主流的 AI 检索架构，可以不依赖 第三方pg_textsearch

PostgreSQL
 ├── pgvector                            向量检索（已内置）
 ├── zhparser/pg_jieba              中文分词
 ├── GIN                                     全文索引（已内置）
 ├── tsvector、tsquery               BM25/全文搜索（已内置）
 └── timescale-pg_textsearch    BM25/全文搜索（拓展超强）

 

1.1 PostgreSQL 内置全文搜索工具说明

函数 作用 简介 用例（SQL）
to_tsvector 索引文本 → 词向量 将原始文本转换为标准化词向量，自动分词、词干化、过滤停用词，为全文索引做准备 UPDATE articles SET content_ts = to_tsvector('english', content);
to_tsquery 解析查询 → 令牌 解析用户查询语句，生成可匹配词向量的结构化查询表达式，支持布尔逻辑与词干扩展 SELECT * FROM articles WHERE content_ts @@ to_tsquery('english', 'postgres & performance !bug');
@@ 匹配词向量与查询 匹配操作符，用于判断 tsvector 是否包含 to_tsquery 生成的查询令牌 SELECT title FROM articles WHERE content_ts @@ to_tsquery('english', 'fast & wireless');
ts_rank/ts_rank_cd 按相关性排序结果 计算文档与查询的相关性得分（0~1），按语义权重排序结果，提升搜索体验 SELECT title, ts_rank(content_ts, to_tsquery('english', 'fast & wireless')) AS rank FROM articles WHERE content_ts @@ to_tsquery('english', 'fast & wireless') ORDER BY rank DESC;

1.2 GIN 全文搜索加速索引

‌GIN 是 PostgreSQL 中用于加速全文搜索、JSON、数组等复杂数据类型查询的通用倒排索引引擎‌，它让你的 to_tsvector、tsquery、数组或 JSON 字段查询速度提升百倍以上，是构建高性能搜索系统的底层基石。

GIN 索引的三大核心应用场景

场景 适用字段类型 索引效果
‌全文搜索‌ tsvector ✅ 最常用，支持词干、停用词、多语言
‌数组匹配‌ text[], int[] 查找包含某个元素的行，如 tags @> '{"sql", "performance"}'
‌JSON 查询‌ jsonb 快速查找键值对，如 data @> '{"status": "active"}'

GIN 的优势 vs 其他索引

索引类型 适用场景 全文搜索支持 多字段合并 查询速度
‌GIN‌ ✅ 全文、数组、JSON ✅ 完全支持 ✅ 支持 ⚡ 极快（倒排）
GIST 大文本、地理数据 ✅ 支持（但不如 GIN 精准） ✅ 支持 🔧 较快（适合动态更新）
B-tree 数值、字符串精确匹配 ❌ 不支持 ❌ 不支持 🐢 慢（全表扫描）

测试

-- 1. 确保你已预计算了 tsvector 字段（重要！）
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || description);

-- 2. 创建 GIN 索引（关键一步！）
CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- 3. 搜索瞬间响应
SELECT name, 
       ts_headline('english', description, to_tsquery('english', 'fast & wireless')) AS snippet,
       ts_rank(search_vector, to_tsquery('english', 'fast & wireless')) AS rank
FROM products 
WHERE search_vector @@ to_tsquery('english', 'fast & wireless')
ORDER BY rank DESC
LIMIT 10;

1.3 timescale/pg_textsearch： 基于BM25 全文搜索扩展

pg_textsearch 是由 Timescale 开源的 PostgreSQL 全文检索扩展组件。它直接在 PostgreSQL 内部实现了工业级的 BM25（Best Matching 25）相关性排序算法，解决了原生全文检索（如 ts_rank）缺乏词频饱和度、逆文档频率（IDF）等排序信号的痛点，允许开发者无需外挂 Elasticsearch 或 Lucene 即可获得高质量的搜索结果。

维度 原生 tsvector/tsquery pg_textsearch
‌核心算法‌ 基于 TF-IDF，无文档长度归一化，词频线性叠加 基于 ‌BM25‌，引入词频饱和（k1）与文档长度归一化（b），避免长文档被压制、高频词被滥用
‌排序精度‌ 短文档天然占优，技术文档常被埋没 ‌搜索精准度提升达 300%‌，长篇技术文档、日志条目能精准排前
‌索引结构‌ GIN 索引，倒排列表存储词项与文档 ID 自定义 ‌BM25 索引访问方法‌，直接在页内存储统计信息（文档总数、平均长度、词频）
‌查询优化‌ ts_rank 需扫描所有匹配行计算得分 支持 ‌Block-Max WAND‌，top-k 查询无需全表扫描，查询延迟降低 2.4–6.5 倍
‌索引构建‌ 单线程构建，大表耗时长 支持 ‌并行索引构建‌，138M 文档仅需 17 分钟
‌索引体积‌ GIN 索引体积大，膨胀率高 索引体积压缩 ‌41%‌，内存与磁盘占用更低
‌分区表支持‌ 无原生分区统计，跨分区排序失真 ✅ ‌原生支持分区表‌，每个分区独立维护 BM25 统计，适用于时间序列日志、传感器数据
‌查询语法‌ @@ to_tsquery(...) + ts_rank(...) content <@> 'search terms'，直接返回负 BM25 分数，ORDER BY 即得最相关结果
‌事务与一致性‌ 与数据库事务强一致 ✅ 完全集成于 PostgreSQL 存储层，支持 WAL、流复制、pg_dump，‌零 ETL、零同步延迟‌
‌适用场景‌ 小规模、低频更新、简单关键词匹配 ‌时序数据、高频写入、RAG、日志分析、文档检索‌，替代 Elasticsearch

核心优势详解

  • ‌BM25 算法的革命性意义‌
    原生 TF-IDF 无法区分“一篇写满‘数据库’的垃圾文”和“一篇深入讲解数据库原理的长文”。
    pg_textsearch 的 BM25 通过参数 k1=1.2 控制词频饱和，b=0.75 控制文档长度惩罚，使‌内容深度‌成为排序核心，而非词频堆砌。

    举例：搜索 "database system"，原生系统可能优先返回短句“this is a database system”，而 pg_textsearch 会优先返回 5000 字的系统架构文档。

  • ‌为时序数据而生的架构设计‌
    在 IoT、监控、日志场景中，数据按时间分区写入。pg_textsearch 每个分区独立计算 IDF（逆文档频率），确保：

    • 单分区查询：得分精准
    • 跨分区查询：得分可比（需统一时间窗口）
    • 自动索引维护：新增分区自动创建 BM25 索引，无需人工干预

  • ‌企业级搜索，零外部依赖‌
    无需部署 Elasticsearch、Typesense 或向量数据库。
    仅需：

    CREATE EXTENSION pg_textsearch;
CREATE INDEX idx_docs_bm25 ON documents USING bm25(content) WITH (text_config = 'english');
SELECT * FROM documents ORDER BY content <@> 'fast wireless charger' LIMIT 10;

    搜索结果与事务强一致，写入即可见，‌一套数据库，完成存储与检索‌。

  • 宽松的开源协议：采用 PostgreSQL License，比采用 AGPL 协议的同类产品（如 ParadeDB）对商业更加友好。
  • 高性能架构：基于 C 语言编写，采用内存表（memtable）架构优化写入速度。
  • 出色的压缩率：默认启用 delta 编码与位打包（bitpacking），可减少约 41% 的索引体积。
  • 混合搜索（Hybrid Search）：可完美结合 pgvector 或 pgvectorscale 扩展，轻松实现“向量语义检索 + 传统文本检索”的混合 AI 搜索架构。
  • 版本兼容：已发布生产就绪（Production Ready）的 v1.x 版本，完美兼容 PostgreSQL 17 和 18

2. PostgreSQL 内置工具pgvector + tsvector、tsquery + GIN + zhparser

2.1. 安装扩展

进入 postgres：

docker exec -it pgvector psql -U postgres
创建扩展：
CREATE EXTENSION vector;
CREATE EXTENSION zhparser;
查看：
\dx
应该能看到：
vector
zhparser
 

2.2 创建中文分词配置（核心）

这个非常关键,PostgreSQL 默认不会中文分词

必须：

CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
 

2.3 测试中文分词

SELECT to_tsvector('chinese', '我爱北京天安门');
输出类似：
'北京':2 '天安门':3 '我':1 '爱':4
说明中文分词成功。

2.4 建表（推荐实践）

例如知识库：

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,

    title TEXT,

    content TEXT,

    -- 全文检索字段
    fts tsvector,

    -- 向量字段
    embedding vector(1024)
);
 

2.5 自动生成全文索引字段（推荐）

方法1：手动更新

UPDATE documents
SET fts = to_tsvector('chinese', content);
 

方法2（推荐）：触发器自动维护

CREATE FUNCTION documents_tsvector_update() RETURNS trigger AS $$
BEGIN
    NEW.fts :=
        to_tsvector('chinese',
            coalesce(NEW.title, '') || ' ' ||
            coalesce(NEW.content, '')
        );
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
创建触发器：
CREATE TRIGGER tsvector_update_trigger
BEFORE INSERT OR UPDATE
ON documents
FOR EACH ROW
EXECUTE FUNCTION documents_tsvector_update();

2.6 创建全文索引（极重要）

CREATE INDEX idx_documents_fts
ON documents
USING GIN(fts);
这个就是：
BM25 倒排索引
核心。

2.7 插入测试数据

INSERT INTO documents(title, content)
VALUES
('人工智能', '人工智能正在改变世界'),
('PostgreSQL', 'PostgreSQL支持向量数据库'),
('向量检索', 'RAG系统通常结合向量检索与全文搜索');
 

2.8 测试中文搜索

搜索“向量”

SELECT *
FROM documents
WHERE fts @@ to_tsquery('chinese', '向量');
 

2.9 搜索排序（类似 BM25）

SELECT
    id,
    title,
    ts_rank_cd(fts, query) AS rank
FROM documents,
     to_tsquery('chinese', '向量') query
WHERE fts @@ query
ORDER BY rank DESC;
这里：
ts_rank_cd
本质就是 PostgreSQL 的 BM25 类排序。

2.10 向量 + BM25 混合检索（现代 RAG 核心）

这才是重点。

例如：

SELECT
    id,
    title,
    ts_rank_cd(fts, query) AS bm25_score,
    1 - (embedding <=> '[0.1,0.2,...]') AS vector_score
FROM documents,
     to_tsquery('chinese', '数据库') query
WHERE fts @@ query
ORDER BY
    (
        ts_rank_cd(fts, query) * 0.4
        +
        (1 - (embedding <=> '[0.1,0.2,...]')) * 0.6
    ) DESC
LIMIT 10;
这就是：
Hybrid Search（混合检索）

3. PostgreSQL 18 + pgvector + zhparser + pg_textsearch

3.1 Dockerfile

# =========================================================
# PostgreSQL 18 + pgvector + zhparser + pg_textsearch
# =========================================================
FROM pgvector/pgvector:pg18

USER root

# ---------------------------------------------------------
# Debian 国内源（可选）
# ---------------------------------------------------------
RUN sed -i 's/deb.debian.org/mirrors.tuna.tsinghua.edu.cn/g' \
    /etc/apt/sources.list.d/debian.sources

# ---------------------------------------------------------
# 安装依赖
# ---------------------------------------------------------
RUN apt-get update && apt-get install -y \
    git \
    wget \
    curl \
    gcc \
    g++ \
    clang \
    make \
    build-essential \
    pkg-config \
    ca-certificates \
    postgresql-server-dev-18 \
    && rm -rf /var/lib/apt/lists/*

# =========================================================
# 1. 安装 SCWS（zhparser 依赖）
# =========================================================
RUN wget -O /tmp/scws.tar.bz2 \
    http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2 \
    && tar jxf /tmp/scws.tar.bz2 -C /tmp \
    && cd /tmp/scws-1.2.3 \
    && ./configure \
    && make -j$(nproc) \
    && make install \
    && ldconfig \
    && rm -rf /tmp/scws*

# =========================================================
# 2. 安装 zhparser
# =========================================================
RUN git clone https://github.com/amutu/zhparser.git /tmp/zhparser \
    && cd /tmp/zhparser \
    && make \
    && make install \
    && rm -rf /tmp/zhparser

# =========================================================
# 3. 安装 pg_textsearch（Timescale 官方版）
# =========================================================
RUN git clone https://github.com/timescale/pg_textsearch.git /tmp/pg_textsearch \
    && cd /tmp/pg_textsearch \
    && make -j$(nproc) \
    && make install \
    && rm -rf /tmp/pg_textsearch

# =========================================================
# 启用 shared_preload_libraries
# pg_textsearch 必须
# =========================================================
RUN echo "shared_preload_libraries = 'pg_textsearch'" \
    >> /usr/share/postgresql/postgresql.conf.sample

# =========================================================
# 初始化扩展
# =========================================================
RUN mkdir -p /docker-entrypoint-initdb.d

RUN printf '%s\n' \
"CREATE EXTENSION IF NOT EXISTS vector;" \
"CREATE EXTENSION IF NOT EXISTS zhparser;" \
"CREATE EXTENSION IF NOT EXISTS pg_textsearch;" \
> /docker-entrypoint-initdb.d/init-extensions.sql

USER postgres

EXPOSE 5432
 

3.2 构建

docker build -t pg18-ai-search .

3.3 运行

docker run --name pg18 \
  -v /data/postgresql:/var/lib/postgresql \
  -e TZ=Asia/Shanghai \
  -v /etc/localtime:/etc/localtime:ro \
  --restart always \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=123456' \
  -d \
  -p 5432:5432 \
  pg18-ai-search

3.4 验证

进入：

docker exec -it pg18-ai psql -U postgres
查看：
\dx
应该有：
vector
zhparser
pg_textsearch
查看所有加载的共享库
SHOW shared_preload_libraries;

3.5 初始化中文分词

-- 初始化
DROP TEXT SEARCH CONFIGURATION IF EXISTS chinese;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l
WITH simple;

-- 查看中文分词
SELECT cfgname FROM pg_ts_config;

-- 查看已安装的插件
SELECT * FROM pg_available_extensions
 

3.6 pg_textsearch 的正确打开方式（重点）

这个新版：不是：

tsvector + ts_rank

模式。而是：

USING bm25
真正 BM25 索引。

3.7 示例

建表

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT
);

创建 BM25 索引

CREATE INDEX documents_bm25_idx
ON documents
USING bm25(content)
WITH (text_config='chinese');

查询

SELECT *
FROM documents
ORDER BY content <@> 'PostgreSQL 向量数据库'
LIMIT 10;
这里：
<@>
就是：
BM25 score operator
不是：
ts_rank
了。

4. PostgreSQL 18 + pgvector + pg_jieba + pg_textsearch

下面是一个完整的：

  • PostgreSQL 18

  • pgvector

  • pg_jieba

  • pg_textsearch

  • 中文全文检索

  • BM25

  • AI Retrieval

实验环境 Dockerfile。

这个版本主要用于：

  • 学习 PostgreSQL Retrieval Engine

  • 对比 zhparser 与 pg_jieba

  • 中文全文检索实验

  • AI Agent / RAG 检索研究

4.1 Dockerfile

FROM pgvector/pgvector:pg18

USER root

# 使用清华源加速
RUN sed -i 's/deb.debian.org/mirrors.tuna.tsinghua.edu.cn/g' \
    /etc/apt/sources.list.d/debian.sources

# 安装依赖（移除 libcppjieba-dev）
RUN apt-get update && apt-get install -y \
    git \
    wget \
    curl \
    gcc \
    g++ \
    make \
    cmake \
    build-essential \
    pkg-config \
    ca-certificates \
    postgresql-server-dev-18 \
    && rm -rf /var/lib/apt/lists/*

# 安装 pg_jieba（支持 PG18）
RUN git clone https://github.com/jaiminpan/pg_jieba.git /tmp/pg_jieba \
    && cd /tmp/pg_jieba \
    && git submodule update --init --recursive \
    && mkdir build && cd build \
    && cmake -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql/18/server \
             -DCMAKE_CXX_FLAGS="-Wall -std=c++11" .. \
    && make -j$(nproc) \
    && make install \
    && rm -rf /tmp/pg_jieba
# 安装 pg_textsearch (Timescale 版本)
RUN git clone https://github.com/timescale/pg_textsearch.git /tmp/pg_textsearch \
    && cd /tmp/pg_textsearch \
    && make \
    && make install \
    && rm -rf /tmp/pg_textsearch

# 关键配置：shared_preload_libraries
RUN echo "shared_preload_libraries = 'pg_jieba, pg_textsearch'" >> /usr/share/postgresql/18/postgresql.conf.sample
RUN echo "default_text_search_config = 'jiebacfg'" >> /usr/share/postgresql/18/postgresql.conf.sample
# 初始化扩展
RUN mkdir -p /docker-entrypoint-initdb.d
RUN echo "CREATE EXTENSION IF NOT EXISTS vector;" > /docker-entrypoint-initdb.d/init-extensions.sql
RUN echo "CREATE EXTENSION IF NOT EXISTS pg_jieba;" >> /docker-entrypoint-initdb.d/init-extensions.sql
RUN echo "CREATE EXTENSION IF NOT EXISTS pg_textsearch;" >> /docker-entrypoint-initdb.d/init-extensions.sql

USER postgres
EXPOSE 5432

4.2 Build

docker build -t pg18-ai-jieba .

4.3 Run

docker run -d \
  --name pg18 \
  -p 5432:5432 \
  -e POSTGRES_USER=root_sa \
  -e POSTGRES_PASSWORD=123456 \
  -v /data/postgresql/data:/var/lib/postgresql \
  --restart always \
  pg18-ai-jieba

4.4 初始化 pg_jieba 中文配置

此步骤可跳过，直接使用内置的 jiebacfg

进入：

docker exec -it pg18 psql -U root_sa

执行：

-- 1. 创建中文分词配置（推荐方式）
CREATE TEXT SEARCH CONFIGURATION chinese_jieba (
    PARSER = pg_jieba
);

-- 2. 添加词性映射（常用映射）
ALTER TEXT SEARCH CONFIGURATION chinese_jieba
ADD MAPPING FOR n, v, a, i, e, l, j
WITH simple;

-- 3. （可选）添加更多映射，覆盖大部分情况
ALTER TEXT SEARCH CONFIGURATION chinese_jieba
ADD MAPPING FOR nr, ns, nt, nz, nw, t, s, f, m, q, r, d, p, c, u, y, z, k, w
WITH simple;

4.5 测试

ts_debug：分词

-- 使用内置配置
SELECT ts_debug(
    'jiebacfg',
    'PostgreSQL支持中文全文检索和AI向量搜索'
);

-- 使用自定义配置
SELECT ts_debug(
    'chinese_jieba',
    'PostgreSQL支持中文全文检索和AI向量搜索'
);

tsvector：分词测试

SELECT to_tsvector(
    'jiebacfg',
    'PostgreSQL支持中文全文检索和AI向量搜索'
);

tsquery：全文检索测试 

SELECT * FROM to_tsquery(
    'jiebacfg', 
    'PostgreSQL支持中文全文检索和AI向量搜索'
);

 查询所有配置

SELECT cfgname, cfgnamespace::regnamespace, cfgparser::regproc 
FROM pg_ts_config 
WHERE cfgname LIKE '%jieba%';

查询所有解析

SELECT prsname FROM pg_ts_parser;

4.7 创建 AI Retrieval 表

CREATE TABLE tool_registry (
    id BIGSERIAL PRIMARY KEY,

    function_name TEXT,
    description TEXT,
    content TEXT,

    embedding vector(1024)
);

4.8 HNSW 索引

CREATE INDEX idx_embedding_hnsw
ON tool_registry
USING hnsw (embedding vector_cosine_ops);

4.9 BM25 索引

CREATE INDEX idx_tool_bm25
ON tool_registry
USING bm25(content)
WITH (text_config='chinese_jieba');

4.10 Hybrid Search 示例

Vector Search

SELECT
    id,
    function_name,
    embedding <=> '[0.1,0.2,0.3]' AS distance
FROM tool_registry
ORDER BY embedding <=> '[0.1,0.2,0.3]'
LIMIT 10;

BM25 Search

SELECT
    id,
    function_name,
    content <@> '请假审批流程'
FROM tool_registry
ORDER BY content <@> '请假审批流程';

 

5. 其他说明

pg_jieba 的优势

  • 中文互联网语义更现代

  • 新词识别更好

  • 更适合 AI/RAG

  • 中文自然语言效果更强

但它也有问题：

  • PostgreSQL 大版本兼容性不如 zhparser

  • ARM/国产CPU 可能踩坑

  • 社区成熟度不如 zhparser

  • 部署复杂度更高

所以：

  • 企业稳定生产：很多仍然选 zhparser

  • AI/RAG/实验研究：很多开始转向 pg_jieba

 

 

 

 

技嘉 3060Ti 8g 1400

华硕 z590-p + 14700kf  1750

美光 英睿达 DDR4 3200MHZ 64G(32*2) 1800

 

posted @ 2026-05-15 19:41  ejiyuan  阅读(109)  评论(0)    收藏  举报