从零开始学PostgreSQL (十二)：高效批量写入数据库

目录

1. 概述

2. 禁用自动提交

3. 使用 COPY

4. 删除索引

5. 删除外键约束

6. 增加maintenance_work_mem

7. 增加max_wal_size

8. 禁用 WAL 存档和流复制

9. 之后运行 ANALYZE

10. 关于pg_dump的一些注意事项

11. 非持久设置

12. 注意事项

13. 总结

概述

当需要在PostgreSQL数据库中大规模填充数据时，采用正确的策略至关重要。以下是提高数据加载效率的九个关键步骤：

1. 禁用自动提交：自动提交会为每次SQL语句结束时强制进行一次事务提交，这增加了磁盘I/O操作次数。在批量插入数据时，关闭自动提交，改为手动管理事务，可以显著提高数据加载速度。

2. 使用 COPY 命令：COPY命令专门设计用于高效数据加载，它绕过了许多标准SQL语句中的开销，如触发器和约束检查，从而大幅提高数据导入速度。

3. 删除索引：索引在数据加载过程中会减慢速度，因为每插入一条记录，索引都需要更新。在数据导入前临时删除索引，待数据加载完毕后再重建，可以大幅提升效率。

4. 删除外键约束：外键约束在数据插入时会进行额外的检查，这会消耗额外的CPU和I/O资源。在数据加载阶段禁用这些约束，待数据加载完成后重新启用，可以加快数据加载过程。

5. 增加 maintenance_work_mem：maintenance_work_mem参数控制了PostgreSQL在执行维护操作（如创建索引）时可以使用的内存量。增加这个参数的值可以加快索引构建速度。

6. 增加 max_wal_size：max_wal_size决定了Write-Ahead Log（WAL）的大小，WAL用于事务恢复。在大量数据加载时，增加WAL大小可以避免WAL文件频繁切换，减少I/O操作。

7. 禁用 WAL 存档和流复制：在数据加载期间，禁用WAL归档和流式复制可以减少不必要的I/O操作和网络传输，从而提高数据加载速度。

8. 之后运行 ANALYZE：数据加载完成后，应运行ANALYZE命令更新统计信息，这有助于查询优化器更好地规划查询计划，提升查询性能。

9. 关于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可以通过以下配置调整来大幅提升性能。除了下面特别指出的情况，即便做了这些设置，数据库软件崩溃时的数据持久性依然得到保证；只有在操作系统突然崩溃的情况下，使用这些设置才会带来数据丢失或损坏的风险。

1. 将数据库集群的数据目录放置在基于内存的文件系统中（即RAM磁盘）。这样可以消除所有的数据库磁盘I/O操作，但是数据存储会被限制在可用内存（以及可能的交换空间）的范围内。

2. 关闭fsync；没有必要将数据强制刷写到磁盘。

3. 关闭synchronous_commit；可能不需要在每次提交时强制将WAL（Write-Ahead Log，预写式日志）写入磁盘。这个设置确实增加了事务丢失的风险（尽管不会导致数据损坏），在数据库崩溃时尤为如此。

4. 关闭full_page_writes；没有必要防范部分页面写入的问题。

5. 增加max_wal_size和checkpoint_timeout；这可以降低检查点的发生频率，但同时会增加./pg_wal目录下的存储需求。

6. **创建非日志表(unlogged tables)**来避免WAL写入，但这会使这些表在崩溃时无法恢复。

通过这些设置，你可以牺牲一部分数据的安全性来换取更高的性能。然而，在做出这些调整之前，务必充分理解它们所带来的风险，并确保你的应用程序能够承受潜在的数据损失。在生产环境中，通常不推荐完全禁用持久性机制，除非你有非常明确的需求和相应的数据恢复计划。

注意事项

• 在进行上述操作时，务必确保数据完整性。例如，删除索引和外键约束后，应在数据加载完毕后立即重建，以维持数据一致性。

• 对于生产环境，任何更改配置或数据库结构的操作都应先在测试环境中验证，以避免对生产数据造成意外影响。

• 调整配置参数前，应充分了解每个参数的作用和可能的影响，避免对数据库性能或稳定性造成负面影响。

总结

通过遵循上述策略，可以显著提高在PostgreSQL数据库中批量加载数据的效率。然而，这些操作应谨慎执行，以防止对数据完整性和系统稳定性产生不良影响。始终在安全的测试环境下试验这些方法，并在生产环境中实施前进行全面的备份和测试。

上一篇：从零开始学PostgreSQL (十一)：并发控制

从零开始学PostgreSQL (十)：磁盘使用情况

从零开始学PostgreSQL (九)：任务进度报告

从零开始学PostgreSQL (八)：监控数据库动态

从零开始学PostgreSQL (七)：高可用性、负载平衡和复制

从零开始学PostgreSQL (六)：备份和恢复

从零开始学PostgreSQL (五)：日常数据库维护任务

从零开始学PostgreSQL (四)：数据库角色

从零开始学PostgreSQL (三)：索引篇

从零开始学PostgreSQL (二)：配置文件

从零开始学PostgreSQL (一)：Centos8 RPM安装PostgreSQL16

公众号内直接回复加群也可以,扫码加群也可以，觉得帖子写的不错，点点关注，点点赞，多多转载，请多多支持。