PostgreSQL插入2万条数据耗时80+秒，如何优化及排查原因？

导致插入缓慢的常见原因 #

• 频繁事务提交：如果将20000条数据拆分成多次小事务提交（甚至单条提交），PostgreSQL每次提交都要强制刷写WAL（预写日志）到磁盘，频繁的IO操作会极大拖慢插入速度。
• 索引/约束过多：若users表上存在多个索引（比如userId的唯一索引、name的普通索引）或外键约束，每条数据插入时都要更新所有索引、校验约束，20000条数据的累积开销会非常明显。
• WAL配置偏保守：默认的WAL配置优先保证数据安全性，比如wal_sync_method设为fsync时每次提交都会强制刷盘，批量插入时会成为IO瓶颈。
• 数据类型不匹配：如果插入值的类型和表字段定义不一致（比如age是整数类型却插入字符串'11'），数据库会自动做类型转换，大量数据插入时这类转换的额外开销会被放大。
• 硬件IO性能不足：使用机械硬盘等低速存储设备时，大量写入操作的IO速度跟不上，直接导致插入耗时过长。

缩短插入耗时的优化方法 #

• 合并为单事务提交：将所有20000条数据放在一个事务中执行，仅在最后提交一次。这样只需要一次WAL刷写操作，能大幅减少IO次数：

  BEGIN;
  INSERT INTO "public"."users" ("userId", "name", "age") VALUES ('1234', 'nop', 11), (...); -- 所有数据行
  COMMIT;
  

• 临时移除索引与约束：插入前删除非必需的索引，禁用外键约束，插入完成后再重建索引、启用约束（注意唯一性约束若需保证数据正确性，不建议禁用）：

  -- 禁用外键触发器
  ALTER TABLE users DISABLE TRIGGER ALL;
  -- 删除索引（示例）
  DROP INDEX IF EXISTS idx_users_userid;
  -- 执行批量插入
  INSERT INTO "public"."users" ("userId", "name", "age") VALUES (...);
  -- 重建索引
  CREATE INDEX idx_users_userid ON users(userId);
  -- 启用触发器
  ALTER TABLE users ENABLE TRIGGER ALL;
  

• 改用COPY命令导入：PostgreSQL的COPY命令是专为批量数据设计的，性能远优于普通INSERT。可以将数据整理为CSV文件后导入：

  COPY users(userId, name, age) FROM '/data/users.csv' WITH (FORMAT csv, HEADER);
  

  如果是程序生成数据，也可以用对应客户端的COPY接口（比如Python的psycopg2库的copy_from方法）。
• 临时调整WAL参数：插入前临时增大wal_buffers和work_mem，或根据存储设备调整wal_sync_method（比如改为fdatasync），插入完成后恢复原配置，平衡性能与安全性。
• 修正数据类型：确保插入值的类型与表字段定义完全匹配，比如age字段为INT类型时直接插入整数11而非字符串'11'，避免不必要的类型转换开销。
• 升级存储硬件：若存在IO瓶颈，更换为SSD存储设备，提升磁盘写入速度。

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