Skip to content
Published on

PostgreSQL + pgvector 向量检索实战指南：从 RAG 到混合检索

🇰🇷KO🇺🇸EN🇯🇵JA🇨🇳ZH
필사 모드
分享
Authors

1. 为什么选 PostgreSQL + pgvector

不用专用向量 DB(Pinecone、Qdrant、Weaviate) 而选择 PostgreSQL + pgvector 的理由：

特性专用向量 DBPostgreSQL + pgvector
额外基础设施需要不需要 (复用现有 PostgreSQL)
ACID 事务有限完整支持
JOIN/关系型查询不支持可自由组合
混合检索有限tsvector + vector
运维复杂度高低 (复用现有 DBA)
扩展性数十亿向量数千万级别

结论：如果向量数量在数千万以下，并且需要和关系型数据一起管理，那么 pgvector 是最优解。

2. 安装与配置

2.1 安装 pgvector

# Ubuntu/Debian
sudo apt install postgresql-17-pgvector

# macOS (Homebrew)
brew install pgvector

# Docker
docker run -d --name pgvector \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  pgvector/pgvector:pg17

2.2 启用扩展

-- 安装 pgvector 扩展
CREATE EXTENSION IF NOT EXISTS vector;

-- 确认版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- 0.8.0

3. 基本用法

3.1 创建表

-- 文档表 (1536 维 = OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),  -- 向量列！
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 384 维 (sentence-transformers/all-MiniLM-L6-v2)
CREATE TABLE chunks (
    id BIGSERIAL PRIMARY KEY,
    doc_id BIGINT REFERENCES documents(id),
    chunk_text TEXT NOT NULL,
    embedding vector(384),
    chunk_index INT
);

3.2 插入数据

-- 单条插入
INSERT INTO documents (title, content, embedding)
VALUES (
    'Kubernetes RBAC Guide',
    'RBAC is a method of regulating access...',
    '[0.1, 0.2, 0.3, ...]'::vector  -- 1536 维向量
);

-- 在 Python 中批量插入

import psycopg2
from pgvector.psycopg2 import register_vector
import numpy as np

conn = psycopg2.connect("dbname=mydb user=postgres password=secret")
register_vector(conn)

cur = conn.cursor()

# 生成 OpenAI 嵌入
from openai import OpenAI
client = OpenAI()

texts = ["Kubernetes RBAC guide", "Docker networking basics", ...]
response = client.embeddings.create(
    model="text-embedding-3-small",
    input=texts
)

# 批量插入
for text, emb_data in zip(texts, response.data):
    embedding = np.array(emb_data.embedding)
    cur.execute(
        "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
        (text, text, embedding)
    )

conn.commit()

3.3 相似度检索

-- 余弦相似度 (最常用)
SELECT id, title,
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- L2 距离
SELECT id, title,
       embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- 内积 (Inner Product) — 在归一化向量上与余弦等价
