江苏龙网

  1. 首页 > 资讯 > 科技 > >

|PostgreSQL中数据批量导入优化方法


现在很多企业都将数据库逐渐由Mysql转向了更加强大而且开源的PostgreSQL数据库 。 在数据迁移过程中 ， PostgreSQL数据库导入大量数据时候非常缓慢 ， 本文我们就来说说PostgreSQL数据库批量导入数据时的优化方法和策略 。
|PostgreSQL中数据批量导入优化方法
本文插图

概述
考虑PostgreSQL数据库批量导入数据时性能缓慢的原因 ， 无非有几个因素：索引 ， 触发器 ， 外键 ， GUID主键 ， 还有可能是预写日志（WAL） 。 我们就从这几个影响因素着手优化 。 当然有可能 ， 本文说的这些技巧都不能有效问题 ， 遇到这样的问题时候 ， 就需要我们具体问题具体分析 ， 并针对性的解决 。
关闭日志记录
对于PostgreSQL 9.5及更高版本 ， 可以先将目标表更改为UNLOGGED ， 然后在加载数据后将其更改回LOGGED：
ALTER TABLESET UNLOGGEDALTER TABLELOGGEDUNLOGGED模式可以确保PostgreSQL不会在变量导入数据时将表写操作记录到预写日志（WAL） ， 从而极大的优化导入过程 。 但是 ， 由于未记录操作 ， 因此如果在加载过程中发生崩溃或服务器关机等故障 ， 则无法恢复数据 。 PostgreSQL重新启动后将自动截断任何未记录的表 。
|PostgreSQL中数据批量导入优化方法
本文插图

另外 ， 未记录的表不会复制到备用服务器 。 在这种情况下 ， 必须在加载之前删除现有的复制 ， 并在加载之后重新创建 。 根据主节点中的数据量和备用数据库的数量 ， 重建复制的时间可能会很长 ， 对于高可用性要求来说这是不可接受的 。
建议采用以下方法 ， 将数据批量插入未记录的表中：
在将表和数据更改为未记录模式之前对其进行备份；
数据加载完成后 ， 重新创建对备用服务器的任何复制；
对可以轻松重新填充的表使用UNLOGGED的批量插入（例如 ， 大型查找表或维度表） 。
删除索引
数据库索引可能在批量数据插入期间导致严重的延迟 。 因为添加数据过程 ， 对应的索引条目需要实时更新 。
建议在开始批量插入之前尽可能删除目标表中的索引 ， 并在导入完成后重新创建索引 。 同样 ， 在大型表上创建索引可能很耗时 ， 但是比在加载过程中更新索引要快 。
DROP INDEX , … CREATE INDEX ON (column1, …,column n)创建索引之前 ， 临时提高maintenance_work_mem配置参数可能会有帮助 。 增加的工作内存可以帮助更快地创建索引 。
为了安全起见的另一种选择是使用现有数据和索引在同一数据库中复制目标表 。 然后 ， 测试有索引和删除索两种情况下批量导入数据的性能对比 ， 然后根据测试结果选择更好的方法 。
删除外键
和索引一样 ， 外键约束也会影响大批量导入的性能 。 因为导入过程中必须检查插入的每个行数据的每个外键是否存在相应的主键 。 当批量导入时 ， 必须为每一行触发该触发器检查外键 ， 从而增加了开销 。
除非受到业务规则的限制 ， 否则建议先从目标表中删除所有外键 ， 在单个事务中加载数据 ， 然后在提交事务后重新创建外键 。
ALTER TABLEDROP CONSTRAINT BEGIN TRANSACTIONCOMMIT ALTER TABLEADD CONSTRAINT FOREIGN KEY () REFERENCES ()...同样增加maintenance_work_mem配置参数也能提高重新创建外键约束的性能 。
暂停触发器
INSERT或DELETE触发器（如果导入过程还涉及从目标表中删除记录）可能会导致批量数据导入延迟 。 这是因为每个触发器将具有需要检查的逻辑 ， 并且需要在每行被插入或删除后立即完成操作 。


推荐阅读


上一篇：收麦科技|同仁堂品牌信用危机，因生产销售劣药，已被罚33万！！

下一篇：|明日继续回暖 别错过！22时18分！就在今晚