PostgreSQL向量数据生成实战：基于正态分布的高效测试数据构建与AI编程

最近的文章创作中，AI工具扮演了重要角色，这引来了关于“AI味”的讨论。但工具本身并无高下之分，关键在于使用者。一个核心观点是：能够驾驭AI并将其效能最大化的人，必然也是相关领域的深度实践者。这并非取代，而是效率与专业洞察的融合。

一个典型的场景是：在为数据库实践课程准备教学数据时，需要生成一批符合现实世界规律的向量数据。单纯随机生成的数据缺乏“群体聚集性”和“语义相似度”等现实意义。这时，借助AI工具，通过清晰的指令（Prompt）和后续微调，可以快速构建出符合业务逻辑的测试数据。

Prompt示例：

我要在PostgreSQL中创建一个存储16维向量的表，需要生成测试数据，写一个PostgreSQL函数，生成1000个（可变量输入）离散随机的向量，然后再以这1000个向量为中心，生成2000个（可变量输入）离散向量，离中心点距离越近出现概率越高，越远概率越低。

AI生成的解决方案

在 PostgreSQL 中处理向量数据，通常推荐安装 pgvector 插件。为了满足“中心点聚合”和“概率分布”的需求，结合高斯分布（正态分布）来编写函数是科学的做法。

1. 环境准备

首先，确保数据库已安装 pgvector 插件。

CREATE EXTENSION IF NOT EXISTS vector;

-- 创建存储 16 维向量的表
CREATE TABLE IF NOT EXISTS vector_test (
    id SERIAL PRIMARY KEY,
    embedding vector(16),
    is_center BOOLEAN -- 标记是否为中心点
);

2. 核心函数逻辑

利用 PostgreSQL 的内置函数 random()，并采用 Box-Muller 变换 生成符合正态分布的随机数，以实现“离中心点越近概率越高”的效果。

函数：generate_vector_data

CREATE OR REPLACE FUNCTION generate_vector_data(
    center_count INT,     -- 中心点数量 (例如 1000)
    points_per_center INT -- 每个中心点周围生成的点数 (例如 2, 总计 2000)
) RETURNS TEXT AS $$
DECLARE
    center_id INT;
    center_vec FLOAT8[];
    new_vec FLOAT8[];
    std_dev FLOAT8 := 0.1; -- 标准差，值越小点越密集
    i INT; j INT; k INT;
    u1 FLOAT8; u2 FLOAT8; z0 FLOAT8;
BEGIN
    -- 1. 生成原始的离散随机中心点
    FOR i IN 1..center_count LOOP
        center_vec := ARRAY(SELECT random() FROM generate_series(1, 16));
        INSERT INTO vector_test (embedding, is_center)
        VALUES (center_vec::vector, TRUE)
        RETURNING id INTO center_id;

        -- 2. 以该中心点为基准，生成服从正态分布的周边点
        FOR j IN 1..points_per_center LOOP
            new_vec := '{}';
            -- 对 16 个维度分别进行高斯扰动
            FOR k IN 1..16 LOOP
                -- Box-Muller 变换生成标准正态分布随机数
                u1 := random();
                u2 := random();
                z0 := sqrt(-2.0 * ln(u1)) * cos(2.0 * pi() * u2);
                -- 将扰动叠加到中心点的对应维度上
                new_vec := array_append(new_vec, center_vec[k] + (z0 * std_dev));
            END LOOP;

            INSERT INTO vector_test (embedding, is_center)
            VALUES (new_vec::vector, FALSE);
        END LOOP;
    END LOOP;

    RETURN 'Successfully generated ' || (center_count + center_count * points_per_center) || ' vectors.';
END;
$$ LANGUAGE plpgsql;

3. 使用与验证

执行函数生成数据：

-- 生成 1000 个中心点，每个点生成 2 个周边点（共 3000 条数据）
SELECT generate_vector_data(1000, 2);

验证数据分布：

-- 查看中心点与周边点的数量
SELECT is_center, count(*) FROM vector_test GROUP BY is_center;

-- 查询距离某个中心点最近的5个点
SELECT * FROM vector_test
ORDER BY embedding <=> (SELECT embedding FROM vector_test WHERE is_center = true LIMIT 1)
LIMIT 5;

关键点解析

• 概率分布模型：使用正态分布，数值出现在均值（中心点）附近的概率最大，完美符合“近处多、远处少”的需求。
• Box-Muller变换：通过均匀分布随机数生成正态分布随机数的标准算法。
• 控制离散度：通过 std_dev（标准差）参数控制点的“紧凑程度”。值越小，点越贴近中心点；值越大，点越分散。

标准差概念详解

为了更直观地理解，可以用“射击打靶”来比喻：

• 平均值：靶心。
• 标准差：一把“尺子”，衡量子弹偏离靶心的平均距离。数据集波动越大，这把“尺子”越长。
• 68%/95%/99.7%：子弹落在“靶心±1/2/3倍尺子长度”范围内的概率（覆盖率）。

