PostgreSQL快速导入百万级模拟数据匹配生产环境数据量的最优方案？

你当前使用的循环插入脚本存在两个核心问题：一是单条循环插入的事务、索引校验开销极高，二是table4的插入语句存在语法错误（random前缺少单引号），可通过以下方式优化插入效率：

1. 替换循环为SQL层批量插入 #

直接使用PostgreSQL内置的generate_series函数 + CTE关联插入，完全规避PL/pgSQL循环开销，单次批量插入10_{50万条数据，性能是循环插入的10}20倍。
如果你的5张表存在ID关联依赖，可参考以下写法：

-- 单次批量插入10万条，可根据服务器内存调整批次大小
WITH inserted_t1 AS (
  INSERT INTO table1 (col1, col2, col3)
  SELECT 'random', 'data', 'here'
  FROM generate_series(1, 100000)
  RETURNING id AS t1_id
),
inserted_t2 AS (
  INSERT INTO table2 (col1, col2, col3, t1_ref_id)
  SELECT 'random', 'data', 'here', t1_id
  FROM inserted_t1
  RETURNING id AS t2_id, t1_id
),
inserted_t3 AS (
  INSERT INTO table3 (col1, col2, col3, t1_ref_id, t2_ref_id)
  SELECT 'random', 'data', 'here', t1_id, t2_id
  FROM inserted_t2
  RETURNING id AS t3_id
)
INSERT INTO table4 (col1, col2, col3)
SELECT 'random', 'data', 'here'
FROM inserted_t3;

-- 单独批量插入table5，不需要关联的表可以单独执行批量插入
INSERT INTO table5 (col1, col2, col3)
SELECT 'random', 'data', 'here'
FROM generate_series(1, 100000);

2. 插入前临时调整数据库配置，进一步提升速度 #

• 插入前删除表上的普通索引、外键约束，所有数据插入完成后再重建，性能可提升3~5倍
• 临时关闭表上的触发器、规则，插入完成后再恢复
• 测试库可临时调整WAL配置：将wal_level设为minimal，max_wal_size调大到10GB以上，减少WAL刷盘频率
• 全量插入用单事务包裹，避免多次事务提交开销

比手动写SQL插入更高效的方案有以下几种：

• 使用PostgreSQL自带的pgbench工具，支持自定义数据生成脚本、多线程并发插入，千万级数据可在几十分钟内完成生成
• 使用Python的Faker库 + psycopg2批量接口，可生成符合真实业务规则的模拟数据（比如姓名、手机号、地址格式，匹配生产的数据分布），配合多进程可大幅提升生成速度
• 条件允许的情况下优先使用脱敏后的生产数据：通过脱敏工具将生产库的敏感字段替换为伪数据，导出后导入测试库，这种方式生成的测试数据不仅量级匹配，数据分布、索引基数也和生产完全一致，性能测试结果的可信度远高于随机模拟数据

• 数据插入完成后，对所有测试表执行ANALYZE 表名，更新数据库统计信息，保证查询执行计划和生产一致
• 尽量保证测试库的硬件配置（CPU、内存、磁盘IO）、数据库参数配置和生产环境对齐，否则测试结果不具备参考性
• 不要用单线程手动发起API请求，可使用Jmeter、Locust等压测工具模拟并发请求，可同时测出单查询延迟和系统吞吐量，更贴合生产实际使用场景

内容的提问来源于stack exchange，提问作者PainIsAMaster