1
-- 在 PostgreSQL 16+ 中启用 pgvector 和 pgvectorscale
2
CREATE EXTENSION IF NOT EXISTS vector;
3
CREATE EXTENSION IF NOT EXISTS vectorscale;
4

5
-- StreamingDiskANN 索引：2026 年 pgvector 的杀手锏
6
CREATE INDEX idx_docs_embedding_streaming_diskann
7
ON documents USING diskann (embedding vector_cosine_ops)
8
WITH (max_retries = 3);
9

10
-- 混合搜索：向量相似度 + SQL 条件过滤
11
SELECT
12
  id,
13
  title,
14
  content,
15
  embedding <=> $1::vector AS cosine_distance
16
FROM documents
17
WHERE
18
  category = 'cardiology'        -- 传统 SQL 过滤
19
  AND published_at > '2025-01-01'
20
  AND embedding <=> $1::vector < 0.5  -- 向量相似度阈值
21
ORDER BY embedding <=> $1::vector
22
LIMIT 20;

1
应用代码 → LangChain → Pinecone（向量搜索）+ PostgreSQL（业务数据）
2
                    ↘ 手动同步数据（要么双写，要么 CDC 管道）

1
# 2026 年的推荐方案：全部用 PostgreSQL
2
import psycopg2
3
from sentence_transformers import SentenceTransformer
4

5
# 加载轻量级 embedding 模型
6
model = SentenceTransformer("BAAI/bge-small-en-v1.5")
7

8
def hybrid_search(query: str, category: str, limit: int = 10):
9
    embedding = model.encode(query).tolist()
10

11
    conn = psycopg2.connect(
12
        host="localhost",
13
        dbname="blog",
14
        user="app_user"
15
    )
16

17
    # 一次查询搞定全部
18
    with conn.cursor() as cur:
19
        cur.execute("""
20
            SELECT
21
                d.id, d.title, d.content,
22
                1 - (d.embedding <=> %s::vector) AS similarity,
23
                d.view_count,
24
                d.published_at
25
            FROM documents d
26
            WHERE
27
                d.category = %s
28
                AND d.published_at > NOW() - INTERVAL '7 days'
29
                AND d.embedding <=> %s::vector < 0.4
30
            ORDER BY d.embedding <=> %s::vector
31
            LIMIT %s
32
        """, (embedding, category, embedding, embedding, limit))
33

34
        results = cur.fetchall()
35

36
    conn.close()
37
    return results
38

39
# 用起来
40
articles = hybrid_search(
41
    query="什么是 RAG 的最佳实践？",
42
    category="AI",
43
    limit=5
44
)

1
┌─────────────────┬─────────────────────┬─────────────────────┐
2
│     场景        │    推荐方案          │    原因              │
3
├─────────────────┼─────────────────────┼─────────────────────┤
4
│ < 50M 向量      │ pgvector            │ 简单、一致性保证      │
5
│ 50M-500M 向量   │ Qdrant / Milvus     │ 分布式索引性能更强    │
6
│ > 1B 向量       │ Milvus              │ 原生分布式分片        │
7
│ 超低延迟 <10ms  │ Redis + vector      │ 纯内存，延迟最低      │
8
│ 原型/PoC        │ Chroma              │ 零配置，快速验证      │
9
│ 离线/边缘设备    │ LanceDB             │ 嵌入式，无服务端      │
10
└─────────────────┴─────────────────────┴─────────────────────┘

1
开始：你的应用需要向量搜索吗？
2
│
3
├── 你的数据量 < 5000 万向量？
4
│   ├── 你已经用 PostgreSQL 了吗？
5
│   │   ├── ✅ → 直接用 pgvector + pgvectorscale（无脑选）
6
│   │   └── ❌ → 还是可以选 pgvector，迁移成本值得
7
│   │
8
├── 你的数据量 5000 万 - 5 亿向量？
9
│   ├── 你需要复杂 SQL 过滤吗？
10
│   │   ├── ✅ → pgvector（DiskANN）+ 分片
11
│   │   └── ❌ → Qdrant / Milvus
12
│   │
13
├── 你的数据量 > 5 亿向量？
14
│   └── → Milvus（原生分布式）
15
│
16
└── 延迟要求 < 10ms p99？
17
    └── → Redis + Vector Search

