背景:最近需要以编程方式将一千万条经纬数据记录插入到postgres数据库,最后通过一系列的实验验证,摸索出一些实践经验。
1、使用COPY:
使用COPY在一条命令里装载所有记录,而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的,它不像INSERT命令那样灵活,但是在装载大量数据时,系统开销也要少很多。因为COPY是单条命令,因此在填充表的时候就没有必要关闭自动提交了。
2、关闭自动提交:
在批量插入数据时,如果每条数据都被自动提交,当中途出现系统故障时,不仅不能保障本次批量插入的数据一致性,而且由于有多次提交操作的发生,整个插入效率也会受到很大的打击。解决方法是,关闭系统的自动提交,并且在插入开始之前,显式的执行begin transaction命令,在全部插入操作完成之后再执行commit命令提交所有的插入操作。
3、删除索引:
如果你正在装载一个新创建的表,最快的方法是创建表,用COPY批量装载,然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间,其它有关该表的查询操作的性能将会受到一定的影响,唯一性约束也有可能遭到破坏。
4、删除外键约束:
和索引一样,"批量地"检查外键约束比一行行检查更加高效。因此,我们可以先删除外键约束,装载数据,然后再重建约束。
5、增大maintenance_work_mem:
在装载大量数据时,临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率,但是它不会对COPY操作本身产生多大的影响。因为在一个数据库会话中,一个时刻只有一个这样的操作可以被执行,并且一个数据库安装通常不会有太多这样的操作并发执行, 把这个数值设置得比work_mem大很多是安全的。 更大的设置可以改进清理和恢复数据库转储的性能。
6、增大checkpoint_segments:
临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向PostgreSQL装载大量数据时,将会导致检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时,所有的脏数据都必须flush到磁盘上。通过提高checkpoint_segments变量的值,可以有效的减少检查点的数目。
7、事后运行ANALYZE:
在增加或者更新了大量数据之后,应该立即运行ANALYZE命令,这样可以保证规划器得到基于该表的最新数据统计。换句话说,如果没有统计数据或者统计数据太过陈旧,那么规划器很可能会选择一个较差的查询规划,从而导致查询效率过于低下。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。