大模型应用必备 SQL：2 小时学会「仅需用的」最小语法子集

-- 【必用1】对话历史表（仅保留业务必填字段，大模型场景无需冗余字段）
CREATE TABLE llm_chat_history (
    id SERIAL PRIMARY KEY,       -- 自增ID：PG 10+ 推荐用「GENERATED ALWAYS AS IDENTITY PRIMARY KEY」（更符合SQL标准）
    user_id VARCHAR(64) NOT NULL,-- 用户标识（建议用UUID，避免重复）
    prompt TEXT NOT NULL,        -- 用户提问（用TEXT存长文本，VARCHAR有长度限制）
    answer TEXT NOT NULL,        -- 大模型回答
    model_name VARCHAR(50) NOT NULL, -- 调用模型（如gpt-3.5-turbo/llama3-8b）
    create_time TIMESTAMP WITH TIME ZONE DEFAULT NOW()  -- UTC时间（分布式场景必用，避免时区偏移）
);

-- 【必用2】RAG文档表（pgvector 0.5.0+ 版本，含向量字段 | 需先安装pgvector插件）
CREATE EXTENSION IF NOT EXISTS vector; -- 自动安装pgvector（PG 11+ 支持）
CREATE TABLE rag_documents (
    id SERIAL PRIMARY KEY,
    doc_title VARCHAR(255) NOT NULL,  -- 文档标题
    doc_content TEXT NOT NULL,        -- 文档内容（分片后存储，单条≤1000字，提升检索效率）
    doc_meta JSONB,                   -- 动态元数据（如{"type":"技术文档","year":"2024"}，PG10+ 原生支持高效查询）
    embedding VECTOR(1536) NOT NULL   -- 向量维度：适配OpenAI/智谱等主流模型，其他模型需同步修改维度
);

-- 【业务：加载当前用户最近5条对话，拼接为大模型上下文】
SELECT prompt, answer  -- 仅查询需要的字段，减少网络传输
FROM llm_chat_history 
WHERE user_id = 'user_001-72d3-4f90-960e-8422c64663aa'  -- 用户UUID过滤
ORDER BY create_time DESC   -- 按时间倒序（最新对话在前）
LIMIT 5;                    -- 限制条数（适配大模型上下文窗口上限）

-- 【业务：2024年发布的技术文档中，与「vLLM推理加速」最相似的5篇】
-- 注意：替换'[0.1234, 0.5678, ...]'为实际生成的1536维embedding向量数组
-- pgvector 0.5.0+ 特性：<=#> 为**余弦距离**运算符，1-距离=相似度
SELECT 
    doc_title, doc_content,
    1 - (embedding <=#> '[0.1234, 0.5678, 0.9012, ...]') AS similarity  -- 余弦相似度计算
FROM rag_documents
WHERE doc_meta @> '{"type":"技术文档"}'  -- JSONB包含过滤：仅匹配技术文档
  AND doc_meta ->> 'year' = '2024'        -- JSONB取字符串值过滤：2024年发布
ORDER BY similarity DESC                   -- 按相似度降序
LIMIT 5;                                   -- 限制返回条数（适配大模型上下文）

-- 【业务：查询2024-06-01至今，gpt-3.5-turbo模型的所有调用记录】
SELECT user_id, model_name, create_time
FROM llm_chat_history
WHERE model_name = 'gpt-3.5-turbo'
  AND create_time >= '2024-06-01T00:00:00+00'::TIMESTAMP WITH TIME ZONE;  -- 统一UTC时间格式

-- 为对话历史表的核心查询条件建立复合索引（user_id+create_time DESC）
CREATE INDEX idx_chat_user_time ON llm_chat_history(user_id, create_time DESC);

-- 为RAG文档表的JSONB元数据建立GIN索引（支持高效的包含/取字段查询）
CREATE INDEX idx_doc_meta ON rag_documents USING GIN(doc_meta);

-- 为embedding字段建立IVFFlat近似索引（pgvector官方推荐，召回率≈95%，速度比精确索引快100+倍）
-- 核心参数：nlist=1000（索引分区数，推荐值=数据量/1000）
CREATE INDEX idx_doc_embedding ON rag_documents USING ivfflat(embedding vector_cosine_ops) WITH (nlist = 1000);
ANALYZE rag_documents;  -- 必须执行：让PostgreSQL优化器识别数据分布，避免全表扫描

-- Step 1: 创建大模型服务专用账号
CREATE ROLE llm_service WITH LOGIN PASSWORD 'llm_2024_pwd_secure';  -- 建议用强密码

-- Step 2: 仅授权查询权限（最小权限原则）
GRANT SELECT ON llm_chat_history TO llm_service;
GRANT SELECT ON rag_documents TO llm_service;

-- Step 3: 撤销危险权限（强化合规，默认无但需明确禁用）
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM llm_service;

-- 环境要求：PostgreSQL 11+，已安装pgvector插件权限
-- Step 1: 安装/启用pgvector插件
CREATE EXTENSION IF NOT EXISTS vector;

-- Step 2: 创建2张核心表
CREATE TABLE llm_chat_history (id SERIAL PRIMARY KEY, user_id VARCHAR(64) NOT NULL, prompt TEXT NOT NULL, answer TEXT NOT NULL, model_name VARCHAR(50) NOT NULL, create_time TIMESTAMP WITH TIME ZONE DEFAULT NOW());
CREATE TABLE rag_documents (id SERIAL PRIMARY KEY, doc_title VARCHAR(255) NOT NULL, doc_content TEXT NOT NULL, doc_meta JSONB, embedding VECTOR(1536) NOT NULL);

-- Step 3: 创建必用索引
CREATE INDEX idx_chat_user_time ON llm_chat_history(user_id, create_time DESC);
CREATE INDEX idx_doc_meta ON rag_documents USING GIN(doc_meta);
CREATE INDEX idx_doc_embedding ON rag_documents USING ivfflat(embedding vector_cosine_ops) WITH (nlist = 1000);
ANALYZE rag_documents;  -- 必须添加：优化IVFFlat索引性能

-- Step 4: 创建服务账号并授权最小权限
CREATE ROLE llm_service WITH LOGIN PASSWORD 'llm_2024_pwd_secure';
GRANT SELECT ON llm_chat_history TO llm_service;
GRANT SELECT ON rag_documents TO llm_service;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM llm_service;

-- 脚本结束：已完成生产级大模型存储初始化