Rolling out LLMs in the enterprise, a lot of teams reach for Milvus or Pinecone too early, then find themselves fighting inconsistent dual writes, awkward cross-system joins, isolated permission models and complicated backups.
Install the pgvector extension into the PostgreSQL 17 instance you already run and you not only inherit ACID guarantees outright, you can also express metadata filtering (tenant_id = 'org_123', say) and vector similarity search in a single SQL statement!
算子级原型与沙盒测试器
-- PostgreSQL 17: 创建 HNSW 向量索引并执行极速混合检索
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE tech_knowledge_base (
id BIGSERIAL PRIMARY KEY,
category VARCHAR(64),
title TEXT,
content TEXT,
embedding vector(1536) -- OpenAI text-embedding-3 维度
);
-- 构建 HNSW 索引 (m=16 邻居数, ef_construction=64 构图深度)
CREATE INDEX ON tech_knowledge_base
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- 混合查询：在指定分类下进行 Top-5 向量最近邻召回
SELECT id, title, 1 - (embedding <=> '[0.012, -0.043, ...]') AS similarity
FROM tech_knowledge_base
WHERE category = 'AI_LLM'
ORDER BY embedding <=> '[0.012, -0.043, ...]'
LIMIT 5;