1
# 成本估算脚本
2
options = [
3
    {
4
        "name": "pgvector (自托管, 2×r6g.xlarge)",
5
        "monthly_compute": "$186 × 2 = $372",
6
        "monthly_storage": "$0.08/GB × 50GB = $4",
7
        "monthly_total": "$376",
8
        "notes": "已包含在现有 PostgreSQL 实例中，无需额外节点"
9
    },
10
    {
11
        "name": "Pinecone (p2.x1, 1000 万向量)",
12
        "monthly_compute": "$0.154/小时 × 730h = $112",
13
        "monthly_storage": "~$70 (1000 万向量)",
14
        "monthly_total": "$182",
15
        "notes": "全托管，但超出免费额度后增长较快"
16
    },
17
    {
18
        "name": "Qdrant (自托管, 3×c6i.xlarge)",
19
        "monthly_compute": "$248 × 3 = $744",
20
        "monthly_storage": "$0.08/GB × 80GB = $6.40",
21
        "monthly_total": "$750",
22
        "notes": "三节点集群，高可用"
23
    },
24
    {
25
        "name": "Milvus (自托管, 3×m6i.xlarge)",
26
        "monthly_compute": "$310 × 3 = $930",
27
        "monthly_storage": "$0.08/GB × 100GB = $8",
28
        "monthly_total": "$938",
29
        "notes": "含 Query Node + Index Node + Data Node"
30
    }
31
]
32

33
for opt in options:
34
    print(f"{opt['name']}:")
35
    print(f"  月成本: {opt['monthly_total']}")
36
    print(f"  备注: {opt['notes']}")
37
    print()

1
-- 2026 年的混合推荐查询
2
-- 需求：推荐「用户喜欢的、最近 7 天内的、和当前文章语义相似的、用户没看过的」文章
3

4
WITH user_profile AS (
5
    -- 获取用户偏好向量和行为数据
6
    SELECT
7
        u.embedding AS user_preference_vector,
8
        array_agg(DISTINCT h.article_id) AS read_articles,
9
        u.preferred_categories
10
    FROM users u
11
    JOIN read_history h ON h.user_id = u.id
12
    WHERE u.id = 42
13
      AND h.read_at > NOW() - INTERVAL '30 days'
14
    GROUP BY u.id
15
),
16

17
candidates AS (
18
    SELECT
19
        a.id,
20
        a.title,
21
        a.embedding <=> up.user_preference_vector AS relevance_score,
22
        a.popularity_score,
23
        a.published_at,
24
        a.category
25
    FROM articles a
26
    CROSS JOIN user_profile up
27
    WHERE
28
        a.published_at > NOW() - INTERVAL '7 days'    -- 时效性
29
        AND a.category = ANY(up.preferred_categories)  -- 偏好分类
30
        AND a.id NOT IN (SELECT unnest(up.read_articles)) -- 去重
31
        AND a.embedding <=> up.user_preference_vector < 0.6  -- 语义阈值
32
)
33

34
SELECT
35
    id,
36
    title,
37
    category,
38
    (1 - relevance_score) * 0.6 +
39
    (popularity_score / 100) * 0.2 +
40
    CASE
41
        WHEN published_at > NOW() - INTERVAL '1 day' THEN 0.2
42
        WHEN published_at > NOW() - INTERVAL '3 days' THEN 0.1
43
        ELSE 0.05
44
    END AS final_score
45
FROM candidates
46
ORDER BY final_score DESC
47
LIMIT 20;