目录
概述
禁用自动提交
使用 COPY
删除索引
删除外键约束
增加maintenance_work_mem
增加max_wal_size
禁用 WAL 存档和流复制
之后运行 ANALYZE
关于pg_dump的一些注意事项
非持久设置
注意事项
总结
概述
当需要在PostgreSQL数据库中大规模填充数据时,采用正确的策略至关重要。以下是提高数据加载效率的九个关键步骤:
禁用自动提交:自动提交会为每次SQL语句结束时强制进行一次事务提交,这增加了磁盘I/O操作次数。在批量插入数据时,关闭自动提交,改为手动管理事务,可以显著提高数据加载速度。
使用 COPY 命令:COPY命令专门设计用于高效数据加载,它绕过了许多标准SQL语句中的开销,如触发器和约束检查,从而大幅提高数据导入速度。
删除索引:索引在数据加载过程中会减慢速度,因为每插入一条记录,索引都需要更新。在数据导入前临时删除索引,待数据加载完毕后再重建,可以大幅提升效率。
删除外键约束:外键约束在数据插入时会进行额外的检查,这会消耗额外的CPU和I/O资源。在数据加载阶段禁用这些约束,待数据加载完成后重新启用,可以加快数据加载过程。
增加 maintenance_work_mem:maintenance_work_mem参数控制了PostgreSQL在执行维护操作(如创建索引)时可以使用的内存量。增加这个参数的值可以加快索引构建速度。
增加 max_wal_size:max_wal_size决定了Write-Ahead Log(WAL)的大小,WAL用于事务恢复。在大量数据加载时,增加WAL大小可以避免WAL文件频繁切换,减少I/O操作。
禁用 WAL 存档和流复制:在数据加载期间,禁用WAL归档和流式复制可以减少不必要的I/O操作和网络传输,从而提高数据加载速度。
之后运行 ANALYZE:数据加载完成后,应运行ANALYZE命令更新统计信息,这有助于查询优化器更好地规划查询计划,提升查询性能。
关于pg_dump的注意事项:使用pg_dump工具进行数据导出或导入时,要注意选择正确的选项以适应不同的数据恢复需求。此外,pg_dump的输出格式(如custom或plain)也会影响数据恢复的速度和效率。
禁用自动提交
使用多个INSERT语句时,应关闭自动提交功能,只在所有插入操作完成后做一次提交。这避免了每次插入操作后都进行磁盘I/O操作,从而显著提高效率。若单独提交每行数据,PostgreSQL将为每行执行大量工作,批量事务还能保证数据一致性,防止部分数据加载成功的情况。
使用COPY命令
利用COPY命令一次性加载所有行,而非一系列的INSERT命令。COPY针对大量行的加载进行了优化,虽然灵活性不如INSERT,但在大数据量加载时开销明显较小。由于COPY是一次性操作,无需为此禁用自动提交。
删除索引
对于新创建的表,最快的方法是先创建表,使用COPY批量加载数据,之后再创建所需索引。在已有数据上创建索引比逐行更新索引更快。对于向现有表添加大量数据,可先删除索引,加载数据后重建,但需权衡对其他用户数据库性能的影响。
移除外键约束
与索引类似,外键约束可以批量检查,而非逐行检查,因此可以先移除,加载数据后重建。在外键约束存在的情况下加载数据,每行新增数据都会在服务器的待处理触发事件队列中增加条目,大规模数据加载可能导致内存溢出,甚至命令失败。
增加maintenance_work_mem
加载大量数据时,临时增加maintenance_work_mem配置变量可以提高性能,尤其是CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令的执行速度。
增大max_wal_size
临时增大max_wal_size也能加快大量数据加载速度,这是因为数据加载会导致检查点更频繁,而检查点要求刷新所有脏页至磁盘。增大max_wal_size可以减少所需检查点的数量。
禁用WAL归档和流式复制
在使用WAL归档或流式复制的环境中加载大量数据,禁用这些功能可能更快。通过将wal_level设为minimal,archive_mode设为off,max_wal_senders设为0,可以避免增量WAL日志记录,同时某些命令无需写WAL,进一步提高速度。
执行ANALYZE
数据分布发生显著变化后,强烈推荐运行ANALYZE,包括批量加载大量数据后。这确保了查询规划器有最新的统计信息,避免因统计信息缺失或过时而导致的查询性能不佳。
关于pg_dump的几点说明
pg_dump生成的脚本默认应用了部分上述优化,但要快速还原pg_dump的备份,还需手动调整一些配置,比如增加maintenance_work_mem和max_wal_size的值,以及在使用WAL归档或流式复制时考虑禁用这些功能。实验pg_dump和pg_restore的并行模式,找到最佳并发作业数量。考虑是否将整个备份作为一个事务恢复,以及使用pg_restore的--jobs选项允许并发数据加载和索引创建
非持久化设置
持久性是数据库的一项特性,它保证即使服务器崩溃或断电,已提交的事务记录也会被保留。然而,持久性会增加数据库的额外开销,如果你的应用场景并不需要这种级别的保障,PostgreSQL可以通过以下配置调整来大幅提升性能。除了下面特别指出的情况,即便做了这些设置,数据库软件崩溃时的数据持久性依然得到保证;只有在操作系统突然崩溃的情况下,使用这些设置才会带来数据丢失或损坏的风险。
将数据库集群的数据目录放置在基于内存的文件系统中(即RAM磁盘)。这样可以消除所有的数据库磁盘I/O操作,但是数据存储会被限制在可用内存(以及可能的交换空间)的范围内。
关闭fsync;没有必要将数据强制刷写到磁盘。
关闭synchronous_commit;可能不需要在每次提交时强制将WAL(Write-Ahead Log,预写式日志)写入磁盘。这个设置确实增加了事务丢失的风险(尽管不会导致数据损坏),在数据库崩溃时尤为如此。
关闭full_page_writes;没有必要防范部分页面写入的问题。
增加max_wal_size和checkpoint_timeout;这可以降低检查点的发生频率,但同时会增加./pg_wal目录下的存储需求。
**创建非日志表(unlogged tables)**来避免WAL写入,但这会使这些表在崩溃时无法恢复。
通过这些设置,你可以牺牲一部分数据的安全性来换取更高的性能。然而,在做出这些调整之前,务必充分理解它们所带来的风险,并确保你的应用程序能够承受潜在的数据损失。在生产环境中,通常不推荐完全禁用持久性机制,除非你有非常明确的需求和相应的数据恢复计划。
注意事项
在进行上述操作时,务必确保数据完整性。例如,删除索引和外键约束后,应在数据加载完毕后立即重建,以维持数据一致性。
对于生产环境,任何更改配置或数据库结构的操作都应先在测试环境中验证,以避免对生产数据造成意外影响。
调整配置参数前,应充分了解每个参数的作用和可能的影响,避免对数据库性能或稳定性造成负面影响。
总结
通过遵循上述策略,可以显著提高在PostgreSQL数据库中批量加载数据的效率。然而,这些操作应谨慎执行,以防止对数据完整性和系统稳定性产生不良影响。始终在安全的测试环境下试验这些方法,并在生产环境中实施前进行全面的备份和测试。
从零开始学PostgreSQL (七):高可用性、负载平衡和复制
从零开始学PostgreSQL (一):Centos8 RPM安装PostgreSQL16
公众号内直接回复加群也可以,扫码加群也可以,觉得帖子写的不错,点点关注,点点赞,多多转载,请多多支持。