PostgreSQL批量插入十亿行浮点测试数据测试PostGIS性能的优化咨询
低门槛批量插入测试数据方案
首先修正你现有代码的两个基础问题:
- 存在拼写错误:代码中变量名
postition是笔误,和实际声明的变量position不匹配,执行会直接报语法错误 - 逐行循环插入的效率问题核心:每执行一次单条INSERT就要单独走一遍语句解析、权限校验、事务写入流程,循环到十万行以上时,执行上下文会不断累积不释放,自然会出现耗时久、内存飙升的问题。
根本不需要手动拼接多行值的实现方式
很多新手会误以为批量插入必须手写(a, b), (c, d)格式的VALUES元组串,其实PostgreSQL自带的generate_series函数可以直接批量生成连续的整数、浮点数序列,直接把生成的结果集作为INSERT的数据源,就可以自动实现一次插入数十万、上百万行的效果,完全不需要手动拼值结构。
单次插入100万行的参考代码
完全匹配你原来的字段逻辑,不需要修改表结构:
-- 单批插入100万行 INSERT INTO lat_longs(counting, position) SELECT g AS counting, -- 对应你原来从0开始累加的序号 g * 0.0000001 AS position -- 对应你原来步长0.0000001的浮点值 FROM generate_series(0, 999999) g;
执行上面的语句时,PostgreSQL会自动在底层构造等效的多行值结构一次性写入,不需要你手动拼任何元组,执行速度比你原来的逐行循环快至少100倍。
凑够10亿行的批量循环代码
不需要外部脚本,直接在数据库内执行,每批固定插入100万行,主动提交事务避免内存累积:
do $$ declare batch_size int := 1000000; -- 每批插入量固定为100万行 total_rows bigint := 1000000000; -- 目标总插入量为10亿行 begin -- 按批次循环,每次生成100万行数据插入 for start_offset in 0 .. total_rows / batch_size - 1 loop INSERT INTO lat_longs(counting, position) SELECT start_offset * batch_size + g AS counting, (start_offset * batch_size + g) * 0.0000001 AS position FROM generate_series(0, batch_size - 1) g; -- 每批插入完成主动提交,释放执行上下文,避免内存飙升 COMMIT; end loop; raise notice '全部10亿行数据插入完成'; end$$;
简单的提速小技巧(零学习成本)
- 插入数据前先临时删掉目标表上的所有索引,等10亿行全部插入完成后再重建索引,插入速度会提升30%以上
- 不需要修改数据库的底层参数配置,也不需要学习WAL、并行执行这类高阶概念,上面的代码本身就可以满足你测试PostGIS函数性能的需求
内容的提问来源于stack exchange,提问作者Andrew Holway
相关产品推荐
相关产品推荐

