云数据库 PostgreSQL 基于 PostgreSQL + pgvector 的智能客服向量检索系统

一、背景

随着企业客服场景的智能化升级，传统基于关键词匹配的客服系统，已难以满足用户对语义理解和多模态检索的需求。

本方案基于 PostgreSQL 15 + pgvector 0.8.2扩展，构建了一套智能客服系统，支持文本与图片的向量化检索，并利用余弦相似度（Cosine Similarity）实现高精度的语义匹配，从而提升客服系统的智能化水平与用户体验。

二、目录

一、环境检测与扩展安装

二、清理旧表

三、创建核心表结构

四、创建辅助索引

五、插入智能客服示例数据

六、统计信息更新

七、创建 HNSW 向量索引

八、搜索参数配置

九、向量检索功能验证

十、EXPLAIN ANALYZE 索引验证

十一、运维监控 SQL

十二、索引预热

十三、技术架构总结

四、步骤1：获取数据库环境信息

1. 获取 PostgreSQL 版本。

输入 SQL：

SELECT version();

输出结果（1行，耗时2.1ms）：

2. 获取关键数据库参数。

输入 SQL：

SELECT name, setting, unitFROM pg_settingsWHERE name IN ( 'shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem', 'max_connections', 'max_parallel_workers', 'max_parallel_workers_per_gather', 'wal_level')ORDER BY name;

输出结果（8行，耗时3.6ms）：

注意：说明：

注意：shared_buffers = 12GB，effective_cache_size = 4GB，maintenance_work_mem = 512MB，满足向量检索场景要求。

五、步骤2：安装 pgvector 扩展

输入 SQL：

CREATE EXTENSION IF NOT EXISTS vector;

输出：执行成功，耗时1.7ms。

验证 SQL：

SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

输出结果（1行，耗时1.9ms）：

注意：说明：

注意：pgvector 0.8.2已安装，支持 HNSW 索引和 vector_cosine_ops 运算符。

七、步骤3：清理旧表（如果存在）

按依赖顺序删除，确保幂等执行：

DROP TABLE IF EXISTS chat_messages CASCADE; — 耗时5.7msDROP TABLE IF EXISTS chat_sessions CASCADE; — 耗时2.7msDROP TABLE IF EXISTS text_vectors CASCADE; — 耗时3.7msDROP TABLE IF EXISTS image_vectors CASCADE; — 耗时3.4ms

注意：说明：

注意：脚本支持幂等执行，重复运行不会报错。

九、步骤4：创建图片向量表 image_vectors（768维）

输入 SQL：

CREATE TABLE image_vectors ( id BIGSERIAL PRIMARY KEY, image_id VARCHAR(64) NOT NULL UNIQUE, image_url TEXT NOT NULL, thumbnail_url TEXT, embedding vector(768) NOT NULL, — 使用大模型生成 输出 768 维 category VARCHAR(100), tags TEXT[], source VARCHAR(50) DEFAULT 'upload', metadata JSONB DEFAULT '{}', status SMALLINT DEFAULT 1, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());COMMENT ON TABLE image_vectors IS '图片向量表 – 智能客服图片检索';COMMENT ON COLUMN image_vectors.embedding IS '使用大模型生成的768维图片向量';

输出：执行成功，耗时5.3ms + 1.8ms。

验证表结构：

SELECT column_name, data_type, is_nullable, column_defaultFROM information_schema.columnsWHERE table_name = 'image_vectors'ORDER BY ordinal_position;

输出结果（12行，耗时7.6ms）：

十、步骤5：创建文本向量表 text_vectors（1024维）

输入 SQL：

CREATE TABLE text_vectors ( id BIGSERIAL PRIMARY KEY, doc_id VARCHAR(64) NOT NULL UNIQUE, title TEXT, content TEXT NOT NULL, content_type VARCHAR(20) DEFAULT 'faq', embedding vector(1024) NOT NULL, — 大模型 输出 1024 维 category VARCHAR(100), tags TEXT[], source VARCHAR(100), metadata JSONB DEFAULT '{}', priority SMALLINT DEFAULT 0, status SMALLINT DEFAULT 1, expire_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW());COMMENT ON TABLE text_vectors IS '文本向量表 – 智能客服知识库检索';COMMENT ON COLUMN text_vectors.embedding IS '大模型生成的1024维文本向量';

输出：执行成功，耗时4.3ms + 1.7ms。

验证表结构（15列）：

十一、步骤6：创建对话记录表 chat_sessions + chat_messages

输入 SQL（会话表）：

CREATE TABLE chat_sessions ( id BIGSERIAL PRIMARY KEY, session_id VARCHAR(64) NOT NULL UNIQUE, user_id VARCHAR(64) NOT NULL, channel VARCHAR(20) DEFAULT 'web', status VARCHAR(20) DEFAULT 'active', created_at TIMESTAMPTZ DEFAULT NOW(), closed_at TIMESTAMPTZ);

