PostgreSQL笔记52: 智能问答系统核心表结构设计与向量检索实践

纲要

• 智能问答系统数据模型概述
  • 知识库表 (knowledge_base)
  • 用户问题表 (qa_questions)
  • 问答历史表 (qa_answers)
• 向量数据存储与检索
  • pgvector 扩展与嵌入向量
  • 向量维度选择与索引
• JSONB 灵活元数据管理
• 表关系与数据闭环
• 适用版本与环境准备
• API 速览
  • pgvector 向量操作函数
  • 相似度检索查询
• Demo 简单示例
  • Node.js + pg 完整可运行示例
• 项目难点与解决方案
• 官方文档与参考链接
• 总结

智能问答系统（RAG 系统）的核心在于将知识库向量化存储，并在用户提问时通过向量相似度检索最相关的知识片段，再交由大语言模型生成最终回答。PostgreSQL 凭借其强大的扩展能力（尤其是 pgvector）和 JSONB 的灵活性，成为此类系统的理想数据底座。

本文基于实际设计经验，深入剖析智能问答系统的三张核心表结构，详细解读字段含义、向量存储策略、索引优化，并提供可运行的 Node.js 示例，帮助读者快速落地一套高效、可追溯的问答数据层。

表结构设计

整个系统围绕“知识存储 – 问题记录 – 回答溯源”三个维度设计，对应的三张表如下：

• knowledge_base：存储经过切分的知识片段及其向量表示。
• qa_questions：记录每次用户提问的问题原文及其向量。
• qa_answers：保存系统生成的回答，并关联所引用的知识片段（用于溯源和反馈）。

这三张表共同形成数据闭环，支持后续的检索优化、效果分析和模型微调。

知识库表（knowledge_base）

该表是整个系统的核心，每一行代表一个最小知识片段（通常是一段话或一个段落）。设计此表时需平衡检索精度与上下文理解能力：片段越细，检索越精准，但过细可能丢失上下文；反之则可能引入噪声。

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE knowledge_base (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,                     -- 知识片段原文
    embedding vector(768) NOT NULL,            -- 向量表示（768维）
    metadata JSONB NOT NULL DEFAULT '{}',      -- 灵活元数据（来源、章节、作者等）
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- 为后续加速检索创建向量索引（建议使用 IVFFlat 或 HNSW）
CREATE INDEX idx_knowledge_base_embedding ON knowledge_base 
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

字段说明：

• content：知识片段的纯文本，供模型生成回答时引用。
• embedding：通过 embedding 模型（如 text-embedding-3-small）生成的浮点数向量，维度通常为 768 或 1536，PostgreSQL 的 vector 类型支持最多 2000 维。
• metadata：JSONB 类型，可存储来源文件名、章节标题、页码、文档分类等任意键值对，方便返回答案时附带引用来源。
• created_at / updated_at：记录时间戳，可用于分析知识的新鲜度。

向量索引：当数据量超过万级时，必须创建索引以加速相似度检索。pgvector 支持两种索引算法：

• IVFFlat：适合百万级数据，构建速度快，需先有数据再进行聚类。
• HNSW：适合更高精度和并发场景，构建较慢但查询更快。

适用版本：PostgreSQL 14+ 配合 pgvector 0.5.0+。若使用 IVFFlat，建议在表中插入一定量数据后再创建索引，以获得更好的聚类效果。

用户问题表（qa_questions）

每次用户提问时，系统将问题文本转换为向量，并存储于此，便于后续分析用户行为模式。

CREATE TABLE qa_questions (
    id BIGSERIAL PRIMARY KEY,
    question TEXT NOT NULL,
    embedding vector(768) NOT NULL,
    asked_at TIMESTAMPTZ DEFAULT now()
);

字段说明：

• question：用户原始提问文本。
• embedding：问题对应的向量，用于与知识库向量进行相似度计算。

问答历史表（qa_answers）

该表记录系统生成的回答、引用的知识片段及用户反馈，是进行质量评估和模型优化的关键数据来源。

CREATE TABLE qa_answers (
    id BIGSERIAL PRIMARY KEY,
    question_id BIGINT NOT NULL REFERENCES qa_questions(id),
    answer TEXT NOT NULL,
    related_docs JSONB NOT NULL,                -- 检索到的相关知识片段（如 id 和 content）
    top_k INT NOT NULL,                         -- 实际召回的数量（通常 3~5）
    response_time_ms INT,                       -- 响应耗时（毫秒）
    recall_rate FLOAT,                          -- 召回率（可用于评估）
    feedback_score INT CHECK (feedback_score BETWEEN 1 AND 5), -- 用户评分（1~5）
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 可选的索引：按问题 ID 和时间查询
CREATE INDEX idx_qa_answers_question_id ON qa_answers(question_id);

字段说明：

• question_id：关联到 qa_questions 表的主键，形成一对一或一对多的问答关系（一个问题可多次生成回答，便于 A/B 测试）。
• answer：系统最终给出的回答文本。
• related_docs：JSONB 数组，存储本次检索命中的知识片段（如 [{id: 1, content: "..."}, ...]），用于溯源和调试。
• top_k：实际返回给模型的知识片段数量。
• response_time_ms：记录耗时，用于性能监控。
• recall_rate：可根据标注数据计算，用于评估检索效果。
• feedback_score：用户满意度打分，为优化提供方向。

表关系与数据闭环

流程说明：

1. 用户提问 → 插入 qa_questions 并生成向量。
2. 使用该向量在 knowledge_base 中执行相似度检索（余弦距离），得到 Top-K 片段。
3. 将这些片段与问题拼装成 prompt 提交给 LLM，生成回答。
4. 将回答、引用的片段、耗时等信息写入 qa_answers。
5. 用户反馈评分存入 feedback_score，形成闭环。

API 速览

本节聚焦于 pgvector 扩展提供的核心向量操作 API，以及基于这些 API 的典型检索查询。

向量类型与操作符

函数/操作符	说明	示例
vector	向量数据类型	'[0.1, 0.2, ...]'::vector
<=>	余弦距离（余弦相似度的补数）	embedding <=> '[0.3, ...]'
<#>	负内积	用于内积相似度
<+>	L2 欧氏距离	距离越小越相似
cosine_distance	余弦距离函数（与 <=> 等价）	cosine_distance(embedding, query_vec)

相似度检索查询

基础查询（无索引加速，适合小数据集）：

SELECT id, content, metadata,
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM knowledge_base
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

使用索引的查询（推荐）：

SELECT id, content, metadata,
       1 - (embedding <=> query_vec) AS similarity
FROM knowledge_base
WHERE embedding <=> query_vec < 0.5   -- 可选阈值过滤
ORDER BY embedding <=> query_vec
LIMIT 5;

索引 ivfflat 或 hnsw 会自动加速 ORDER BY 和 < 比较。

获取向量距离：

SELECT embedding <=> '[0.1, ...]' AS distance
FROM knowledge_base
WHERE id = 1;

Demo 简单示例

以下是一个基于 Node.js 和 pg 驱动实现的完整 RAG 数据层 Demo，演示了建表、插入知识、向量检索及问答记录的完整流程。

运行说明

1. 环境准备

# 安装 PostgreSQL 14+ 并启用 pgvector 扩展
# 使用 Docker 快速启动
docker run -e POSTGRES_PASSWORD=password -p 5432:5432 -d ankane/pgvector

2. 项目初始化

mkdir rag-postgres-demo && cd rag-postgres-demo
npm init -y
npm install pg dotenv

3. 环境变量

创建 .env 文件：

DATABASE_URL=postgresql://postgres:password@localhost:5432/rag_demo

4. 运行 Demo

node index.js

代码说明

// index.ts
import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
});

