pgvector 2026：将 PostgreSQL 转变为高性能向量数据库——配置、调优与 RAG 集成指南

-- HNSW 索引（推荐大多数工作负载）
CREATE INDEX ON documents
  USING hnsw (embedding vector_l2_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat 索引（构建更快，召回率较低）
CREATE INDEX ON documents
  USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 100);

-- L2 距离（越小越相似）
SELECT id, embedding <-> query_vec AS distance
FROM documents ORDER BY distance LIMIT 10;

-- 余弦距离（用于归一化 embedding）
SELECT id, embedding <=> query_vec AS distance
FROM documents ORDER BY distance LIMIT 10;

docker run -d \
  --name pgvector-demo \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=vectordb \
  -p 5432:5432 \
  pgvector/pgvector:0.8.2-pg18

# 验证
docker exec pgvector-demo psql -U postgres -d vectordb -c "SELECT * FROM pg_extension WHERE extname = 'vector';"

# 安装构建依赖（Ubuntu/Debian）
sudo apt-get install postgresql-server-dev-18 build-essential git

# 克隆并构建 pgvector
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

# 在数据库中启用扩展
psql -U postgres -d mydb -c "CREATE EXTENSION IF NOT EXISTS vector;"

-- pgvector 已在 Supabase 上预装。只需启用：
CREATE EXTENSION IF NOT EXISTS vector;

-- 验证版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- 返回: 0.8.2

-- 在 RDS PostgreSQL 18 上，pgvector 作为扩展可用
CREATE EXTENSION IF NOT EXISTS vector;

-- 如需查看可用扩展
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- 检查 pgvector 版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- 预期: 0.8.2

-- 测试向量类型
SELECT '[1,2,3]'::vector(3) <-> '[4,5,6]'::vector(3) AS l2_distance;
-- 预期: ~5.196

-- 创建带向量列的表（1536 维度 = OpenAI embedding）
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    title       VARCHAR(512) NOT NULL,
    content     TEXT,
    embedding   vector(1536),
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    tenant_id   INTEGER NOT NULL DEFAULT 0
);

-- 为常用过滤列添加索引
CREATE INDEX idx_docs_tenant ON documents(tenant_id);
CREATE INDEX idx_docs_created ON documents(created_at);

-- 插入单条带 embedding 的文档
INSERT INTO documents (title, content, embedding, metadata, tenant_id)
VALUES (
    'Introduction to pgvector',
    'pgvector adds vector support to PostgreSQL...',
    ARRAY_FILL(0.0::real, ARRAY[1536])::vector(1536),  -- 占位符
    '{"author": "dibi8", "category": "database"}',
    1
);

-- 使用实际 OpenAI embedding 插入（从 Python）
-- 详见下方 RAG 集成部分的完整示例

# 从 Python 批量插入向量
import psycopg2
import numpy as np

conn = psycopg2.connect("dbname=vectordb user=postgres password=mysecretpassword host=localhost")
cur = conn.cursor()

# 生成 1 万条随机向量作为示例
batch_size = 10000
titles = [f"document_{i}" for i in range(batch_size)]
contents = [f"Content for document {i}" for i in range(batch_size)]
embeddings = np.random.randn(batch_size, 1536).astype(np.float32)
tenant_ids = [i % 10 for i in range(batch_size)]

# 使用 executemany 批量插入
args = [(titles[i], contents[i], embeddings[i].tolist(), tenant_ids[i]) for i in range(batch_size)]
cur.executemany(
    "INSERT INTO documents (title, content, embedding, tenant_id) VALUES (%s, %s, %s::vector, %s)",
    args
)
conn.commit()
print(f"已插入 {batch_size} 条文档")

-- 设置并行索引构建参数
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;

-- 使用调优参数构建 HNSW 索引
CREATE INDEX idx_docs_embedding_hnsw ON documents
  USING hnsw (embedding vector_l2_ops)
  WITH (
    m = 16,              -- 每层连接数（默认: 16）
    ef_construction = 128  -- 动态候选列表大小（默认: 64）
  );

-- 检查索引大小
SELECT pg_size_pretty(pg_relation_size('idx_docs_embedding_hnsw'));
-- 典型值: 100K 条 1536 维向量约 ~450 MB

-- 基础 ANN 搜索
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 10;

-- 带过滤的向量搜索（最常见的生产模式）
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
WHERE tenant_id = 42
  AND created_at > NOW() - INTERVAL '30 days'
  AND metadata->>'category' = 'tech'
ORDER BY embedding <-> $1::vector
LIMIT 20;

-- 首先启用 pg_trgm 用于文本搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 组合向量相似度 + 文本相关性
SELECT
    d.id,
    d.title,
    d.embedding <-> $1::vector AS vec_distance,
    similarity(d.title, $2) AS text_similarity
FROM documents d
WHERE d.title % $2  -- trigram 相似度过滤
ORDER BY
    (d.embedding <-> $1::vector) * 0.7 + (1 - similarity(d.title, $2)) * 0.3
LIMIT 10;

-- 调优 ef_search 以平衡召回率与延迟
-- 越高 = 召回率越好，查询越慢
SET hnsw.ef_search = 100;  -- 默认 40；64-128 是生产典型值

-- 检查实际查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 10;
-- 应显示: Index Scan using idx_docs_embedding_hnsw

-- 为量化存储添加 halfvec 列
ALTER TABLE documents ADD COLUMN embedding_half halfvec(1536);

-- 从全精度转换
UPDATE documents SET embedding_half = embedding::halfvec;