输出：执行成功，耗时3.5ms。

输入 SQL（消息表）：

CREATE TABLE chat_messages ( id BIGSERIAL PRIMARY KEY, session_id VARCHAR(64) NOT NULL, role VARCHAR(10) NOT NULL, content_type VARCHAR(20) DEFAULT 'text', content TEXT NOT NULL, image_url TEXT, embedding vector(1024), — 1024维向量用于相似问题推荐 matched_doc_ids TEXT[], confidence REAL, feedback SMALLINT, created_at TIMESTAMPTZ DEFAULT NOW());COMMENT ON TABLE chat_messages IS '对话消息表 – 含向量字段用于相似问题推荐';

输出：执行成功，耗时3.5ms + 1.7ms。

确认所有核心表：

SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))FROM information_schema.tablesWHERE table_schema = 'public' AND table_name IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')ORDER BY table_name;

输出结果（4行，耗时3.3ms）：

十三、步骤7：创建 B-Tree + GIN 辅助索引

共创建11个辅助索引：

各索引创建 SQL 示例（部分索引）：

— 图片分类索引（部分索引，仅 status=1 的记录）CREATE INDEX idx_image_vectors_category ON image_vectors (category) WHERE status = 1;– 图片标签 GIN 索引CREATE INDEX idx_image_vectors_tags ON image_vectors USING GIN (tags) WHERE status = 1;– 图片元数据 JSONB GIN 索引CREATE INDEX idx_image_vectors_metadata ON image_vectors USING GIN (metadata);

列出所有已创建索引：

SELECT indexname, indexdefFROM pg_indexesWHERE schemaname = 'public' AND tablename IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')ORDER BY tablename, indexname;

输出结果（18行，耗时3.4ms）：

十五、步骤8：插入 FAQ 知识库数据（12条）

注意：说明：

注意：embedding 字段需要你使用大模型生成。

输入 SQL 示例（faq-001）：

INSERT INTO text_vectors (doc_id, title, content, content_type, embedding, category, tags, priority)VALUES ( 'faq-001', '如何重置密码', '你可以通过以下步骤重置密码：1. 单击登录页面的"忘记密码"链接；2. 输入注册邮箱； 3. 查收重置邮件；4. 单击邮件中的链接设置新密码。', 'faq', (SELECT array_agg(random())::vector(1024) FROM generate_series(1, 1024)), '账号管理', ARRAY['账号管理', 'faq'], 2);

共插入12条 FAQ 数据，全部成功：

验证 SQL：

SELECT doc_id, title, category, priority, status, LEFT(content, 40) || '…' AS content_previewFROM text_vectorsORDER BY priority DESC, doc_id;

输出结果（12行，耗时1.9ms）：

十六、步骤9：插入图片向量示例数据（8条）

注意：说明：

注意：embedding 字段需要你使用大模型生成。

输入 SQL 示例（img-001）：

INSERT INTO image_vectors (image_id, image_url, embedding, category, tags, metadata)VALUES ( 'img-001', 'https://cdn.example.com/products/phone_x1.jpg', (SELECT array_agg(random())::vector(768) FROM generate_series(1, 768)), '产品图片', ARRAY['产品图片', 'smart_cs'], '{"width":800,"height":600,"format":"jpg"}'::jsonb);

共插入8条图片数据，全部成功：

十七、步骤10：插入对话示例数据

会话插入 SQL：

INSERT INTO chat_sessions (session_id, user_id, channel, status)VALUES ('sess-001', 'user-1001', 'web', 'active'), ('sess-002', 'user-1002', 'wechat', 'active'), ('sess-003', 'user-1003', 'app', 'closed');

输出：影响3行，耗时1.9ms

共插入8条对话消息（3个会话）：

十九、步骤11：执行 ANALYZE 更新统计信息

ANALYZE image_vectors; — 耗时2.8msANALYZE text_vectors; — 耗时2.1msANALYZE chat_sessions; — 耗时1.8msANALYZE chat_messages; — 耗时1.9ms

各表数据量统计：

SELECT 'image_vectors' AS table_name, count(*) AS row_count FROM image_vectorsUNION ALL SELECT 'text_vectors', count(*) FROM text_vectorsUNION ALL SELECT 'chat_sessions', count(*) FROM chat_sessionsUNION ALL SELECT 'chat_messages', count(*) FROM chat_messagesORDER BY table_name;

输出结果（4行，耗时2.5ms）：

注意：说明：

注意：4张核心表，31条示例数据。

21、步骤12：图片向量 HNSW 索引（768维）

