最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
PostgreSQL大规模随机数据生成完整指南
时间：2026-07-26 09:29:49 编辑：袖梨 来源：一聚教程网
一、核心工具：generate_series + random 函数家族
PostgreSQL 自带强大的数据生成能力，无需额外工具就能生成上亿行测试数据。
基础随机函数清单
| 函数 | 返回值 | 示例 |
|---|---|---|
random() | 0~1 之间的 double | 0.7234561 |
random() * (max - min) + min | 任意范围 double | random() * 100 |
(random() * 100)::INT | 整数 | 73 |
floor(random() * N)::INT | 0 ~ N-1 整数 | floor(random()*10)::INT |
gen_random_uuid() | UUID（PG 13+） | 7c3a... |
md5(random()::text) | 32 位 MD5 字符串 | 'a3f9...' |
now() - (random() * interval '365 days') | 随机日期 | 一年内随机时间 |
chr(65 + (random()*25)::int) | 随机字符 A~Z | 'M' |
二、generate_series：行数生成器
基础用法
-- 生成 1~10 的整数SELECT * FROM generate_series(1, 10);-- 生成 1~100 步长 5SELECT * FROM generate_series(1, 100, 5);-- 生成日期序列SELECT generate_series( '2026-01-01'::date, '2026-12-31'::date, '1 day'::interval);-- 生成时间戳序列SELECT generate_series( '2026-01-01 00:00:00'::timestamp, '2026-01-02 00:00:00'::timestamp, '1 hour'::interval);
三、典型场景：生成各种测试数据
场景 1：生成 100 万行用户数据
-- 创建表CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), age INT, gender CHAR(1), city VARCHAR(20), salary NUMERIC(10,2), register_at TIMESTAMP, is_active BOOLEAN);-- 一次性生成 100 万行（耗时约 10 秒）INSERT INTO users (username, email, age, gender, city, salary, register_at, is_active)SELECT 'user_' || i AS username, 'user_' || i || '@example.com' AS email, 18 + (random() * 50)::INT AS age, CASE WHEN random() < 0.5 THEN 'M' ELSE 'F' END AS gender, (ARRAY['北京','上海','广州','深圳','杭州','成都','武汉','西安']) [1 + (random() * 7)::INT] AS city, (3000 + random() * 47000)::NUMERIC(10,2) AS salary, NOW() - (random() * interval '1095 days') AS register_at, random() < 0.8 AS is_activeFROM generate_series(1, 1000000) i;
场景 2：生成订单数据（含外键关联）
CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT, order_no VARCHAR(20), amount NUMERIC(10,2), status VARCHAR(20), created_at TIMESTAMP);-- 生成 500 万订单，关联到已有的 100 万用户INSERT INTO orders (user_id, order_no, amount, status, created_at)SELECT 1 + (random() * 999999)::BIGINT AS user_id, 'ORD' || lpad(i::text, 12, '0') AS order_no, (random() * 9999 + 1)::NUMERIC(10,2) AS amount, (ARRAY['pending','paid','shipped','delivered','cancelled']) [1 + (random() * 4)::INT] AS status, NOW() - (random() * interval '365 days') AS created_atFROM generate_series(1, 5000000) i;
场景 3：生成中文姓名（仿真数据）
-- 创建一个临时数据池WITH first_names AS ( SELECT unnest(ARRAY['张','王','李','赵','刘','陈','杨','黄', '周','吴','徐','孙','胡','朱','高','林']) AS name),last_names AS ( SELECT unnest(ARRAY['伟','芳','娜','秀英','敏','静','丽','强', '磊','军','洋','勇','艳','杰','娟','涛']) AS name)INSERT INTO users (username)SELECT (SELECT name FROM first_names ORDER BY random() LIMIT 1) || (SELECT name FROM last_names ORDER BY random() LIMIT 1)FROM generate_series(1, 100000);
这种写法每次子查询都要排序，百万级以上慢。下面有更快方案。
改进版（百万级也快）
INSERT INTO users (username)SELECT (ARRAY['张','王','李','赵','刘','陈','杨','黄', '周','吴','徐','孙','胡','朱','高','林']) [1 + (random() * 15)::INT] || (ARRAY['伟','芳','娜','秀英','敏','静','丽','强', '磊','军','洋','勇','艳','杰','娟','涛']) [1 + (random() * 15)::INT]FROM generate_series(1, 1000000);
场景 4：生成 IP 地址
SELECT floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int AS ip_addressFROM generate_series(1, 100);-- 生成 inet 类型SELECT ( floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int)::inet;
场景 5：生成 JSON 数据
CREATE TABLE events ( id SERIAL PRIMARY KEY, payload JSONB, created_at TIMESTAMP);INSERT INTO events (payload, created_at)SELECT jsonb_build_object( 'event_type', (ARRAY['click','view','purchase','share'])[1+(random()*3)::INT], 'user_id', (random() * 1000000)::BIGINT, 'page', '/page/' || (random()*100)::INT, 'duration_ms', (random() * 10000)::INT, 'tags', ARRAY[ (ARRAY['hot','new','sale','featured'])[1+(random()*3)::INT], (ARRAY['mobile','web','app'])[1+(random()*2)::INT] ] ) AS payload, NOW() - (random() * interval '30 days') AS created_atFROM generate_series(1, 1000000);
场景 6：生成关联表（一对多）
-- 1 个用户 5~20 个订单CREATE TABLE orders2 ( id BIGSERIAL PRIMARY KEY, user_id BIGINT, amount NUMERIC(10,2));INSERT INTO orders2 (user_id, amount)SELECT u.id, (random() * 1000)::NUMERIC(10,2)FROM users uCROSS JOIN LATERAL generate_series(1, 5 + (random() * 15)::INT);
场景 7：生成正态分布数据（更真实）
random() 是均匀分布，但真实业务数据通常是正态分布。
-- Box-Muller 变换生成正态分布CREATE OR REPLACE FUNCTION normal_random(mean NUMERIC, stddev NUMERIC)RETURNS NUMERIC AS $$SELECT mean + stddev * sqrt(-2 * ln(random())) * cos(2 * pi() * random());$$ LANGUAGE SQL;-- 生成均值 5000、标准差 1500 的工资SELECT normal_random(5000, 1500)::NUMERIC(10,2)FROM generate_series(1, 1000);
四、性能优化技巧（亿级数据生成）
优化 1：批量插入 + 关闭日志
-- 1. 设置会话参数（仅本会话生效）SET synchronous_commit TO OFF; -- 关闭同步提交SET maintenance_work_mem TO '2GB'; -- 增大维护内存SET wal_level TO minimal; -- 需要重启,谨慎使用-- 2. 用 UNLOGGED 表（绕过 WAL，速度提升 3-5 倍）CREATE UNLOGGED TABLE temp_users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(50));-- 3. 删除索引,生成完再重建DROP INDEX IF EXISTS idx_users_email;-- 插入数据 ...CREATE INDEX idx_users_email ON users(email);
优化 2：分批插入（避免事务过大）
-- 一次性插 1 亿可能爆 WAL，分批插入DO $$DECLARE batch_size INT := 1000000; -- 每批 100 万 total INT := 100; -- 总共 100 批 = 1 亿BEGIN FOR i IN 1..total LOOP INSERT INTO users (username, age) SELECT 'user_' || ((i-1)*batch_size + g), 18 + (random() * 50)::INT FROM generate_series(1, batch_size) g; COMMIT; RAISE NOTICE '完成第 % 批,共 % 行', i, i * batch_size; END LOOP;END $$;
优化 3：使用 COPY 替代 INSERT（最快）
-- 先生成 CSV 文件copy ( SELECT i AS id, 'user_' || i AS username, 18 + (random() * 50)::INT AS age FROM generate_series(1, 10000000) i) TO '/tmp/users.csv' WITH CSV;-- 再用 COPY 导入（速度比 INSERT 快 10 倍以上）copy users(id, username, age) FROM '/tmp/users.csv' WITH CSV;
优化 4：并行生成（最快）
利用多个会话并行插入到不同的分区：
# 4 个会话并行for i in {0..3}; do psql -c "INSERT INTO users SELECT ... FROM generate_series($((i*2500000+1)), $(((i+1)*2500000)))" &donewait五、性能对比实测
生成 1000 万行简单用户数据的耗时：
| 方法 | 耗时 | 备注 |
|---|---|---|
| 普通 INSERT + generate_series | ~120 秒 | 基准 |
| UNLOGGED 表 + INSERT | ~40 秒 | 3 倍提速 |
| 删除索引 + INSERT + 重建 | ~70 秒 | 索引很多时显著 |
| COPY FROM CSV | ~25 秒 | 5 倍提速 |
| 4 会话并行 INSERT | ~35 秒 | 受限 IO |
| COPY FROM PROGRAM + 并行 | ~10 秒 | 最快 |
六、第三方工具推荐
1. pgbench（PG 自带）
经典基准测试工具，自带 TPC-B 数据生成。
# 创建并初始化测试数据pgbench -i -s 100 mydb # scale=100 → 1000万行 pgbench_accounts# scale 含义# scale=1 → 10 万行# scale=100 → 1000 万行# scale=1000 → 1 亿行# 自定义脚本压测pgbench -c 50 -j 4 -T 60 -f my_script.sql mydb
2. Faker（Python 第三方）
# 安装: pip install faker psycopg2-binaryfrom faker import Fakerimport psycopg2fake = Faker('zh_CN')conn = psycopg2.connect("dbname=test user=postgres")cur = conn.cursor()# 生成真实感的中文姓名、地址、电话等data = [ (fake.name(), fake.email(), fake.address(), fake.phone_number(), fake.date_of_birth()) for _ in range(100000)]# 批量插入cur.executemany( "INSERT INTO users(name, email, address, phone, birthday) VALUES (%s,%s,%s,%s,%s)", data)conn.commit()3. pgloader（结构化导入）
# 从 MySQL/SQLite/CSV 等导入pgloader csv://data.csv pgsql://user@host/db
4. mockaroo + COPY
mockaroo.com 在线生成各种逼真数据，下载 CSV 后用 COPY 导入。
七、实战完整案例：电商系统测试数据
-- ============== 1. 创建表结构 ==============CREATE UNLOGGED TABLE customers ( id BIGSERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), register_at TIMESTAMP);CREATE UNLOGGED TABLE products ( id BIGSERIAL PRIMARY KEY, name VARCHAR(100), category VARCHAR(50), price NUMERIC(10,2), stock INT);CREATE UNLOGGED TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT, total NUMERIC(10,2), status VARCHAR(20), created_at TIMESTAMP);CREATE UNLOGGED TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT, product_id BIGINT, quantity INT, price NUMERIC(10,2));-- ============== 2. 生成 100 万客户 ==============INSERT INTO customers (name, email, phone, register_at)SELECT (ARRAY['张','王','李','赵','刘'])[1+(random()*4)::INT] || (ARRAY['伟','芳','娜','磊','静'])[1+(random()*4)::INT], 'cust_' || i || '@test.com', '13' || lpad((random() * 999999999)::BIGINT::text, 9, '0'), NOW() - (random() * interval '1095 days')FROM generate_series(1, 1000000) i;-- ============== 3. 生成 1 万商品 ==============INSERT INTO products (name, category, price, stock)SELECT '商品_' || i, (ARRAY['服饰','电子','食品','家居','美妆','图书','运动','母婴']) [1+(random()*7)::INT], (random() * 9999 + 10)::NUMERIC(10,2), (random() * 1000)::INTFROM generate_series(1, 10000) i;-- ============== 4. 生成 1000 万订单 ==============INSERT INTO orders (customer_id, total, status, created_at)SELECT 1 + (random() * 999999)::BIGINT, (random() * 5000)::NUMERIC(10,2), (ARRAY['pending','paid','shipped','delivered','cancelled']) [1+(random()*4)::INT], NOW() - (random() * interval '365 days')FROM generate_series(1, 10000000);-- ============== 5. 生成订单明细（每订单 1-5 件）==============INSERT INTO order_items (order_id, product_id, quantity, price)SELECT o.id, 1 + (random() * 9999)::BIGINT, 1 + (random() * 4)::INT, (random() * 1000)::NUMERIC(10,2)FROM orders oCROSS JOIN LATERAL generate_series(1, 1 + (random() * 4)::INT);-- ============== 6. 转回 LOGGED 并建索引 ==============ALTER TABLE customers SET LOGGED;ALTER TABLE products SET LOGGED;ALTER TABLE orders SET LOGGED;ALTER TABLE order_items SET LOGGED;CREATE INDEX idx_customers_email ON customers(email);CREATE INDEX idx_orders_customer ON orders(customer_id);CREATE INDEX idx_orders_created ON orders(created_at);CREATE INDEX idx_oi_order ON order_items(order_id);CREATE INDEX idx_oi_product ON order_items(product_id);-- ============== 7. 收集统计信息 ==============ANALYZE customers;ANALYZE products;ANALYZE orders;ANALYZE order_items;
八、常用代码片段速查
-- 随机整数 1~100(random() * 99 + 1)::INTfloor(random() * 100)::INT + 1-- 随机小数（保留 2 位）(random() * 100)::NUMERIC(10,2)-- 随机布尔(50% 概率)random() < 0.5-- 随机布尔(80% 概率为 true)random() < 0.8-- 随机字符串(长度 10)substr(md5(random()::text), 1, 10)-- 随机 UUID (PG 13+)gen_random_uuid()-- 随机日期(过去 1 年)NOW() - (random() * interval '365 days')-- 随机日期(未来 30 天)NOW() + (random() * interval '30 days')-- 数组随机取一个(ARRAY['A','B','C','D'])[1 + (random() * 3)::INT]-- 加权随机(70% A, 20% B, 10% C)CASE WHEN random() < 0.7 THEN 'A' WHEN random() < 0.9 THEN 'B' ELSE 'C'END-- NULL 概率（10% 为 NULL）CASE WHEN random() < 0.1 THEN NULL ELSE 'value' END-- 随机金额（指数分布,模拟真实消费）(-100 * ln(1 - random()))::NUMERIC(10,2)-- 随机手机号（中国）'1' || (ARRAY['3','5','7','8','9'])[1 + (random() * 4)::INT] ||lpad((random() * 999999999)::BIGINT::text, 9, '0')-- 随机邮箱substr(md5(random()::text), 1, 8) || '@' ||(ARRAY['gmail.com','qq.com','163.com','outlook.com'])[1+(random()*3)::INT]-- 随机 IPv4(floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int || '.' || floor(random()*256)::int)::inet
九、生成数据的注意事项
1. 控制随机种子（可重复测试）
SELECT setseed(0.42); -- 固定种子,后续 random() 结果可重复SELECT random() FROM generate_series(1, 5);
2. 大数据量生成时的内存问题
-- ❌ 一次性生成 1 亿行可能 OOMINSERT INTO t SELECT ... FROM generate_series(1, 100000000);-- ✅ 分批DO $$ ... LOOP 中分批生成 ... END $$;
3. 索引建议
生成数据前删索引，生成完再建：
- 千万级数据时，不删索引插入会慢 3-10 倍
- 主键索引可以保留（用 SERIAL 自增）
4. 序列重置
-- 如果用 BIGSERIAL,生成数据后需要重置序列SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));5. 避免触发器影响
-- 临时禁用触发器ALTER TABLE users DISABLE TRIGGER ALL;-- 插入数据 ...ALTER TABLE users ENABLE TRIGGER ALL;
十、生产用途警告
| 场景 | 是否推荐 |
|---|---|
| 性能测试 / 压测 | ✅ 强烈推荐 |
| 开发环境测试 | ✅ 推荐 |
| 学习 SQL / 调优 | ✅ 推荐 |
| 演示 Demo | ✅ 推荐 |
| 生产环境填充 | ❌ 绝对不要！ |
永远不要用这种方式直接在生产环境插入随机数据，会污染真实数据。
一句话总结
PostgreSQL 自带的 generate_series + random() 是大规模测试数据生成的"瑞士军刀"，配合 UNLOGGED 表 + 删索引 + 分批插入 + COPY 可以在分钟级生成亿级数据。
核心套路：
INSERT INTO target_table (col1, col2, ...)SELECT <random 表达式>, <random 表达式>, ...FROM generate_series(1, N);
性能要求高时改用 COPY，需要更逼真的中文/真实场景数据时配合 Python Faker 或 mockaroo。
以上就是PostgreSQL大规模随机数据生成的完整指南的详细内容，更多关于PostgreSQL大规模随机数据生成的资料请关注本站其它相关文章！
您可能感兴趣的文章:- Python随机生成数据后插入到PostgreSQL
- PostgreSQL 16 新特性之正态分布随机数函数的示例
- postgreSQL自动生成随机数值的实例
相关文章
- 窃贼地精联机卡了怎么办？窃贼地精联机失败最新解决方法 07-27
- 万达影城app能改签吗 操作方法介绍 07-27
- 逆战未来雷霆之影 逆战未来雷霆之影最强搭配与实战技巧指南 07-27
- 逆战未来昆仑神宫 逆战未来昆仑神宫玩法解析与实战技巧 07-27
- 逆战未来竞技模式 逆战未来竞技模式玩法详解与上分技巧 07-27
- 逆战未来伯特的审判 逆战未来伯特的审判玩法解析与实战评测 07-27