首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >从零开始学PostgreSQL (十二):高效批量写入数据库

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

作者头像
DBA实战
发布于 2024-09-06 11:20:46
发布于 2024-09-06 11:20:46
8080
举报
文章被收录于专栏:DBA实战DBA实战

概述

当需要在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数据库中批量加载数据的效率。然而,这些操作应谨慎执行,以防止对数据完整性和系统稳定性产生不良影响。始终在安全的测试环境下试验这些方法,并在生产环境中实施前进行全面的备份和测试。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2024-08-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA实战 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
进阶数据库系列(二十三):PostgreSQL 性能优化
PostgreSQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。例如:
民工哥
2023/08/22
4.2K0
进阶数据库系列(二十三):PostgreSQL 性能优化
PostgreSQL数据库导入大量数据时如何优化
来源 | OSCHINA 社区、作者 | PostgreSQLChina 链接:https://my.oschina.net/postgresqlchina/blog/5568852 在使用 PostgreSQL 的时候,我们某些时候会往库里插入大量数据,例如,导入测试数据,导入业务数据等等。本篇文章介绍了在导入大量数据时的一些可供选择的优化手段。可以结合自己的情况进行选择。 一、关闭自动提交 关闭自动提交,并且只在每次 (数据拷贝) 结束的时候做一次提交。 如果允许每个插入都独立地提交,那么 Postg
程序猿DD
2022/08/29
1.9K0
PostgreSQL技术大讲堂 - 第32讲:数据库参数调整
PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。
用户5892232
2023/11/03
4650
PostgreSQL技术大讲堂 - 第32讲:数据库参数调整
从零开始学PostgreSQL (二): 配置文件
PostgreSQL 使用三个主要的配置文件来控制服务器的行为、网络访问和用户映射。下面是对这三个文件的详细介绍:
DBA实战
2024/09/06
7760
从零开始学PostgreSQL (二): 配置文件
数据库PostrageSQL-WAL配置
有几个WAL相关的配置参数会影响数据库性能。本节将解释它们的使用。关于服务器配置参数的设置的一般信息请参考Chapter 19。
cwl_java
2021/01/13
5870
从Oracle到PostgreSQL:一文掌握Checkpoint重要概念
墨墨导读:Checkpoint是数据库中重要的概念,无论在Oracle,MySQL这个概念,它主要功能是在检查点时刻,脏数据全部刷新到磁盘,以实现数据的一致性和完整性。PostgreSQL为什么要设计Checkpoint呢?跟Oracle一样,其主要目的是缩短崩溃恢复时间。PostgreSQL在崩溃恢复时会以最近的Checkpoint为基础,不断应用这之后的WAL日志。下面我们就从Oracle的角度去学习下PostgreSQL的Checkpoint。
数据和云
2019/07/30
1K0
pg 13批量插入最佳实践
背景:最近需要以编程方式将一千万条经纬数据记录插入到postgres数据库,最后通过一系列的实验验证,摸索出一些实践经验。
DB之路
2021/03/04
1.3K0
PostgreSQL集群篇——PostgreSQL的配置文件解析
日常中我们进行安装PostgreSQL后都需要对其进行配置基础配置,以便其能有效发挥出服务器的性能,下面是我进行整理后的postgresql.conf配置文件的相关注释,方便大家对于各个属性进行熟悉。
cn華少
2021/07/27
4K0
PostgreSQL 如何面对高压力下的写操作的优化
数据库的重要性能指标中有一项对于高并发下的数据库写操作,不少数据库都对此有执念,一秒钟写入的数据量是多少,并为此而自豪. 数据的写入在单位时间中的确是很重要的. POSTGRESQL 怎么能应对高并发下的写操作,并且在不改变目前的硬件的条件的基础上, 怎么进行优化.
postgres
2023/07/12
3430
--POSTGRESQL FULL PAGE 优化 与 CHECKPOINT 的“矛盾”!
在说完mysql 不要关DW 后,祭出 POSTGRESQL FULL PAGE 的确是有点不厚道,所以必然会引出 FULL PAGE 也存在性能问题的话题。到底是大公鸡和大马猴的问题,还是小绵羊的牧羊犬的故事。
AustinDatabases
2020/03/31
9460
PostgreSQL统计信息的几个重要视图
比如tup_returned值明显大于tup_fetched,历史SQL语句很多是全表扫描,存在没有使用索引的SQL,可结合pg_stat_statments查找慢SQL,也可结合pg_stat_user_table找全表扫描次数和行数最多的表;
yzsDBA
2020/10/29
1.3K0
PostgreSql 学了还是乱麻,那就捋一捋 DB架构
其实学习的过程中,很容易陷入一个问题,貌似都懂,一问就含糊,今天就自己捋一捋的那团乱麻。
AustinDatabases
2020/05/09
5400
PostgreSQL备份恢复实现
本文主要介绍pg_dump、pg_dumpall、copy、pg_basebackup的使用。
数据和云
2021/09/22
5.8K0
PostgreSQL备份恢复实现
POSTGRESQL MYSQL MONGODB 配置文件总结(感谢我的三个DBA)
最近在整理POSTGRESQL MYSQL MONGODB REDIS 的标准化模板配置参数,当然这里面还包含 LINUX 的一些基本配置. 相关的整理的工作是我的三个 DBA 操作的, 针对目前的参数进行了相关的整理和重新设定. 昨天有人问我要,这边进贴出来,如果大家有什么问题 ,告诉我们, 大家一起进步.
AustinDatabases
2021/02/26
8630
从零开始学PostgreSQL (六): 备份和恢复
PostgreSQL 提供了多种备份和恢复策略,旨在满足不同规模和需求的数据库环境。以下是 PostgreSQL 备份和恢复的主要方法概览:
DBA实战
2024/09/06
9650
从零开始学PostgreSQL (六): 备份和恢复
PostgreSQL 性能优化全方位指南:深度提升数据库效率
在现代互联网应用中,数据库性能优化是系统优化中至关重要的一环,尤其对于数据密集型和高并发的应用而言,PostgreSQL(以下简称PG)凭借其丰富的特性和强大的功能,成为很多企业的首选。然而,随着数据规模的扩展和查询复杂度的提升,PostgreSQL的性能问题逐渐显现。本文将详细介绍PostgreSQL性能优化的各个方面,涵盖硬件调优、数据库配置、索引使用、查询优化等内容,帮助你全方位提升数据库的效率。
用户11404404
2024/12/13
9060
PostgreSQL中的预写式日志
预写式日志write ahead log,是数据库保证数据完整性的重要数据结构。数据库管理器将数据库发生的变更记录写入wal日志缓冲区,进而写入wal日志文件中,在数据库崩溃时利用wal日志进行重演恢复,这几乎是所有数据库的统一实现原理。
数据库架构之美
2020/07/28
1.3K0
从零开始学PostgreSQL (八):监控数据库动态
PostgreSQL 提供了一套丰富的统计信息收集和报告机制,用于监控数据库的运行状况和性能。以下是这些机制的一些关键要点:
DBA实战
2024/09/06
2120
从零开始学PostgreSQL (八):监控数据库动态
数据库PostrageSQL-服务器配置资源消耗
shared_buffers (integer) 设置数据库服务器将使用的共享内存缓冲区量。默认通常是 128 兆字节(128MB),但是如果你的内核设置不支持(在initdb时决定),那么可以会更少。这个设置必须至少为 128 千字节(BLCKSZ的非默认值将改变最小值)。不过为了更好的性能,通常会使用明显高于最小值的设置。
cwl_java
2020/10/15
1.6K0
数据库PostrageSQL-连续归档和时间点恢复(PITR)
在任何时间,PostgreSQL在数据集簇目录的pg_wal/子目录下都保持有一个预写式日志(WAL)。这个日志存在的目的是为了保证崩溃后的安全:如果系统崩溃,可以“重放”从最后一次检查点以来的日志项来恢复数据库的一致性。该日志的存在也使得第三种备份数据库的策略变得可能:我们可以把一个文件系统级别的备份和WAL文件的备份结合起来。当需要恢复时,我们先恢复文件系统备份,然后从备份的WAL文件中重放来把系统带到一个当前状态。这种方法比之前的方法管理起来要更复杂,但是有其显著的优点:
cwl_java
2020/12/22
1.1K0
推荐阅读
相关推荐
进阶数据库系列(二十三):PostgreSQL 性能优化
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档