// 模拟 Embedding 模型输出（实际应调用 OpenAI / 本地模型）
function generateMockEmbedding(dim: number = 768): number[] {
    return Array.from({ length: dim }, () => Math.random() * 2 - 1);
}

async function setupDatabase() {
    const client = await pool.connect();
    try {
        // 1. 启用 pgvector 扩展
        await client.query(`CREATE EXTENSION IF NOT EXISTS vector;`);

        // 2. 创建三张核心表
        await client.query(`
            CREATE TABLE IF NOT EXISTS knowledge_base (
                id BIGSERIAL PRIMARY KEY,
                content TEXT NOT NULL,
                embedding VECTOR(768) NOT NULL,
                metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
        `);

        await client.query(`
            CREATE TABLE IF NOT EXISTS qa_questions (
                id BIGSERIAL PRIMARY KEY,
                question TEXT NOT NULL,
                embedding VECTOR(768) NOT NULL,
                context JSONB DEFAULT '{}'::JSONB,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
        `);

        await client.query(`
            CREATE TABLE IF NOT EXISTS qa_answers (
                id BIGSERIAL PRIMARY KEY,
                question_id BIGINT NOT NULL REFERENCES qa_questions(id) ON DELETE CASCADE,
                answer TEXT NOT NULL,
                referenced_docs JSONB NOT NULL DEFAULT '[]'::JSONB,
                topk INT DEFAULT 5,
                response_time_ms INT,
                recall_rate FLOAT,
                feedback_score INT CHECK (feedback_score >= 1 AND feedback_score <= 5),
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
        `);

        // 3. 创建索引
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_kb_metadata ON knowledge_base USING GIN (metadata);
            CREATE INDEX IF NOT EXISTS idx_kb_embedding_hnsw 
            ON knowledge_base USING HNSW (embedding vector_cosine_ops)
            WITH (m = 16, ef_construction = 64);
        `);

        console.log('✅ 数据库表与索引创建完成');
    } finally {
        client.release();
    }
}

// 插入知识片段
async function insertKnowledge(content: string, metadata: object) {
    const embedding = generateMockEmbedding(768);
    const result = await pool.query(
        `INSERT INTO knowledge_base (content, embedding, metadata)
         VALUES ($1, $2::VECTOR(768), $3::JSONB)
         RETURNING id`,
        [content, JSON.stringify(embedding), JSON.stringify(metadata)]
    );
    return result.rows[0].id;
}

// 向量检索
async function vectorSearch(queryEmbedding: number[], topK: number = 5) {
    const result = await pool.query(
        `SELECT id, content, metadata, 1 - (embedding <=> $1::VECTOR(768)) AS similarity
         FROM knowledge_base
         ORDER BY embedding <=> $1::VECTOR(768)
         LIMIT $2`,
        [JSON.stringify(queryEmbedding), topK]
    );
    return result.rows;
}

// 记录用户提问
async function recordQuestion(question: string) {
    const embedding = generateMockEmbedding(768);
    const result = await pool.query(
        `INSERT INTO qa_questions (question, embedding)
         VALUES ($1, $2::VECTOR(768))
         RETURNING id`,
        [question, JSON.stringify(embedding)]
    );
    return result.rows[0].id;
}

// 记录问答历史
async function recordAnswer(
    questionId: number,
    answer: string,
    referencedDocs: object[],
    topK: number,
    responseTimeMs: number,
    recallRate?: number
) {
    await pool.query(
        `INSERT INTO qa_answers (question_id, answer, referenced_docs, topk, response_time_ms, recall_rate)
         VALUES ($1, $2, $3::JSONB, $4, $5, $6)`,
        [questionId, answer, JSON.stringify(referencedDocs), topK, responseTimeMs, recallRate ?? null]
    );
}

async function main() {
    await setupDatabase();

    // 插入示例知识
    const knowledgeDocs = [
        { content: 'PostgreSQL 是一款先进的开源关系型数据库管理系统，支持 ACID 事务。', 
          metadata: { source: 'docs/intro.md', topic: 'database' } },
        { content: 'pgvector 是 PostgreSQL 的向量相似度搜索扩展，支持 L2、内积和余弦距离。',
          metadata: { source: 'docs/pgvector.md', topic: 'vector' } },
        { content: 'RAG（检索增强生成）结合了信息检索与 LLM 生成能力，可有效减少幻觉。',
          metadata: { source: 'docs/rag.md', topic: 'ai' } },
    ];

    for (const doc of knowledgeDocs) {
        await insertKnowledge(doc.content, doc.metadata);
    }
    console.log(`✅ 已插入 ${knowledgeDocs.length} 条知识片段`);

    // 模拟用户提问
    const userQuestion = '什么是 RAG？';
    const questionEmbedding = generateMockEmbedding(768);

    // 向量检索
    const searchResults = await vectorSearch(questionEmbedding, 3);
    console.log('🔍 检索结果:', searchResults.map(r => ({ id: r.id, similarity: r.similarity })));

    // 记录提问
    const questionId = await recordQuestion(userQuestion);

    // 模拟生成回答
    const mockAnswer = 'RAG（检索增强生成）是一种结合信息检索与 LLM 生成的技术框架...';
    const referencedDocs = searchResults.map(r => ({
        id: r.id,
        content: r.content,
        source: r.metadata?.source || 'unknown',
        similarity: r.similarity,
    }));

    await recordAnswer(questionId, mockAnswer, referencedDocs, 3, 450, 0.92);
    console.log('✅ 问答记录已保存');

    // 查询完整问答链路
    const fullChain = await pool.query(
        `SELECT 
            q.id AS question_id,
            q.question,
            a.answer,
            a.referenced_docs,
            a.response_time_ms,
            a.feedback_score
         FROM qa_questions q
         LEFT JOIN qa_answers a ON q.id = a.question_id
         WHERE q.id = $1`,
        [questionId]
    );
    console.log('📝 完整问答链路:', JSON.stringify(fullChain.rows, null, 2));

    await pool.end();
}

main().catch(console.error);

技术点总结

该 Demo 演示了以下核心技术：

• pgvector 扩展的启用与向量列定义。
• 三张核心表的创建与关联关系。
• 使用 HNSW 索引加速向量相似度检索。
• 模拟向量生成与向量检索（余弦距离）。
• 完整的问答数据闭环：提问 → 检索 → 回答 → 存储溯源。

多语言示例

以下分别提供 Go、Python 和 Java 三种语言实现的完整 Demo，均基于同一套表结构设计（knowledge_base、qa_questions、qa_answers），实现知识插入、向量检索、问答记录等核心功能。每个示例均包含运行说明、代码说明及技术点总结，便于横向对比。

Go 实现

运行说明

1. 环境准备

• Go 1.21+
• PostgreSQL 14+ 且安装 pgvector 扩展
• 推荐使用 Docker 启动 PostgreSQL：docker run -e POSTGRES_PASSWORD=password -p 5432:5432 -d ankane/pgvector

2. 项目初始化

mkdir rag-demo-go && cd rag-demo-go
go mod init rag-demo-go
go get github.com/jackc/pgx/v5

3. 环境变量

创建 .env 文件（或直接使用系统环境变量）：

DATABASE_URL=postgresql://postgres:password@localhost:5432/rag_demo

4. 运行

go run main.go

代码说明

// main.go
package main

import (
    "context"
    "encoding/json"
    "fmt"
    "log"
    "math/rand"
    "os"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

// 模拟 embedding 生成（768维）
func generateMockEmbedding(dim int) []float32 {
    vec := make([]float32, dim)
    for i := range vec {
        vec[i] = rand.Float32()*2 - 1
    }
    return vec
}

// 将 []float32 转为 pgvector 可接受的字符串格式
func vecToString(vec []float32) string {
    b, _ := json.Marshal(vec)
    return string(b)
}

func main() {
    ctx := context.Background()

    dbURL := os.Getenv("DATABASE_URL")
    if dbURL == "" {
        dbURL = "postgresql://postgres:password@localhost:5432/rag_demo"
    }

    config, err := pgxpool.ParseConfig(dbURL)
    if err != nil {
        log.Fatal("Parse config error:", err)
    }
    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        log.Fatal("Connect error:", err)
    }
    defer pool.Close()

    // 1. 启用扩展并建表
    _, err = pool.Exec(ctx, `CREATE EXTENSION IF NOT EXISTS vector;`)
    if err != nil {
        log.Fatal("Create extension error:", err)
    }

    // 建表语句（与 Node.js 保持一致）
    createTables := `
    CREATE TABLE IF NOT EXISTS knowledge_base (
        id BIGSERIAL PRIMARY KEY,
        content TEXT NOT NULL,
        embedding VECTOR(768) NOT NULL,
        metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
        created_at TIMESTAMPTZ DEFAULT NOW(),
        updated_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE TABLE IF NOT EXISTS qa_questions (
        id BIGSERIAL PRIMARY KEY,
        question TEXT NOT NULL,
        embedding VECTOR(768) NOT NULL,
        context JSONB DEFAULT '{}'::JSONB,
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE TABLE IF NOT EXISTS qa_answers (
        id BIGSERIAL PRIMARY KEY,
        question_id BIGINT NOT NULL REFERENCES qa_questions(id) ON DELETE CASCADE,
        answer TEXT NOT NULL,
        referenced_docs JSONB NOT NULL DEFAULT '[]'::JSONB,
        topk INT DEFAULT 5,
        response_time_ms INT,
        recall_rate FLOAT,
        feedback_score INT CHECK (feedback_score >= 1 AND feedback_score <= 5),
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE INDEX IF NOT EXISTS idx_kb_metadata ON knowledge_base USING GIN (metadata);
    CREATE INDEX IF NOT EXISTS idx_kb_embedding_hnsw 
        ON knowledge_base USING HNSW (embedding vector_cosine_ops)
        WITH (m = 16, ef_construction = 64);
    `
    _, err = pool.Exec(ctx, createTables)
    if err != nil {
        log.Fatal("Create tables error:", err)
    }
    fmt.Println("✅ 数据库表与索引创建完成")

    // 2. 插入知识片段
    knowledgeDocs := []struct {
        content  string
        metadata map[string]interface{}
    }{
        {"PostgreSQL 是一款先进的开源关系型数据库管理系统，支持 ACID 事务。",
            map[string]interface{}{"source": "docs/intro.md", "topic": "database"}},
        {"pgvector 是 PostgreSQL 的向量相似度搜索扩展，支持 L2、内积和余弦距离。",
            map[string]interface{}{"source": "docs/pgvector.md", "topic": "vector"}},
        {"RAG（检索增强生成）结合了信息检索与 LLM 生成能力，可有效减少幻觉。",
            map[string]interface{}{"source": "docs/rag.md", "topic": "ai"}},
    }

    for _, doc := range knowledgeDocs {
        emb := generateMockEmbedding(768)
        embStr := vecToString(emb)
        metadataJSON, _ := json.Marshal(doc.metadata)
        _, err := pool.Exec(ctx,
            `INSERT INTO knowledge_base (content, embedding, metadata) VALUES ($1, $2::VECTOR(768), $3::JSONB)`,
            doc.content, embStr, metadataJSON)
        if err != nil {
            log.Fatal("Insert knowledge error:", err)
        }
    }
    fmt.Printf("✅ 已插入 %d 条知识片段\n", len(knowledgeDocs))

    // 3. 模拟用户提问：向量检索 + 记录问答
    userQuestion := "什么是 RAG？"
    queryEmb := generateMockEmbedding(768)
    queryEmbStr := vecToString(queryEmb)

    // 向量检索
    rows, err := pool.Query(ctx,
        `SELECT id, content, metadata, 1 - (embedding <=> $1::VECTOR(768)) AS similarity
         FROM knowledge_base
         ORDER BY embedding <=> $1::VECTOR(768)
         LIMIT 3`,
        queryEmbStr)
    if err != nil {
        log.Fatal("Search error:", err)
    }
    var searchResults []map[string]interface{}
    for rows.Next() {
        var id int64
        var content string
        var metadataJSON []byte
        var similarity float32
        if err := rows.Scan(&id, &content, &metadataJSON, &similarity); err != nil {
            log.Fatal("Scan error:", err)
        }
        var metadata map[string]interface{}
        json.Unmarshal(metadataJSON, &metadata)
        searchResults = append(searchResults, map[string]interface{}{
            "id":         id,
            "content":    content,
            "metadata":   metadata,
            "similarity": similarity,
        })
    }
    rows.Close()
    fmt.Println("🔍 检索结果:", searchResults)

    // 记录问题
    var questionID int64
    err = pool.QueryRow(ctx,
        `INSERT INTO qa_questions (question, embedding) VALUES ($1, $2::VECTOR(768)) RETURNING id`,
        userQuestion, queryEmbStr).Scan(&questionID)
    if err != nil {
        log.Fatal("Insert question error:", err)
    }

    // 模拟回答
    mockAnswer := "RAG（检索增强生成）是一种结合信息检索与 LLM 生成的技术框架..."
    referencedDocs := make([]map[string]interface{}, len(searchResults))
    for i, r := range searchResults {
        referencedDocs[i] = map[string]interface{}{
            "id":         r["id"],
            "content":    r["content"],
            "source":     r["metadata"].(map[string]interface{})["source"],
            "similarity": r["similarity"],
        }
    }
    refDocsJSON, _ := json.Marshal(referencedDocs)

    _, err = pool.Exec(ctx,
        `INSERT INTO qa_answers (question_id, answer, referenced_docs, topk, response_time_ms, recall_rate)
         VALUES ($1, $2, $3::JSONB, $4, $5, $6)`,
        questionID, mockAnswer, refDocsJSON, 3, 450, 0.92)
    if err != nil {
        log.Fatal("Insert answer error:", err)
    }
    fmt.Println("✅ 问答记录已保存")

    // 查询完整链路
    var fullChain struct {
        QuestionID    int64
        Question      string
        Answer        string
        ReferencedDocs json.RawMessage
        ResponseTimeMs int
        FeedbackScore  *int
    }
    err = pool.QueryRow(ctx,
        `SELECT q.id, q.question, a.answer, a.referenced_docs, a.response_time_ms, a.feedback_score
         FROM qa_questions q
         LEFT JOIN qa_answers a ON q.id = a.question_id
         WHERE q.id = $1`,
        questionID).Scan(
        &fullChain.QuestionID, &fullChain.Question, &fullChain.Answer,
        &fullChain.ReferencedDocs, &fullChain.ResponseTimeMs, &fullChain.FeedbackScore,
    )
    if err != nil {
        log.Fatal("Query chain error:", err)
    }
    fmt.Printf("📝 完整问答链路: ID=%d, Question=%s, Answer=%s, ResponseTime=%dms\n",
        fullChain.QuestionID, fullChain.Question, fullChain.Answer, fullChain.ResponseTimeMs)
}

技术点总结

• 使用 pgxpool 连接池管理 PostgreSQL 连接。
• 通过 VECTOR 类型和 JSONB 与 pgx 的集成，支持向量和 JSON 数据的直接绑定。
• 采用 HNSW 索引加速余弦相似度检索。
• 完整实现知识入库、向量检索、问答记录和链路查询。

Python 实现

运行说明

1. 环境准备

• Python 3.9+
• PostgreSQL 14+ 且安装 pgvector 扩展
• 推荐使用 Docker 启动 PostgreSQL：docker run -e POSTGRES_PASSWORD=password -p 5432:5432 -d ankane/pgvector

2. 项目初始化

mkdir rag-demo-py && cd rag-demo-py
python -m venv venv
source venv/bin/activate  # Windows: venv\Scripts\activate
pip install psycopg2-binary python-dotenv

3. 环境变量

创建 .env 文件：

DATABASE_URL=postgresql://postgres:password@localhost:5432/rag_demo

4. 运行

python main.py

代码说明

# main.py
import os
import json
import random
import psycopg2
from psycopg2.extras import Json
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:password@localhost:5432/rag_demo")

def generate_mock_embedding(dim=768):
    return [random.uniform(-1, 1) for _ in range(dim)]

def vec_to_string(vec):
    return json.dumps(vec)

def main():
    conn = psycopg2.connect(DATABASE_URL)
    conn.autocommit = True
    cur = conn.cursor()

    # 1. 启用扩展并建表
    cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
    cur.execute("""
    CREATE TABLE IF NOT EXISTS knowledge_base (
        id BIGSERIAL PRIMARY KEY,
        content TEXT NOT NULL,
        embedding VECTOR(768) NOT NULL,
        metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
        created_at TIMESTAMPTZ DEFAULT NOW(),
        updated_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE TABLE IF NOT EXISTS qa_questions (
        id BIGSERIAL PRIMARY KEY,
        question TEXT NOT NULL,
        embedding VECTOR(768) NOT NULL,
        context JSONB DEFAULT '{}'::JSONB,
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE TABLE IF NOT EXISTS qa_answers (
        id BIGSERIAL PRIMARY KEY,
        question_id BIGINT NOT NULL REFERENCES qa_questions(id) ON DELETE CASCADE,
        answer TEXT NOT NULL,
        referenced_docs JSONB NOT NULL DEFAULT '[]'::JSONB,
        topk INT DEFAULT 5,
        response_time_ms INT,
        recall_rate FLOAT,
        feedback_score INT CHECK (feedback_score >= 1 AND feedback_score <= 5),
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE INDEX IF NOT EXISTS idx_kb_metadata ON knowledge_base USING GIN (metadata);
    CREATE INDEX IF NOT EXISTS idx_kb_embedding_hnsw 
        ON knowledge_base USING HNSW (embedding vector_cosine_ops)
        WITH (m = 16, ef_construction = 64);
    """)
    print("✅ 数据库表与索引创建完成")

    # 2. 插入知识片段
    knowledge_docs = [
        ("PostgreSQL 是一款先进的开源关系型数据库管理系统，支持 ACID 事务。",
         {"source": "docs/intro.md", "topic": "database"}),
        ("pgvector 是 PostgreSQL 的向量相似度搜索扩展，支持 L2、内积和余弦距离。",
         {"source": "docs/pgvector.md", "topic": "vector"}),
        ("RAG（检索增强生成）结合了信息检索与 LLM 生成能力，可有效减少幻觉。",
         {"source": "docs/rag.md", "topic": "ai"}),
    ]
    for content, metadata in knowledge_docs:
        emb = generate_mock_embedding(768)
        emb_str = vec_to_string(emb)
        cur.execute(
            "INSERT INTO knowledge_base (content, embedding, metadata) VALUES (%s, %s::VECTOR(768), %s::JSONB)",
            (content, emb_str, Json(metadata))
        )
    print(f"✅ 已插入 {len(knowledge_docs)} 条知识片段")

    # 3. 模拟用户提问
    user_question = "什么是 RAG？"
    query_emb = generate_mock_embedding(768)
    query_emb_str = vec_to_string(query_emb)

    # 向量检索
    cur.execute(
        """SELECT id, content, metadata, 1 - (embedding <=> %s::VECTOR(768)) AS similarity
           FROM knowledge_base
           ORDER BY embedding <=> %s::VECTOR(768)
           LIMIT 3""",
        (query_emb_str, query_emb_str)
    )
    search_results = []
    for id, content, metadata, similarity in cur.fetchall():
        search_results.append({
            "id": id,
            "content": content,
            "metadata": metadata,
            "similarity": similarity
        })
    print("🔍 检索结果:", search_results)

    # 记录问题
    cur.execute(
        "INSERT INTO qa_questions (question, embedding) VALUES (%s, %s::VECTOR(768)) RETURNING id",
        (user_question, query_emb_str)
    )
    question_id = cur.fetchone()[0]

    # 模拟回答
    mock_answer = "RAG（检索增强生成）是一种结合信息检索与 LLM 生成的技术框架..."
    referenced_docs = []
    for r in search_results:
        referenced_docs.append({
            "id": r["id"],
            "content": r["content"],
            "source": r["metadata"].get("source", "unknown"),
            "similarity": r["similarity"]
        })
    cur.execute(
        """INSERT INTO qa_answers (question_id, answer, referenced_docs, topk, response_time_ms, recall_rate)
           VALUES (%s, %s, %s::JSONB, %s, %s, %s)""",
        (question_id, mock_answer, Json(referenced_docs), 3, 450, 0.92)
    )
    print("✅ 问答记录已保存")

    # 查询完整链路
    cur.execute(
        """SELECT q.id, q.question, a.answer, a.referenced_docs, a.response_time_ms, a.feedback_score
           FROM qa_questions q
           LEFT JOIN qa_answers a ON q.id = a.question_id
           WHERE q.id = %s""",
        (question_id,)
    )
    row = cur.fetchone()
    print(f"📝 完整问答链路: ID={row[0]}, Question={row[1]}, Answer={row[2]}, ResponseTime={row[4]}ms")

    cur.close()
    conn.close()

if __name__ == "__main__":
    main()

技术点总结

• 使用 psycopg2 驱动，通过 Json 适配器处理 JSONB 数据。
• 向量类型 VECTOR 通过字符串传递（json.dumps 序列化）。
• 利用 psycopg2 的参数化查询防止 SQL 注入。
• 完整实现知识入库、向量检索、问答记录和链路查询。

Java 实现

运行说明

1. 环境准备

• JDK 17+
• Maven 3.8+
• PostgreSQL 14+ 且安装 pgvector 扩展
• 推荐使用 Docker 启动 PostgreSQL：docker run -e POSTGRES_PASSWORD=password -p 5432:5432 -d ankane/pgvector

2. 项目初始化

mkdir rag-demo-java && cd rag-demo-java
mvn archetype:generate -DgroupId=com.example -DartifactId=rag-demo -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
cd rag-demo

修改 pom.xml 添加依赖：

<dependencies>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.7.2</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.16.1</version>
    </dependency>
    <dependency>
        <groupId>io.github.cdimascio</groupId>
        <artifactId>dotenv-java</artifactId>
        <version>3.0.0</version>
    </dependency>
</dependencies>

3. 环境变量

在项目根目录创建 .env 文件：

DATABASE_URL=postgresql://postgres:password@localhost:5432/rag_demo

4. 运行

mvn compile exec:java -Dexec.mainClass="com.example.App"

代码说明

// src/main/java/com/example/App.java
package com.example;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import io.github.cdimascio.dotenv.Dotenv;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;

public class App {
    private static final int DIM = 768;
    private static final Random random = new Random();
    private static final ObjectMapper mapper = new ObjectMapper();

    private static float[] generateMockEmbedding() {
        float[] vec = new float[DIM];
        for (int i = 0; i < DIM; i++) {
            vec[i] = random.nextFloat() * 2 - 1;
        }
        return vec;
    }

    private static String vecToString(float[] vec) throws Exception {
        return mapper.writeValueAsString(vec);
    }

    public static void main(String[] args) throws Exception {
        Dotenv dotenv = Dotenv.load();
        String dbUrl = dotenv.get("DATABASE_URL");
        if (dbUrl == null) dbUrl = "postgresql://postgres:password@localhost:5432/rag_demo";

        try (Connection conn = DriverManager.getConnection(dbUrl);
             Statement stmt = conn.createStatement()) {

            // 1. 启用扩展并建表
            stmt.execute("CREATE EXTENSION IF NOT EXISTS vector;");
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS knowledge_base (
                    id BIGSERIAL PRIMARY KEY,
                    content TEXT NOT NULL,
                    embedding VECTOR(768) NOT NULL,
                    metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
                    created_at TIMESTAMPTZ DEFAULT NOW(),
                    updated_at TIMESTAMPTZ DEFAULT NOW()
                );
                CREATE TABLE IF NOT EXISTS qa_questions (
                    id BIGSERIAL PRIMARY KEY,
                    question TEXT NOT NULL,
                    embedding VECTOR(768) NOT NULL,
                    context JSONB DEFAULT '{}'::JSONB,
                    created_at TIMESTAMPTZ DEFAULT NOW()
                );
                CREATE TABLE IF NOT EXISTS qa_answers (
                    id BIGSERIAL PRIMARY KEY,
                    question_id BIGINT NOT NULL REFERENCES qa_questions(id) ON DELETE CASCADE,
                    answer TEXT NOT NULL,
                    referenced_docs JSONB NOT NULL DEFAULT '[]'::JSONB,
                    topk INT DEFAULT 5,
                    response_time_ms INT,
                    recall_rate FLOAT,
                    feedback_score INT CHECK (feedback_score >= 1 AND feedback_score <= 5),
                    created_at TIMESTAMPTZ DEFAULT NOW()
                );
                CREATE INDEX IF NOT EXISTS idx_kb_metadata ON knowledge_base USING GIN (metadata);
                CREATE INDEX IF NOT EXISTS idx_kb_embedding_hnsw 
                    ON knowledge_base USING HNSW (embedding vector_cosine_ops)
                    WITH (m = 16, ef_construction = 64);
            """);
            System.out.println("✅ 数据库表与索引创建完成");

            // 2. 插入知识片段
            List<Map.Entry<String, Map<String, String>>> knowledgeDocs = List.of(
                    Map.entry("PostgreSQL 是一款先进的开源关系型数据库管理系统，支持 ACID 事务。",
                            Map.of("source", "docs/intro.md", "topic", "database")),
                    Map.entry("pgvector 是 PostgreSQL 的向量相似度搜索扩展，支持 L2、内积和余弦距离。",
                            Map.of("source", "docs/pgvector.md", "topic", "vector")),
                    Map.entry("RAG（检索增强生成）结合了信息检索与 LLM 生成能力，可有效减少幻觉。",
                            Map.of("source", "docs/rag.md", "topic", "ai"))
            );

            for (var doc : knowledgeDocs) {
                float[] emb = generateMockEmbedding();
                String embStr = vecToString(emb);
                String metadataJson = mapper.writeValueAsString(doc.getValue());
                try (PreparedStatement ps = conn.prepareStatement(
                        "INSERT INTO knowledge_base (content, embedding, metadata) VALUES (?, ?::VECTOR(768), ?::JSONB)")) {
                    ps.setString(1, doc.getKey());
                    ps.setString(2, embStr);
                    ps.setString(3, metadataJson);
                    ps.executeUpdate();
                }
            }
            System.out.println("✅ 已插入 " + knowledgeDocs.size() + " 条知识片段");

            // 3. 模拟用户提问
            String userQuestion = "什么是 RAG？";
            float[] queryEmb = generateMockEmbedding();
            String queryEmbStr = vecToString(queryEmb);

            // 向量检索
            List<ObjectNode> searchResults = new ArrayList<>();
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT id, content, metadata, 1 - (embedding <=> ?::VECTOR(768)) AS similarity " +
                    "FROM knowledge_base ORDER BY embedding <=> ?::VECTOR(768) LIMIT 3")) {
                ps.setString(1, queryEmbStr);
                ps.setString(2, queryEmbStr);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    ObjectNode node = mapper.createObjectNode();
                    node.put("id", rs.getLong("id"));
                    node.put("content", rs.getString("content"));
                    node.put("similarity", rs.getFloat("similarity"));
                    // 解析 metadata JSON
                    String meta = rs.getString("metadata");
                    node.set("metadata", mapper.readTree(meta));
                    searchResults.add(node);
                }
            }
            System.out.println("🔍 检索结果: " + mapper.writeValueAsString(searchResults));

            // 记录问题
            long questionId;
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO qa_questions (question, embedding) VALUES (?, ?::VECTOR(768)) RETURNING id")) {
                ps.setString(1, userQuestion);
                ps.setString(2, queryEmbStr);
                ResultSet rs = ps.executeQuery();
                rs.next();
                questionId = rs.getLong(1);
            }

            // 模拟回答
            String mockAnswer = "RAG（检索增强生成）是一种结合信息检索与 LLM 生成的技术框架...";
            ArrayNode refDocs = mapper.createArrayNode();
            for (ObjectNode r : searchResults) {
                ObjectNode doc = mapper.createObjectNode();
                doc.put("id", r.get("id").asLong());
                doc.put("content", r.get("content").asText());
                doc.put("source", r.get("metadata").get("source").asText("unknown"));
                doc.put("similarity", r.get("similarity").asDouble());
                refDocs.add(doc);
            }
            String refDocsJson = mapper.writeValueAsString(refDocs);

            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO qa_answers (question_id, answer, referenced_docs, topk, response_time_ms, recall_rate) " +
                    "VALUES (?, ?, ?::JSONB, ?, ?, ?)")) {
                ps.setLong(1, questionId);
                ps.setString(2, mockAnswer);
                ps.setString(3, refDocsJson);
                ps.setInt(4, 3);
                ps.setInt(5, 450);
                ps.setDouble(6, 0.92);
                ps.executeUpdate();
            }
            System.out.println("✅ 问答记录已保存");

            // 查询完整链路
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT q.id, q.question, a.answer, a.referenced_docs, a.response_time_ms, a.feedback_score " +
                    "FROM qa_questions q LEFT JOIN qa_answers a ON q.id = a.question_id WHERE q.id = ?")) {
                ps.setLong(1, questionId);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    System.out.printf("📝 完整问答链路: ID=%d, Question=%s, Answer=%s, ResponseTime=%dms%n",
                            rs.getLong(1), rs.getString(2), rs.getString(3), rs.getInt(5));
                }
            }
        }
    }
}

技术点总结

• 使用 JDBC 驱动 postgresql 配合 VECTOR 类型字符串传递。
• 借助 Jackson 处理 JSONB 的序列化与反序列化。
• 采用 try-with-resources 确保连接和语句正确释放。
• 完整实现知识入库、向量检索、问答记录和链路查询。

多语言对比

维度	Node.js	Go	Python	Java
驱动/ORM	pg	pgx/v5	psycopg2	postgresql JDBC
向量传递方式	JSON.stringify 后转 ::VECTOR	json.Marshal 后转 ::VECTOR	json.dumps 后转 ::VECTOR	ObjectMapper.writeValueAsString 后转 ::VECTOR
JSONB 处理	原生对象 + JSON.stringify	json.Marshal + JSONB 强制转换	psycopg2.extras.Json	ObjectMapper + ::JSONB
连接管理	Pool 连接池	pgxpool 连接池	普通 Connection（可搭配 psycopg2.pool）	DriverManager（可搭配 HikariCP）
异步支持	原生 async/await	原生 goroutine + context	同步（可使用 asyncio + asyncpg）	同步（可使用 CompletableFuture）
错误处理	try/catch	if err != nil	try/except	try/catch
依赖管理	npm	go mod	pip	Maven
代码简洁性	中等（异步直观）	中等（显式错误处理）	简洁（动态类型）	较冗长（类型声明 + 序列化）
典型应用场景	快速原型、中小规模	高并发、微服务	数据分析、快速开发	企业级应用、大型团队

各语言实现均遵循相同的表结构和业务逻辑，开发者可根据项目技术栈和性能要求灵活选择。

项目难点与解决方案

核心难点

• 如何在海量知识片段中快速检索出与用户问题最相关的 Top-K 片段。
• 如何兼顾元数据的灵活性与查询性能。
• 如何设计表结构以便于后续的问答效果分析和模型优化。

解决方案

• 采用 pgvector 的 IVFFlat 或 HNSW 索引，将向量检索时间复杂度从 O(n) 降至 O(log n) 级别。
• 使用 JSONB 存储元数据，并结合 GIN 索引实现高效过滤。
• 通过 qa_answers 表记录 recall_rate、feedback_score 和 response_time_ms，形成可量化的评估闭环。

广度

• 涵盖知识入库、向量检索、问答记录、用户反馈全链路。
• 兼容多种向量索引算法，适应不同数据规模。

深度

• 深入探讨向量维度的选择依据（模型输出维度与索引支持上限）。
• 提供完整的 Node.js 示例，覆盖从建表到查询的全流程。

复杂度

• 涉及数据库扩展、向量索引、JSONB 查询优化，属于中等偏上的复杂度，适合有一定 PostgreSQL 基础的开发者。

官方文档

• PostgreSQL 官方文档: https://www.postgresql.org/docs/
• pgvector 官方文档: https://github.com/pgvector/pgvector
• PostgreSQL JSONB 索引: https://www.postgresql.org/docs/current/datatype-json.html

参考链接

• PostgreSQL 维基: https://wiki.postgresql.org/
• pgvector 相似度搜索指南: https://github.com/pgvector/pgvector#indexing
• RAG 系统最佳实践: https://www.pgcon.org/2024/schedule/

总结

本文围绕 RAG 智能问答系统的数据层设计，系统阐述了基于 PostgreSQL 的三张核心表结构及其向量检索能力。通过合理使用 pgvector 扩展和 JSONB 数据类型，既实现了高效的知识检索，又保留了元数据的灵活性。

表结构设计中，knowledge_base 作为知识底座存储向量片段，qa_questions 记录用户问题，qa_answers 保存回答及溯源信息，三者共同构成了完整的数据闭环。同时，通过示例代码展示了从建表、插入、检索到记录的全流程，帮助读者快速上手实践。