-- 在 halfvec 上创建 HNSW 索引
CREATE INDEX idx_docs_embedding_half_hnsw ON documents
  USING hnsw (embedding_half halfvec_l2_ops);

-- 使用 halfvec 查询（结果几乎相同）
SELECT id, title, embedding_half <-> $1::halfvec AS distance
FROM documents
ORDER BY embedding_half <-> $1::halfvec
LIMIT 10;

-- 检查空间节省
SELECT
    pg_size_pretty(pg_relation_size('idx_docs_embedding_hnsw')) AS full_size,
    pg_size_pretty(pg_relation_size('idx_docs_embedding_half_hnsw')) AS half_size;
-- half_size 通常为 full_size 的 ~45-50%

; pgbouncer.ini 用于 pgvector 工作负载
[databases]
vectordb = host=localhost port=5432 dbname=vectordb

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
reserve_pool_size = 10

pip install langchain-postgres==0.0.13 langchain-openai==0.3.0

from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings

embeddings = OpenAIEmbeddings(model="text-embedding-3-large")

vector_store = PGVector(
    connection="postgresql://postgres:mysecretpassword@localhost:5432/vectordb",
    embeddings=embeddings,
    collection_name="documents",
    use_jsonb=True,
)

# 添加文档
from langchain_core.documents import Document
docs = [
    Document(page_content="pgvector turns PostgreSQL into a vector database", metadata={"source": "blog"}),
    Document(page_content="HNSW indexes provide fast ANN search", metadata={"source": "docs"}),
]
vector_store.add_documents(docs)

# 带过滤的相似性搜索
results = vector_store.similarity_search(
    "vector database for RAG",
    k=5,
    filter={"source": "blog"}
)
for doc in results:
    print(f"Content: {doc.page_content}")

pip install llama-index-vector-stores-postgres==0.4.2

from llama_index.vector_stores.postgres import PGVectorStore
from llama_index.core import VectorStoreIndex, SimpleDirectoryReader
from llama_index.embeddings.openai import OpenAIEmbedding

vector_store = PGVectorStore.from_params(
    host="localhost",
    port=5432,
    database="vectordb",
    user="postgres",
    password="mysecretpassword",
    table_name="llama_index_docs",
    embed_dim=1536,
    hnsw_kwargs={
        "hnsw_m": 16,
        "hnsw_ef_construction": 128,
        "hnsw_ef_search": 100,
        "hnsw_dist_method": "vector_l2_ops",
    },
)

# 加载文档
documents = SimpleDirectoryReader("./data").load_data()
index = VectorStoreIndex.from_documents(documents, vector_store=vector_store)

# 查询
query_engine = index.as_query_engine()
response = query_engine.query("How does pgvector work?")
print(response)

import psycopg2
from openai import OpenAI
import numpy as np

client = OpenAI()
conn = psycopg2.connect("dbname=vectordb user=postgres password=mysecretpassword host=localhost")

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

def retrieve_documents(query: str, top_k: int = 5, tenant_id: int = 1):
    query_vec = get_embedding(query)
    cur = conn.cursor()
    cur.execute("""
        SELECT title, content, embedding <=> %s::vector AS distance
        FROM documents
        WHERE tenant_id = %s
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (query_vec, tenant_id, query_vec, top_k))
    return cur.fetchall()

# 完整 RAG 管道
def rag_query(user_question: str) -> str:
    docs = retrieve_documents(user_question, top_k=5)
    context = "\n\n".join([f"Title: {d[0]}\n{d[1]}" for d in docs])
    
    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": "Answer based on the provided context."},
            {"role": "user", "content": f"Context:\n{context}\n\nQuestion: {user_question}"}
        ]
    )
    return response.choices[0].message.content

print(rag_query("What is pgvector used for?"))

-- 启用 RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 策略：租户只能看到自己的文档
CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

-- 每会话设置租户
SET app.current_tenant = '42';

-- 现在所有查询自动按租户过滤
SELECT * FROM documents;  -- 仅可见租户 42 的文档

-- 追踪慢向量查询
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查找延迟最高的向量查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE query LIKE '%<->%'
ORDER BY mean_exec_time DESC
LIMIT 10;

# 在 postgresql.conf 中启用 pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

# 包含向量数据的完整备份
pg_dump -h localhost -U postgres -d vectordb -Fc > vectordb_backup.dump

# 恢复
pg_restore -h localhost -U postgres -d vectordb_restore vectordb_backup.dump

# 向量以文本数组形式备份并正确恢复

# 生产工作负载使用连接池
from psycopg2 import pool

conn_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=50,
    host="localhost",
    database="vectordb",
    user="postgres",
    password="mysecretpassword"
)

def search_with_pool(query_vec, limit=10):
    conn = conn_pool.getconn()
    try:
        cur = conn.cursor()
        cur.execute(
            "SELECT id, title FROM documents ORDER BY embedding <-> %s::vector LIMIT %s",
            (query_vec, limit)
        )
        return cur.fetchall()
    finally:
        conn_pool.putconn(conn)

SELECT title, embedding <-> $1::vector AS distance
FROM documents
WHERE tenant_id = 42
  AND created_at > NOW() - INTERVAL '7 days'
  AND metadata @> '{"status": "published"}'
ORDER BY embedding <-> $1::vector
LIMIT 10;

-- 对 ef_search 值做基准测试
SET hnsw.ef_search = 64;
EXPLAIN ANALYZE SELECT ... ORDER BY embedding <-> $1 LIMIT 10;

SET hnsw.ef_search = 128;
EXPLAIN ANALYZE SELECT ... ORDER BY embedding <-> $1 LIMIT 10;