现在很多企业都在逐步将数据库从Mysql迁移到更强大开源的PostgreSQL数据库。在数据迁移过程中，PostgreSQL数据库导入大量数据非常缓慢。在本文中，我们将谈谈PostgreSQL数据库批量导入数据的优化方法和策略。概述考虑到PostgreSQL数据库在批量导入数据时性能变慢的原因，无外乎几个因素：索引、触发器、外键、GUID主键，可能还有预写日志（WAL）。我们从这几个影响因素开始优化。当然，也有可能本文提到的技术都没有效果。遇到此类问题，需要具体问题具体分析，针对性解决。关闭日志对于PostgreSQL9.5及以上，可以先将目标表改成UNLOGGED，加载完数据后再改回LOGGED：ALTERTABLESETUNLOGGEDALTERTABLELOGGEDUNLOGGED模式可以保证PostgreSQL在变量导入数据时不将表写操作记录到预写日志（WAL）中，从而大大优化导入过程。但是，由于不记录操作，因此如果在加载过程中发生崩溃或服务器关闭等故障，则无法恢复数据。PostgreSQL将在重新启动后自动截断所有未记录的表。此外，未记录的表不会复制到备用服务器。在这种情况下，现有的复制必须在加载之前删除并在加载之后重新创建。根据主节点的数据量和备用数据库的数量，重建复制的时间可能会很长，这对于高可用性要求是不可接受的。建议使用以下方法将数据批量插入到未记录的表中：在将表更改为未记录模式之前备份表和数据；数据加载完成后，重新创建到备用服务器的任何复制；使用UNLOGGED批量插入的表（例如，大型查找表或维度表）。删除索引数据库索引可能会导致批量数据插入期间出现严重延迟。因为添加数据的过程，需要实时更新相应的索引条目。建议在开始批量插入之前尽可能删除目标表中的索引，并在导入完成后重新创建索引。此外，在大型表上创建索引可能很耗时，但它比在加载期间更新索引要快。DROPINDEX,...<批量导入数据...>CREATEINDEXON(column1,...,column)在创建索引之前，临时增加可能会有帮助maintenance_work_mem配置参数。增加工作内存有助于更快地创建索引。另一个安全的选择是使用现有数据和索引在同一数据库中复制目标表。然后，测试批量导入数据有无索引的性能对比，再根据测试结果选择更好的方法。删除外键与索引一样，外键约束也会影响批量导入的性能。因为导入过程中必须检查插入的每一行数据的每个外键是否有对应的主键。批量导入时，必须为每一行触发此触发器以检查外键，从而增加了开销。除非受到业务规则的限制，否则建议先将目标表的外键全部drop掉，在一个事务中加载数据，事务提交后重新创建外键。ALTERTABLEDROPCONSTRAINTBEGINTRANSACTION<批量导入数据...>COMMITALTERTABLEADDCONSTRAINTFOREIGNKEY()REFERENCES()...添加maintenance_work_mem配置参数也可以提高re创建外键约束的性能。暂停触发器INSERT或DELETE触发器（如果导入过程还涉及从目标表中删除记录）可能会导致批量数据导入延迟。这是因为每个触发器都有需要在插入或删除每一行时立即检查和完成的逻辑。建议在批量导入数据前禁用目标表中的所有触发器，并在导入完成后启用它们。禁用所有触发器还会强制系统触发器进行外键约束检查。ALTERTABLEDISABLETRIGGERALL<批量导入数据...>ALTERTABLEENABLETRIGGERALL使用多值INSERT对于批量数据加载，运行数千或数十万条INSERT语句可能是一个糟糕的选择。因为查询优化器必须解析和准备每个单独的INSERT命令，然后进行所有约束检查，作为单独的事务运行并记录它。使用具有多个值的单个INSERT语句可以节省这些不必要的开支。插入(,,...,)VALUES(,,...,),(,,...,),(,,...,),(,,...,),...多值INSERT性能受以下因素影响现有索引。建议在运行命令之前删除索引，然后再创建索引。另一点需要注意的是PostgreSQL可用于运行多值INSERT的内存量。在运行多值INSERT时，大量的输入值必须放入RAM中，除非有足够的内存可用，否则该过程可能会失败。建议设置effective_cache_size参数为50%，shared_buffer参数设置为机器总内存的25%。为了安全起见，将导入分成多个多值INSERT，每个不超过1000行值。使用COPY命令建议使用PostgreSQLCOPY命令从一个或多个文件导入数据。COPY还针对批量数据导入进行了优化，并且比运行大量INSERT语句甚至多值INSERTS更快。COPY[(column1>,...,)]FROM''WITH(,,...,)使用COPY有很多优点：支持文本和二进制文件导入；具有交易性质；它允许指定输入文件的结构；它可以使用WHERE子句有条件地导入数据。运行ANALYZ这与提高批量数据导入性能无关，但强烈建议在批量导入后立即对目标表运行ANALYZE命令。大量新导入的行会大大改变数据表列中的数据分布，使表的统计信息过时。当过时的统计信息与查询优化器一起使用时，查询性能可能会非常慢。运行ANALYZE命令将确保更新统计信息。小结对于数据库应用来说，批量数据导入可能不会每天都做，但在运行时会对查询性能产生影响。这就是为什么有必要尽可能缩短导入时间的原因。DBA可以做的一件事是在具有类似服务器规格和PostgreSQL配置的开发或在线环境中执行性能测试和优化，以最大程度地减少意外。每个数据加载场景都不同，最好尝试每种方法，找到最好最快的方法。
