腾讯云PostgreSQL 16 + pgvector向量数据库生产部署与调优实录

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install postgresql-16 postgresql-server-dev-16 build-essential git -y

sudo pg_dropcluster 16 main --stop  # 清理默认cluster
sudo pg_createcluster 16 main --start --locale=en_US.UTF-8
sudo systemctl enable postgresql

cd /tmp
git clone --branch v0.7.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

CREATE EXTENSION vector;
-- 验证版本
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- 应返回 '0.7.0'

# 内存相关（总内存8G，留2G给OS和AI推理服务）
shared_buffers = 2GB           # 推荐总内存的25%
effective_cache_size = 6GB      # 推荐总内存的75%
maintenance_work_mem = 512MB    # 索引构建时可临时增大
work_mem = 16MB                 # 排序和哈希操作

# 向量检索专用（HNSW索引性能关键）
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
enable_seqscan = off            # 强制优先走索引，适用于向量检索场景

# 写入性能（每日200篇文章+向量，写入压力不大）
synchronous_commit = off
wal_buffers = 64MB
checkpoint_timeout = 15min

sudo systemctl restart postgresql

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content_summary TEXT,
    source_url VARCHAR(512) UNIQUE,
    category VARCHAR(64),
    tags TEXT[],
    embedding vector(1024),          -- BGE-M3维度
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建标量索引（加速过滤）
CREATE INDEX idx_articles_created ON articles(created_at DESC);
CREATE INDEX idx_articles_category ON articles(category) WHERE category IS NOT NULL;

-- 创建向量索引（最关键）
CREATE INDEX idx_articles_embedding_hnsw ON articles 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 200);

import psycopg2
import numpy as np

conn = psycopg2.connect("host=localhost dbname=ai_db user=postgres")
cur = conn.cursor()

# 假设embedding为list[float]长度1024
embedding_str = '[' + ','.join(map(str, embedding_list)) + ']'

cur.execute("""
    INSERT INTO articles (title, content_summary, source_url, tags, embedding)
    VALUES (%s, %s, %s, %s, %s::vector)
    ON CONFLICT (source_url) DO UPDATE 
    SET embedding = EXCLUDED.embedding,
        updated_at = NOW()
""", (title, summary, url, tags, embedding_str))

SELECT 
    id,
    title,
    content_summary,
    tags,
    created_at,
    1 - (embedding <=> %s::vector) AS similarity
FROM articles
WHERE 
    created_at >= NOW() - INTERVAL '3 days'
    AND category = 'AI'
    AND embedding IS NOT NULL
ORDER BY embedding <=> %s::vector
LIMIT 5;

WITH similarity_pairs AS (
    SELECT 
        a1.id AS id1,
        a2.id AS id2,
        1 - (a1.embedding <=> a2.embedding) AS sim
    FROM articles a1
    JOIN articles a2 ON a1.id < a2.id
    WHERE 
        a1.created_at >= NOW() - INTERVAL '7 days'
        AND a2.created_at >= NOW() - INTERVAL '7 days'
        AND a1.title ILIKE '%AI%'
)
SELECT id1, id2, sim
FROM similarity_pairs
WHERE sim > 0.85
ORDER BY sim DESC;

采集脚本 → Dify工作流 → Qwen摘要 + BGE向量 → PostgreSQL 16 (pgvector)
                                                ↓
                                           混合查询API
                                                ↓
                                           前端展示