在前面的向量例子中，假设某个维度的平均值是0.5，标准差 std_dev 设为0.1：

• 68%的概率，生成的数值会落在 0.4 到 0.6 之间（0.5 ± 0.1）。
• 95%的概率，数值会落在 0.3 到 0.7 之间（0.5 ± 0.2）。
• 99.7%的概率，数值会落在 0.2 到 0.8 之间（0.5 ± 0.3）。

人工迭代与性能压测

完全依赖AI会失去思考过程，因此需要在此基础上进行手动优化和扩展，例如使维度和标准差可调，并利用 pgbench 进行并行压测，这是数据库与中间件性能测试的常见手段。

1. 增强函数：修改函数，支持自定义维度 (dims) 和标准差 (i_std_dev)。

   CREATE OR REPLACE FUNCTION generate_vector_data(
       center_count INT,
       points_per_center INT,
       dims int,  -- 维度
       i_std_dev float8 default 0.1 -- 标准差
   ) RETURNS TEXT AS $$
   -- ... 函数体逻辑类似，将循环上限改为 dims，使用 i_std_dev ...
   $$ LANGUAGE plpgsql;

2. 并行插入：使用 pgbench 工具并行生成大量数据。

   • 准备脚本 1.sql：

     SELECT generate_vector_data(:center_count, :points_per_center, :dims, :i_std_dev);

   • 执行命令（10个并发，共生成30万条记录）：

     pgbench -M prepared -n -r -f 1.sql -D center_count=300 -D points_per_center=100 -D dims=8 -D i_std_dev=0.1 -c 10 -j 10 -t 1

3. 索引与查询：为向量列创建 HNSW 索引以加速相似性搜索，并测试查询性能。

   -- 并行创建索引优化设置
   set maintenance_work_mem ='1GB';
   set max_parallel_workers=4;
   alter table vector_test set (parallel_workers =4);
   create index on vector_test using hnsw (embedding vector_l2_ops) with (m=16, ef_construction=64);
   
   -- 相似向量查询示例
   SELECT * FROM vector_test
   ORDER BY embedding <-> (SELECT embedding FROM vector_test WHERE id = 1)
   LIMIT 10;

应对更复杂的需求

业务需求往往会变得更复杂。例如，要求生成的数据严格落在 [0,1) 区间内，并且每个中心点自身的“聚集强度”（类内标准差）也服从一个正态分布。这再次体现了人工智能工具在辅助解决复杂逻辑问题时的价值。只需将更精确的需求描述为 Prompt，AI 便能生成符合要求、逻辑严谨的代码。

升级后的 Prompt 与 AI 生成的函数核心逻辑：

需求要点：向量值需截断至 [0,1)；每个中心点的类内标准差服从 N(std_dev_mean, std_dev_sigma) 分布，且必须为正。

CREATE OR REPLACE FUNCTION generate_vector_data(
    center_count INT,
    points_per_center INT,
    dims INT,
    std_dev_mean FLOAT8 DEFAULT 0.1, -- 类内标准差的均值
    std_dev_sigma FLOAT8 DEFAULT 0.01 -- 类内标准差的分布标准差
) RETURNS TEXT AS $$
DECLARE
    local_std_dev FLOAT8; -- 每个中心点自己的类内标准差
    perturbed_val FLOAT8;
    -- ... 其他变量
BEGIN
    FOR i IN 1..center_count LOOP
        -- 生成中心点向量...
        -- 为当前中心点生成其“类内标准差”：N(std_dev_mean, std_dev_sigma)，必须 > 0
        LOOP
            u1 := random(); u2 := random();
            IF u1 = 0 THEN u1 := 1e-10; END IF;
            z_std := sqrt(-2.0 * ln(u1)) * cos(2.0 * pi() * u2);
            local_std_dev := std_dev_mean + z_std * std_dev_sigma;
            EXIT WHEN local_std_dev > 0;
        END LOOP;

        -- 生成聚集点，并对每个维度的扰动值进行截断
        FOR j IN 1..points_per_center LOOP
            FOR k IN 1..dims LOOP
                -- ... 生成正态扰动 z0 ...
                perturbed_val := center_vec[k] + z0 * local_std_dev;
                -- 截断到 [0, 1)，使用 1 - 1e-9 避免等于1
                perturbed_val := GREATEST(0.0, LEAST(1.0 - 1e-9, perturbed_val));
                new_vec := array_append(new_vec, perturbed_val);
            END LOOP;
            -- 插入数据...
        END LOOP;
    END LOOP;
    RETURN 'Successfully generated ...';
END;
$$ LANGUAGE plpgsql;

总结

整个过程清晰地展示了从提出需求、利用AI生成基础方案、到人工迭代优化以满足具体业务细节和性能要求的完整工作流。这不仅是简单的工具使用，更是大数据处理场景下，专业知识与高效工具的结合。真正高效的使用者，懂得如何向AI清晰表达问题，并具备判断和优化其输出结果的能力。