SELECT id, title,
       (embedding <#> '[0.1, 0.2, ...]'::vector) * -1 AS similarity
FROM documents
ORDER BY embedding <#> '[0.1, 0.2, ...]'::vector
LIMIT 10;

运算符汇总：

运算符含义用途
<->L2 距离基于欧氏距离
<=>余弦距离方向相似度 (最常用)
<#>内积 (负值)用于归一化向量

4. 索引：HNSW vs IVFFlat

4.1 IVFFlat

-- 创建 IVFFlat 索引
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- 检索时设置 probe 数量 (精度与速度的取舍)
SET ivfflat.probes = 10;
参数说明推荐值
lists聚类数量√(行数) ~ 行数/1000
probes检索的聚类数量lists/10 ~ lists/5

4.2 HNSW (推荐)

-- 创建 HNSW 索引 (构建时间长但检索快)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- 检索时设置 ef_search
SET hnsw.ef_search = 100;
参数说明推荐值
m连接数16~64
ef_construction构建质量200+
ef_search检索质量40~200

4.3 HNSW vs IVFFlat 对比

特性IVFFlatHNSW
构建速度快慢
检索速度一般快
Recall一般 (取决于 probe)高
内存少多
更新需要重建可实时更新

推荐：大多数情况下用 HNSW。如果数据变更频繁或者内存受限，就选 IVFFlat。

5. 混合检索：向量 + 全文检索

5.1 Full-Text Search + Vector Search

-- 添加 tsvector 列
ALTER TABLE documents ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;

CREATE INDEX ON documents USING gin(tsv);

-- 混合检索函数
CREATE OR REPLACE FUNCTION hybrid_search(
    query_text TEXT,
    query_embedding vector(1536),
    match_count INT DEFAULT 10,
    vector_weight FLOAT DEFAULT 0.7,
    text_weight FLOAT DEFAULT 0.3
)
RETURNS TABLE (id BIGINT, title TEXT, score FLOAT) AS $$
BEGIN
    RETURN QUERY
    WITH vector_results AS (
        SELECT d.id, d.title,
               1 - (d.embedding <=> query_embedding) AS vector_score
        FROM documents d
        ORDER BY d.embedding <=> query_embedding
        LIMIT match_count * 3
    ),
    text_results AS (
        SELECT d.id, d.title,
               ts_rank(d.tsv, plainto_tsquery('english', query_text)) AS text_score
        FROM documents d
        WHERE d.tsv @@ plainto_tsquery('english', query_text)
        LIMIT match_count * 3
    ),
    combined AS (
        SELECT
            COALESCE(v.id, t.id) AS id,
            COALESCE(v.title, t.title) AS title,
            COALESCE(v.vector_score, 0) * vector_weight +
            COALESCE(t.text_score, 0) * text_weight AS score
        FROM vector_results v
        FULL OUTER JOIN text_results t ON v.id = t.id
    )
    SELECT c.id, c.title, c.score
    FROM combined c
    ORDER BY c.score DESC
    LIMIT match_count;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM hybrid_search(
    'Kubernetes RBAC security',
    '[0.1, 0.2, ...]'::vector(1536)
);

5.2 Reciprocal Rank Fusion (RRF)

CREATE OR REPLACE FUNCTION rrf_search(
    query_text TEXT,
    query_embedding vector(1536),
    match_count INT DEFAULT 10,
    rrf_k INT DEFAULT 60
)
RETURNS TABLE (id BIGINT, title TEXT, rrf_score FLOAT) AS $$
BEGIN
    RETURN QUERY
    WITH vector_ranked AS (
        SELECT d.id, d.title,
               ROW_NUMBER() OVER (ORDER BY d.embedding <=> query_embedding) AS rank
        FROM documents d
        LIMIT match_count * 5
    ),
    text_ranked AS (
        SELECT d.id, d.title,
               ROW_NUMBER() OVER (
                   ORDER BY ts_rank(d.tsv, plainto_tsquery('english', query_text)) DESC
               ) AS rank
        FROM documents d
        WHERE d.tsv @@ plainto_tsquery('english', query_text)
        LIMIT match_count * 5
    ),
    fused AS (
        SELECT
            COALESCE(v.id, t.id) AS id,
            COALESCE(v.title, t.title) AS title,
            COALESCE(1.0 / (rrf_k + v.rank), 0) +
            COALESCE(1.0 / (rrf_k + t.rank), 0) AS rrf_score
        FROM vector_ranked v
        FULL OUTER JOIN text_ranked t ON v.id = t.id
    )
    SELECT f.id, f.title, f.rrf_score
    FROM fused f
    ORDER BY f.rrf_score DESC
    LIMIT match_count;
END;
$$ LANGUAGE plpgsql;

6. RAG 流水线集成

6.1 Python 完整示例

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI
import numpy as np

client = OpenAI()
conn = psycopg2.connect("dbname=ragdb user=postgres password=secret")
register_vector(conn)

def embed(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-small", input=text
    )
    return resp.data[0].embedding

def rag_query(question: str, top_k: int = 5) -> str:
    query_vec = embed(question)

    cur = conn.cursor()
    cur.execute("""
        SELECT title, content,
               1 - (embedding <=> %s::vector) AS similarity
        FROM documents
        WHERE 1 - (embedding <=> %s::vector) > 0.7
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (query_vec, query_vec, query_vec, top_k))

    results = cur.fetchall()
    context = "\n\n".join([
        f"[{r[0]}] (similarity: {r[2]:.3f})\n{r[1]}"
        for r in results
    ])

    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": f"Answer based on context:\n{context}"},
            {"role": "user", "content": question}
        ]
    )
    return response.choices[0].message.content

# 使用
answer = rag_query("Kubernetes RBAC 中 ClusterRole 和 Role 有什么区别？")
print(answer)

7. 性能调优

7.1 核心配置

-- 工作内存 (索引构建/检索时)
SET maintenance_work_mem = '2GB';  -- HNSW 构建时
SET work_mem = '256MB';             -- 检索时

-- 并行处理
SET max_parallel_workers_per_gather = 4;
SET max_parallel_maintenance_workers = 4;

-- HNSW 构建优化
SET maintenance_work_mem = '4GB';
-- 构建结束后还原

7.2 基准测试

100 万条向量 (1536 维)，PostgreSQL 17 + pgvector 0.8.0：

索引构建时间检索延迟 (p50)Recall@10内存
无索引 (brute)-850ms100%0
IVFFlat (lists=1000, probes=50)45s8ms95%1.2GB
HNSW (m=16, ef=200)12min3ms99%2.8GB

8. 测验

Q1. pgvector 的 <=> 运算符计算的是什么？

余弦距离(1 - cosine_similarity)。值越小越相似。要换算成相似度就用 1 - (a <=> b)。

Q2. HNSW 和 IVFFlat 中检索速度更快的是哪个？

HNSW。构建慢，但检索快而且 Recall 也高。大多数生产环境都推荐它。

Q3. 混合检索比纯向量检索更好的理由是什么？

向量检索擅长语义相似度，但对关键词精确匹配较弱。与全文检索结合后，可以同时拿到 语义相似度 + 关键词精确度。

Q4. RRF(Reciprocal Rank Fusion) 的原理是什么？

把各路检索结果的 排名倒数 相加。优点是无需归一化就能融合分数量纲不同的检索结果。

Q5. IVFFlat 的 lists 与 probes 参数是什么关系？

lists 是聚类数量，probes 是检索时要探查的聚类数量。probes 越大越精确但越慢。通常 probes = lists/10 ~ lists/5。

Q6. 什么情况下应该选 pgvector 而不是专用向量 DB？

(1) 需要与关系型数据做 JOIN (2) 需要 ACID 事务 (3) 向量数量在数千万以下 (4) 想把额外基础设施的运维负担降到最低。

Discuss on Twitter • View on GitHub