输入 SQL：

CREATE INDEX idx_image_vectors_embedding_cosineON image_vectorsUSING hnsw (embedding vector_cosine_ops)WITH (m = 16, ef_construction = 128);

输出：执行成功，耗时2.7ms。

22、步骤13：文本向量 HNSW 索引（1024维）

输入 SQL：

CREATE INDEX idx_text_vectors_embedding_cosineON text_vectorsUSING hnsw (embedding vector_cosine_ops)WITH (m = 16, ef_construction = 128);

输出：执行成功，耗时2.6ms。

23、步骤14：对话消息向量 HNSW 索引（1024维）

输入 SQL：

CREATE INDEX idx_chat_messages_embedding_cosineON chat_messagesUSING hnsw (embedding vector_cosine_ops)WITH (m = 16, ef_construction = 64);

输出：执行成功，耗时2.5ms。

查看 HNSW 索引大小：

SELECT indexname AS index_name, pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size, indexdefFROM pg_indexesWHERE schemaname = 'public' AND indexname LIKE '%embedding_cosine'ORDER BY indexname;

输出结果（3行，耗时2.9ms）：

注意：说明：

注意：HNSW 索引参数：

25、步骤15：设置 HNSW 搜索参数

输入 SQL：

— 设置搜索候选列表大小（值越大检索越精确，推荐100 ~ 200）SET hnsw.ef_search = 100;– 开启迭代扫描模式（解决 WHERE 过滤时结果不足的问题）SET hnsw.iterative_scan = relaxed_order;

输出：两条均执行成功。

确认设置：

SHOW hnsw.ef_search;

输出结果：

建议：生产环境可将参数写入 postgresql.conf：

# pgvector HNSW 搜索参数hnsw.ef_search = 100hnsw.iterative_scan = relaxed_order

27、步骤16：以图搜图（余弦相似度 Top 5）

输入 SQL：

SELECT iv.image_id, iv.image_url, iv.category, iv.tags, 1 – (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')) AS similarityFROM image_vectors ivWHERE iv.status = 1ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')LIMIT 5;

输出结果（5行，耗时2.6ms）：

注意：说明：

注意：验证通过，自身相似度为1.0，其余按余弦相似度降序排列。

28、步骤17：分类过滤图片检索（category='产品图片'）

输入 SQL：

SELECT iv.image_id, iv.image_url, iv.category, 1 – (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004')) AS similarityFROM image_vectors ivWHERE iv.status = 1 AND iv.category = '产品图片'ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004')LIMIT 5;

输出结果（5行，耗时1.9ms）：

注意：说明：

注意：验证通过，以故障图片 img-004 为查询向量，仅在"产品图片"分类下检索，结果符合预期。

29、步骤18：FAQ 语义检索（余弦相似度 Top 5）

输入 SQL：

SELECT tv.doc_id, tv.title, tv.category, tv.priority, 1 – (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-001')) AS similarityFROM text_vectors tvWHERE tv.status = 1 AND (tv.expire_at IS NULL OR tv.expire_at > NOW())ORDER BY tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-001')LIMIT 5;

输出结果（5行，耗时2.1ms）：

注意：说明：

注意：验证通过，FAQ 语义检索正常工作，支持过期时间过滤。

30、步骤19：带置信度阈值的检索（similarity > 0.5）

输入 SQL：

SELECT tv.doc_id, tv.title, tv.category, 1 – (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-004')) AS similarityFROM text_vectors tvWHERE tv.status = 1 AND 1 – (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-004')) > 0.5ORDER BY tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-004')LIMIT 5;

输出结果（5行，耗时2.3ms）：

注意：说明：

31、步骤20：混合检索（向量70% + 全文30%）

输入 SQL：

SELECT tv.doc_id, tv.title, LEFT(tv.content, 50) AS content_preview, 1 – (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-003')) AS vector_score, ts_rank(to_tsvector('simple', tv.content), plainto_tsquery('simple', '发货')) AS text_score, 0.7 * (1 – (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-003'))) + 0.3 * ts_rank(to_tsvector('simple', tv.content), plainto_tsquery('simple', '发货')) AS hybrid_scoreFROM text_vectors tvWHERE tv.status = 1ORDER BY hybrid_score DESCLIMIT 5;

输出结果（5行，耗时2.5ms）：

注意：说明：

注意：示例数据使用随机向量，全文检索中关键词"发货"只在 faq-003 标题匹配。生产环境中真实 Embedding 会使 hybrid_score 更有区分度。

32、步骤21：相似问题推荐（对话消息向量检索）

输入 SQL：

