PostgreSQL 11.6如何实现CTE查询并行插入及分区表CTAS

原生CTAS/SELECT INTO的并行能力边界 #

PostgreSQL 11 版本的并行查询机制仅支持SELECT阶段的并行执行，插入环节不存在原生并行能力：所有并行worker返回的计算结果会统一汇总到主进程，由主进程单线程完成数据写入目标表的动作，没有任何参数可以开启单条语句内的插入并行，该能力直到PostgreSQL 14版本才正式上线。
你当前观察到的并行仅发生在CTE扫描、过滤、计算的阶段，7.5亿条数据量级下，单线程插入会成为整个流程的明显瓶颈。

分区表对插入并行的实际作用 #

PostgreSQL 11 下，分区表本身不会让单条CTAS/INSERT语句的插入自动并行，优化器没有自动生成分区级并行写入计划的能力。但分区表的物理拆分特性，为手动实现并行插入提供了基础：

• 每个分区都是独立的物理文件，独立维护自己的存储、索引、锁资源
• 多个会话同时向不同分区写入时，不会产生页锁、扩展锁冲突，WAL写入压力也会分散到不同文件
• 写入完成后不需要额外做数据合并，直接通过父表即可访问全量数据

哈希分区表并行写入实操 #

PostgreSQL 11 不支持CTAS语法直接创建分区表，必须手动建表后通过多会话并行写入实现等价效果，步骤如下：

1. 提前创建哈希分区父表与对应子分区，字段定义与CTE返回字段完全对齐。分区数建议与服务器CPU物理核心数匹配，不要超过核数避免调度开销，示例：

   -- 创建哈希分区父表
   CREATE TABLE target_table (
       id bigint,
       col1 text,
       col2 int
       -- 补充其余和CTE输出对齐的字段
   ) PARTITION BY HASH (id);
   
   -- 以4分区为例创建子分区
   CREATE TABLE target_table_p0 PARTITION OF target_table FOR VALUES WITH (MODULUS 4, REMAINDER 0);
   CREATE TABLE target_table_p1 PARTITION OF target_table FOR VALUES WITH (MODULUS 4, REMAINDER 1);
   CREATE TABLE target_table_p2 PARTITION OF target_table FOR VALUES WITH (MODULUS 4, REMAINDER 2);
   CREATE TABLE target_table_p3 PARTITION OF target_table FOR VALUES WITH (MODULUS 4, REMAINDER 3);
   

2. 开启与分区数相等的独立数据库连接，每个连接仅向一个子分区写入数据，每个连接内的SELECT逻辑依然可以正常触发并行查询。注意WHERE条件必须和分区路由规则完全匹配，避免写入不符合分区约束的数据报错，示例：

   -- 连接1 写入p0
   INSERT INTO target_table_p0
   WITH your_cte AS (
       -- 原有CTE查询逻辑保持不变
   )
   SELECT * FROM your_cte
   WHERE MOD(hashtext(id::text), 4) = 0; -- 与分区规则对齐的过滤条件
   

   其余3个连接分别修改过滤条件为=1/=2/=3，写入对应子分区即可。

性能优化注意事项 #

• 写入前先删除目标表（含所有子分区）的索引、外键约束，数据全量写入完成后再统一重建，该操作通常能带来30%以上的写入速度提升
• 离线一次性导入场景，可以先将表设置为UNLOGGED，导入完成后再改回LOGGED，能大幅降低WAL写入开销，注意设置为UNLOGGED期间数据库崩溃会丢失该表数据，不适合线上业务场景
• 导入前适当调大会话级maintenance_work_mem（建议设置为1-4GB，根据服务器内存调整）、wal_buffers参数，加速数据写入与后续索引构建
• 不需要通过事务包裹多个连接的写入操作，单条写入语句自带事务即可，避免长事务占用过多资源
• 如果不想使用分区表，也可以用同样的多连接拆分逻辑，将数据分别写入独立的中间表，最后通过UNION ALL视图对外提供访问，但后续维护成本高于分区表方案

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