如何基于外键高效向posts、authors、categories三张表批量插入5万条数据

现有方案的核心问题 #

你的当前实现处理5万条数据会有严重性能瓶颈，同时存在数据一致性风险：

• 15万次独立数据库请求带来巨量网络IO开销，串行执行的情况下耗时会达到数分钟甚至更久
• 每次插入post、category都要额外执行子查询查找关联ID，额外增加了10万次表查询开销
• 未做重复数据兼容：如果多个post对应同一个作者，插入作者时会触发author_name唯一约束报错直接中断
• 无事务保障：如果某一条数据插入中途失败，已插入的数据无法自动回滚，会出现脏数据

性能优化方案 #

方案1：批量插入+内存映射（性能最优，推荐） #

总共只需要3~10次数据库请求即可完成全量插入，性能比原有方案提升几十上百倍：

1. 先遍历所有待插入数据，提取所有作者去重，批量插入作者表，用ON CONFLICT跳过重复作者，同时用RETURNING返回所有作者ID和名称的映射存在内存对象中

INSERT INTO authors (author_name, author_slug)
VALUES 
('Dan Brown', 'dan-brown'),
('Author 2', 'author-2'),
-- 批量列出去重后的所有作者
ON CONFLICT (author_name) DO NOTHING
RETURNING id, author_name;

2. 遍历所有待插入数据，用上面拿到的作者ID映射替换对应post的author_id，批量插入post表，同样用RETURNING返回post ID和post标识的映射

INSERT INTO posts (post, post_slug, author_id)
VALUES
('post1', 'post1', 1),
('post2', 'post2', 2),
-- 批量列所有post
RETURNING id, post_slug;

3. 遍历所有待插入数据，用post ID映射替换post_id，批量插入全部分类

INSERT INTO categories (category_name, category_slug, post_id)
VALUES
('Thriller', 'thriller', 1),
('Fiction', 'fiction', 2),
-- 批量列所有分类

如果数据量太大，可以按每1000~5000条为一个批次拆分执行，避免单条SQL过长。

方案2：单条数据CTE写入（兼顾简洁和性能） #

如果不想处理批量映射，可将单条数据的3次写入合并为1次CTE查询，天然带事务属性，不会出现脏数据，总请求数从15万降到5万，性能也有3倍以上提升：

WITH inserted_author AS (
  INSERT INTO authors (author_name, author_slug)
  VALUES ($1, $2)
  ON CONFLICT (author_name) DO UPDATE SET author_slug = EXCLUDED.author_slug
  RETURNING id AS author_id
),
inserted_post AS (
  INSERT INTO posts (post, post_slug, author_id)
  SELECT $3, $4, author_id FROM inserted_author
  RETURNING id AS post_id
)
INSERT INTO categories (category_name, category_slug, post_id)
SELECT $5, $6, post_id FROM inserted_post;

用参数化查询传入对应值即可。

额外优化点 #

• 你当前建表语句中posts.author_id、categories.post_id字段用serial类型不合理，改为integer即可，serial是自增字段类型，用于外键会生成无用的自增序列
• 插入过程中可以临时关闭非必要索引、约束校验，插入完成后再开启，可进一步提升写入速度

内容的提问来源于stack exchange，提问作者s.khan