SELECT cm.session_id, cm.role, LEFT(cm.content, 50) AS content_preview, 1 – (cm.embedding <=> (SELECT embedding FROM chat_messages WHERE content LIKE '%密码%' LIMIT 1)) AS similarityFROM chat_messages cmWHERE cm.role = 'user'ORDER BY cm.embedding <=> (SELECT embedding FROM chat_messages WHERE content LIKE '%密码%' LIMIT 1)LIMIT 5;

输出结果（5行，耗时2.0ms）：

注意：说明：

注意：验证通过，基于用户历史提问的向量检索，可用于"猜你想问"功能。

34、步骤22：EXPLAIN ANALYZE 验证查询计划

输入 SQL：

EXPLAIN (ANALYZE, BUFFERS)SELECT iv.image_id, 1 – (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')) AS similarityFROM image_vectors ivWHERE iv.status = 1ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')LIMIT 10;

输出结果（耗时2.0ms）：

Limit (cost=3.48..3.50 rows=8 width=24) (actual time=0.097…) Buffers: shared hit=67 InitPlan 1 (returns $0) -> Seq Scan on image_vectors (cost=0.00..1.10 rows=1 …) Filter: ((image_id)::text = 'img-001'::text) Rows Removed by Filter: 7 Buffers: shared hit=1 InitPlan 2 (returns $1) -> Seq Scan on image_vectors image_vectors_1 (cost=0.00..1.10 …) Filter: ((image_id)::text = 'img-001'::text) Rows Removed by Filter: 7 Buffers: shared hit=1 -> Sort (cost=1.28..1.30 rows=8 width=24) (actual time=0….) Sort Key: ((iv.embedding <=> $1)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=67 -> Seq Scan on image_vectors iv (cost=0.00..1.16 …) Filter: (status = 1) Buffers: shared hit=67Planning Time: 0.101 msExecution Time: 0.115 ms

分析：当前仅8行数据，PostgreSQL 优化器正确选择了 Seq Scan（对小表更高效）。当数据量增长到数万行以上时，HNSW Index Scan 将自动启用。之前的50万行验证中已确认 HNSW 索引命中，查询延迟仅1.753ms。

36、步骤23：向量表存储统计

输入 SQL：

SELECT c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS data_size, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size, s.n_live_tup AS row_countFROM pg_stat_user_tables sJOIN pg_class c ON s.relid = c.oidWHERE c.relname IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')ORDER BY pg_total_relation_size(c.oid) DESC;

输出结果（4行，耗时3.9ms）：

37、步骤24：索引使用情况

输入 SQL：

SELECT indexrelname AS index_name, idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS index_sizeFROM pg_stat_user_indexesWHERE schemaname = 'public'ORDER BY idx_scan DESC;

输出结果（21个索引，耗时2.8ms）：

注意：说明：

注意：刚部署完成，索引使用次数均为0。生产环境运行后此监控可用于发现未使用的索引。

38、步骤25：缓冲区命中率

输入 SQL：

SELECT ROUND( sum(heap_blks_hit)::numeric / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2 ) AS buffer_hit_ratio_pctFROM pg_statio_user_tables;

输出结果（1行，耗时2.7ms）：

注意：说明：

注意：刚创建的表尚无缓冲区读取统计。建议生产环境中此值应保持在 > 95%，低于 90% 需要增加 shared_buffers。

40、步骤26：预热 HNSW 索引到共享缓冲区

输入 SQL：

— 安装 pg_prewarm 扩展CREATE EXTENSION IF NOT EXISTS pg_prewarm;– 预热图片向量 HNSW 索引SELECT pg_prewarm('idx_image_vectors_embedding_cosine') AS pages_prewarmed;– 预热文本向量 HNSW 索引SELECT pg_prewarm('idx_text_vectors_embedding_cosine') AS pages_prewarmed;

输出结果：

注意：说明：

注意：pg_prewarm 将索引页面加载到 shared_buffers，减少首次查询的磁盘 I/O。建议在数据库重启后或大量数据导入后执行。

41、十三、技术架构总结

┌─────────────────────────────────────────────────────┐│ 智能客服应用层 │├──────────┬──────────┬──────────┬────────────────────┤│ 以图搜图 │ FAQ检索 │ 混合检索 │ 相似问题推荐 │├──────────┴──────────┴──────────┴────────────────────┤│ pgvector HNSW 索引层 ││ • image_vectors: 768维 (CLIP ViT-L/14) ││ • text_vectors: 1024维 (BGE-large-zh-v1.5) ││ • chat_messages: 1024维 (对话向量) │├─────────────────────────────────────────────────────┤│ PostgreSQL 15.14 + pgvector 0.8.2 ││ • 余弦距离: <=> 运算符 ││ • HNSW 参数: m=16, ef_search=100 ││ • 辅助索引: B-Tree + GIN (部分索引) │└─────────────────────────────────────────────────────┘