PostgreSQL大規(guī)模隨機數據生成的完整指南
更新時間：2026年05月17日 15:05:15 作者：zxrhhm
本文詳細介紹了使用PostgreSQL自帶的generate_series和random()函數家族快速生成大規(guī)模測試數據的方法和技巧,涵蓋基礎用隨機函數、典型應用場景、性能優(yōu)化、第三方工具推薦、實戰(zhàn)案例及注意事項,需要的朋友可以參考下
一、核心工具：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 步長 5
SELECT * 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 萬行用戶數據
-- 創(chuàng)建表
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_active
FROM generate_series(1, 1000000) i;
場景 2：生成訂單數據（含外鍵關聯(lián)）
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 萬訂單，關聯(lián)到已有的 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_at
FROM generate_series(1, 5000000) i;
場景 3：生成中文姓名（仿真數據）
-- 創(chuàng)建一個臨時數據池
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_address
FROM 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_at
FROM generate_series(1, 1000000);
場景 6：生成關聯(lián)表（一對多）
-- 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 u
CROSS JOIN LATERAL generate_series(1, 5 + (random() * 15)::INT);
場景 7：生成正態(tài)分布數據（更真實）
random() 是均勻分布，但真實業(yè)務數據通常是正態(tài)分布。
-- Box-Muller 變換生成正態(tài)分布
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);
四、性能優(yōu)化技巧（億級數據生成）
優(yōu)化 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);
優(yōu)化 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 $$;
優(yōu)化 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;
優(yōu)化 4：并行生成（最快）
利用多個會話并行插入到不同的分區(qū)：
# 4 個會話并行
for i in {0..3}; do
psql -c "INSERT INTO users SELECT ... FROM generate_series($((i*2500000+1)), $(((i+1)*2500000)))" &
done
wait
五、性能對比實測
生成 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 數據生成。
# 創(chuàng)建并初始化測試數據 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-binary
from faker import Faker
import psycopg2
fake = 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 導入。
七、實戰(zhàn)完整案例：電商系統(tǒng)測試數據
-- ============== 1. 創(chuàng)建表結構 ==============
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)::INT
FROM 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 o
CROSS 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. 收集統(tǒng)計信息 ==============
ANALYZE customers;
ANALYZE products;
ANALYZE orders;
ANALYZE order_items;
八、常用代碼片段速查
-- 隨機整數 1~100
(random() * 99 + 1)::INT
floor(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); -- 固定種子,后續(xù) random() 結果可重復 SELECT random() FROM generate_series(1, 5);
2. 大數據量生成時的內存問題
-- ? 一次性生成 1 億行可能 OOM INSERT 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. 避免觸發(fā)器影響
-- 臨時禁用觸發(fā)器 ALTER TABLE users DISABLE TRIGGER ALL; -- 插入數據 ... ALTER TABLE users ENABLE TRIGGER ALL;
十、生產用途警告
| 場景 | 是否推薦 |
|---|---|
| 性能測試 / 壓測 | ? 強烈推薦 |
| 開發(fā)環(huán)境測試 | ? 推薦 |
| 學習 SQL / 調優(yōu) | ? 推薦 |
| 演示 Demo | ? 推薦 |
| 生產環(huán)境填充 | ? 絕對不要！ |
永遠不要用這種方式直接在生產環(huán)境插入隨機數據，會污染真實數據。
一句話總結
PostgreSQL 自帶的 generate_series + random() 是大規(guī)模測試數據生成的"瑞士軍刀"，配合 UNLOGGED 表 + 刪索引 + 分批插入 + COPY 可以在分鐘級生成億級數據。
核心套路：
INSERT INTO target_table (col1, col2, ...)
SELECT
<random 表達式>,
<random 表達式>,
...
FROM generate_series(1, N);
性能要求高時改用 COPY，需要更逼真的中文/真實場景數據時配合 Python Faker 或 mockaroo。
以上就是PostgreSQL大規(guī)模隨機數據生成的完整指南的詳細內容，更多關于PostgreSQL大規(guī)模隨機數據生成的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了解決PostgreSQL 執(zhí)行超時的情況，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql通過索引優(yōu)化查詢速度操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
今天想測試一個PostgresSQL語法的 SQL,但是打開PostgresSQL之后沉默了,密碼是什么？日長月久的,漸漸就忘記了,于是開始了尋找密碼的道路,所以本文介紹了Postgresql忘記密碼,如何重置密碼,需要的朋友可以參考下2024-05-05
PostgreSQL 實現(xiàn)distinct關鍵字給單獨的幾列去重
這篇文章主要介紹了PostgreSQL 實現(xiàn)distinct關鍵字給單獨的幾列去重，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要給大家介紹了關于postgresql數據庫如何查看數據中表信息的相關資料,要查詢數據表信息,需要用到 系統(tǒng)表或系統(tǒng)視圖等,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-04-04
這段文章詳細介紹了在PostgreSQL中選擇合適的日期時間數據類型的方法,特別推薦使用timestampwithouttimezone類型來存儲精確到微秒的日期和時間,文章對比了多種日期時間數據類型的特點和適用場景,并并并強調了避免使用varchar等存儲日期時間的重要性2026-06-06
PostgreSQL行鎖分為共享鎖和排他鎖,用于控制并發(fā)事務對行數據的訪問,共享鎖允許多事務讀取,排他鎖獨占訪問,本文就來具體介紹一下行鎖,感興趣的可以了解一下2025-06-06
postgresql的now()與Oracle的sysdate區(qū)別說明
這篇文章主要介紹了postgresql的now()與Oracle的sysdate區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL 中 VACUUM 操作的鎖機制詳細對比解析
PostgreSQL 提供了三種主要的 VACUUM 操作：AutoVACUUM、VACUUM 和 VACUUM FULL,它們在鎖機制上有顯著差異,下面給大家分享PostgreSQL 中 VACUUM 操作的鎖機制詳細對比解析,感興趣的朋友一起看看吧2025-05-05
文章介紹了兩種運行預寫好的SQL文件的方式：首先連接數據庫后執(zhí)行,或者直接通過psql命令執(zhí)行,需要注意的是,文件路徑在Linux系統(tǒng)中應使用斜杠/,而不是反斜杠\,否則會報Permission denied錯誤2024-12-12