pgvector PostgreSQL向量扩展

-- 在每个需要使用pgvector的数据库中执行
CREATE EXTENSION vector;

-- 检查版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';

-- 升级扩展
ALTER EXTENSION vector UPDATE;

-- 创建表时指定维度
CREATE TABLE items (
    id bigserial PRIMARY KEY,
    content TEXT,
    embedding vector(1536)    -- 1536维单精度向量（如OpenAI text-embedding-3-small）
);

-- 无维度约束（同一列可存储不同维度的向量）
CREATE TABLE embeddings (
    model_id bigint,
    item_id bigint,
    embedding vector,         -- 不指定维度
    PRIMARY KEY (model_id, item_id)
);

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding halfvec(1536)   -- 半精度，存储减半
);

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding sparsevec(30000)   -- 30000维稀疏向量（如SPLADE）
);

-- 插入格式：{index:value,...}/维度（索引从1开始）
INSERT INTO items (embedding) VALUES ('{1:0.5,100:0.3,500:0.8}/30000');

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding bit(768)        -- 二值量化向量
);

INSERT INTO items (embedding) VALUES ('10110010...');

-- 插入单条
INSERT INTO items (content, embedding) VALUES ('Hello world', '[1,2,3]');

-- 批量插入
INSERT INTO items (content, embedding) VALUES
    ('文档1', '[1,2,3]'),
    ('文档2', '[4,5,6]'),
    ('文档3', '[7,8,9]');

-- Upsert（冲突时更新）
INSERT INTO items (id, content, embedding) VALUES (1, '文档', '[1,2,3]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

-- L2 距离搜索（最近邻）
SELECT id, content, embedding <-> '[3,1,2]' AS distance
FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

-- 余弦相似度搜索
SELECT id, content, 1 - (embedding <=> '[3,1,2]') AS similarity
FROM items
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;

-- 内积搜索（注意取负）
SELECT id, content, (embedding <#> '[3,1,2]') * -1 AS inner_product
FROM items
ORDER BY embedding <#> '[3,1,2]'
LIMIT 5;

-- 距离阈值过滤
SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;

-- 查找与某条记录最相似的记录
SELECT * FROM items WHERE id != 1
ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1)
LIMIT 5;

-- 带条件过滤的向量搜索
SELECT id, content, embedding <=> '[3,1,2]' AS distance
FROM items
WHERE category = 'AI'
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;

-- 平均向量
SELECT AVG(embedding) FROM items;

-- 分组平均
SELECT category, AVG(embedding) FROM items GROUP BY category;

-- 向量求和
SELECT SUM(embedding) FROM items;

-- 向量维度
SELECT vector_dims('[1,2,3]');    -- 3

-- 欧氏范数
SELECT vector_norm('[3,4]');       -- 5

-- L2归一化
SELECT l2_normalize('[3,4]');      -- [0.6, 0.8]

-- 子向量提取
SELECT subvector('[1,2,3,4,5]'::vector, 2, 3);  -- [2,3,4]（从第2位开始取3个）

-- 二值量化
SELECT binary_quantize('[1,-2,3,-4]'::vector);   -- 10（正=1，负=0）

-- 向量运算
SELECT '[1,2,3]'::vector + '[4,5,6]'::vector;     -- [5,7,9]
SELECT '[4,5,6]'::vector - '[1,2,3]'::vector;     -- [3,3,3]
SELECT '[1,2,3]'::vector * '[4,5,6]'::vector;     -- [4,10,18]（逐元素乘法）

-- 创建HNSW索引（按距离度量选择操作符类）
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);   -- 余弦距离
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);       -- L2距离
CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);       -- 内积
CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);       -- L1距离

-- 自定义参数
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
-- m: 每层最大连接数，默认16。增大→更准确但更多内存
-- ef_construction: 构建时候选列表大小，默认64。增大→构建更慢但图质量更高

-- 设置搜索时的候选列表大小（默认40）
SET hnsw.ef_search = 100;   -- 增大→更准确但更慢

-- 单次查询设置（推荐，不影响全局）
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
COMMIT;

-- 增加构建内存
SET maintenance_work_mem = '8GB';

-- 增加并行工作线程
SET max_parallel_maintenance_workers = 7;
SET max_parallel_workers = 8;

-- 并发创建索引（不锁表，生产环境推荐）
CREATE INDEX CONCURRENTLY ON items USING hnsw (embedding vector_cosine_ops);

-- 创建IVFFlat索引
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);
-- lists: 聚类数量。推荐值：行数/1000（≤100万行），sqrt(行数)（>100万行）

-- 搜索时设置探测列表数
SET ivfflat.probes = 10;
-- probes: 搜索的聚类数，默认1。推荐：sqrt(lists)
-- probes = lists 时退化为精确搜索

-- 创建半精度索引
CREATE INDEX ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

-- 查询时也转为半精度
SELECT * FROM items
ORDER BY embedding::halfvec(1536) <=> '[1,2,3]'::halfvec(1536)
LIMIT 5;

-- 二值量化索引（极致压缩）
CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

-- 两阶段搜索：粗排+精排
SELECT * FROM (
    SELECT * FROM items
    ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize('[1,-2,3]'::vector)
    LIMIT 20   -- 先多取
) ORDER BY embedding <=> '[1,-2,3]' LIMIT 5;  -- 精排取前5

-- 为特定分类创建部分索引
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WHERE (category_id = 123);

-- 查询时自动使用
SELECT * FROM items WHERE category_id = 123
ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- 严格排序（精确距离顺序）
SET hnsw.iterative_scan = strict_order;

-- 宽松排序（略失序但更高召回率）
SET hnsw.iterative_scan = relaxed_order;

-- 创建支持混合检索的表
CREATE TABLE articles (
    id bigserial PRIMARY KEY,
    title TEXT,
    content TEXT,
    category TEXT,
    embedding vector(1536),
    textsearch tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))) STORED
);

-- 创建全文索引
CREATE INDEX ON articles USING gin(textsearch);

-- 创建向量索引
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

-- 创建B-tree索引用于过滤
CREATE INDEX ON articles (category);

-- 使用分区表实现多租户隔离
CREATE TABLE tenant_documents (
    id bigserial,
    tenant_id int NOT NULL,
    content TEXT,
    embedding vector(1536),
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST(tenant_id);

-- 为每个租户创建分区
CREATE TABLE tenant_a PARTITION OF tenant_documents FOR VALUES IN (1);
CREATE TABLE tenant_b PARTITION OF tenant_documents FOR VALUES IN (2);

-- 每个分区自动拥有独立的向量索引
CREATE INDEX ON tenant_a USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON tenant_b USING hnsw (embedding vector_cosine_ops);

-- 查询时自动路由到对应分区
SELECT * FROM tenant_documents
WHERE tenant_id = 1
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;