如何优化Postgres批量插入？插入超100行无响应无报错问题排查

现有业务表session_prepared需适配1万行以上规模的批量插入场景，表字段结构如下：

数据集采用PHP关联数组格式存储，每行数据对应一个数组元素，定义代码：

$dataset = [["columnindex" => 1, "rowindex" => 2, "type" => "num", "value" => 400], ...]

当前异常表现：调用Laravel框架自带的insert方法执行写入：

SessionPrepared::insert($dataset);

传入100行规模的数据集时Postgres无响应，PDO未返回任何错误信息；将数据集通过array_slice切片仅保留前10行时可正常写入：

$dataset = array_slice($dataset, 0, 10);

• 优先确认参数绑定超限问题：检查PHP配置max_input_vars数值，Laravel的批量insert会生成多值INSERT语句，每个字段对应一个PDO预处理占位符，100行4个字段共400个绑定参数，若max_input_vars配置值低于该数值，PHP会静默截断参数，导致Postgres一直等待缺失参数、出现无响应无报错的现象。同时把PDO错误模式调整为异常抛出：在数据库连接配置中添加PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION，打印异常信息和最终生成的SQL语句，确认是否存在语法错误、字段不匹配问题。
• 排查锁阻塞问题：查看Postgres服务端日志（默认路径为/var/log/postgresql/对应版本目录），查询pg_locks系统视图，确认插入请求是否被未提交的长事务、表级锁阻塞。
• 校验运行时资源限制：检查PHP配置的memory_limit、max_execution_time、Web服务（Nginx/Apache/FPM）的超时配置，确认是否是请求内存不足、进程超时被静默杀死导致无响应。
• 检查表对象附加逻辑：确认表上是否存在行级触发器、复杂Check约束、外键约束，10行数据写入时校验耗时短无感知，100行数据写入时校验逻辑累计耗时过长导致请求挂起。

• 分片控制单批次写入规模：不要一次性传入全量万行数据调用insert，按单批次500-1000行对数据集做切分写入，既可以避开参数数量限制，也能避免单条SQL过长、单次锁持有时间过久的问题，示例代码：

$batchSize = 500;
foreach (array_chunk($dataset, $batchSize) as $batch) {
    SessionPrepared::insert($batch);
}

• 手动包裹事务减少IO开销：关闭框架单条SQL自动提交逻辑，把全量批量插入逻辑包裹在同一个手动事务中，减少事务提交时的WAL日志刷盘次数，能提升30%以上的写入性能，示例：

DB::transaction(function () use ($dataset) {
    $batchSize = 500;
    foreach (array_chunk($dataset, $batchSize) as $batch) {
        SessionPrepared::insert($batch);
    }
}, 5); // 第二个参数可按需调整事务重试次数

• 万行以上场景使用COPY原生语法：Postgres提供的COPY命令是专为批量数据导入设计的接口，写入性能是普通INSERT的5-10倍，可通过PDO直接调用STDIN流写入，无需额外扩展，适合大规模数据导入场景。
• 离线导入场景临时调整索引策略：如果是无业务并发的离线数据导入，可以在写入前暂时删除非主键索引、临时关闭外键约束校验，数据全部写入完成后再重建索引、恢复约束，能减少插入过程中索引维护的额外开销，线上并发写入场景禁止使用该方案。
• 服务端参数适配调优：针对批量写入场景，可临时调大Postgres配置的wal_buffers、checkpoint_timeout参数，减少WAL刷盘和检查点触发频率，写入完成后手动触发checkpoint即可，注意参数调整需要结合服务器内存规格评估，不要盲目调大。

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