您有一个文件，可能是一个很大的CSV，并且您想将其内容导入数据库。有很多选项可以执行此操作，但是您将如何决定使用哪个选项。问题经常是散装负载将花费多少时间。我几天前想为PostgreSQL设计数据提取过程时发现自己做了同样的事情，在那里我们需要每24小时从CSV文件批量加载大约250GB的数据。
用于将其批量加载到PostgreSQL的Goto解决方案是本机copy命令。但是copy命令的局限性在于它要求将CSV文件放置在服务器上。因此，我决定对批量加载选项和技术进行简单的比较。
简而言之，我想了解将数据加载到标准表和未记录表中的性能差异，并希望比较加载到具有索引vs drop-index-> load-> recreate-index选项的表中的加载时间差异。
此外，我想看看上述每个选项的COPY命令，客户端复制命令，通过file_fdw加载和pg_bulkload的性能差异。
由于打算在不同的数据加载技术和选项之间进行相对的性能比较，因此使用运行macOS Catalena，16GB RAM，2.7 GHz四核Intel Core i7处理器和500 GB SSD磁盘的个人MacBook Pro足够好达到目的。
对于数据库，我使用默认配置选项从源代码编译了PostgreSQL v12。我将大多数配置参数保留为其默认值，仅更改了以下提到的设置。
出于此练习的目的，我从http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/下载了一个CSV文件示例，其中包含500万行。
为简单起见，我在PostgreSQL中创建了一个sales_record表，并与CSV文件进行了一对一映射
创建表sales_record（区域VARCHAR，国家VARCHAR，item_type VARCHAR，sales_channel VARCHAR，order_priority CHAR，order_date DATE，order_id INT，ship_date DATE，unit_sold INT，unit_price FLOAT，unit_cost FLOAT，total_revenue FLOAT，total_cost FLOAT，total_cost FLOAT，
除此之外，我还想了解拥有索引对大容量性能的影响，因此对于需要INDEX的测试，我在country列上创建了btree索引。
COPY在PostgreSQL表和标准文件系统文件之间移动数据。复制命令有两个变体，COPY TO和COPY FROM。前者将表内容复制到文件中，而我们将使用后者将数据从文件加载到表中。
``\ copy''是运行SQL COPY命令的psql操作，但是psql（client）读取或写入文件并在服务器和本地文件系统之间路由数据，而不是服务器读取或写入指定的文件。这意味着文件可访问性和特权是本地用户（而不是服务器）的文件可访问性和特权，并且不需要SQL超级用户特权。
外部数据包装器file_fdw可用于访问服务器文件系统中的数据文件，或在服务器上执行程序并读取其输出。我们还可以使用file_fdw将数据从CSV加载到PostgreSQL表中。
-创建file_fdw扩展名和外部服务器CREATE EXTENSION file_fdw;创建服务器file_fdw_server FOREIGN DATA WRAPPER file_fdw; -定义指向我们的CSV文件的外部表FLOAT，total_cost FLOAT，total_profit FLOAT）服务器file_fdw_server选项（格式＆＃39; csv＆＃39;标头＆＃39; false＆＃39;，文件名＆＃39; /Users/muhammadusama/work/data/5m_Sales_Records.csv&# 39 ;,定界符＆＃39 ;, null＆＃39;＆＃39;）;-将数据从外部表复制到本地表INSERT INTO sales_record SELECT * from foreign_sales_record;
尽管在加载数据时，file_fdw的预期速度不如COPY命令快，但在加载之前对数据进行预处理时，它提供了很多灵活性和选项。
当涉及到高速数据加载时，pg_bulkload也是一个非常有趣的选项。它是一个开放源代码工具，可通过跳过共享缓冲区和WAL日志记录来实现其性能。
-创建pg_bulkload扩展$ bin / psql -c＆＃34;创建扩展pg_bulkload＆＃34; postgres--创建具有适当内容的控制文件）TYPE = CSV＃输入文件类型QUOTE =＆＃34; \＆＃34;＆＃34; ＃引用字符ESCAPE = \＃引用的转义字符DELIMITER =＆＃34;，＆＃34; ＃分隔符-执行pg_bulkload实用程序$ bin / pg_bulkload -d postgres -h localhost sample_csv.ctl
下图显示了每个工具/命令从CSV文件加载500万行所花费的时间
每种数据加载方法都有自己的优缺点，对于特定的用例，这可能是一个比其他方法更好的选择。但是在原始性能方面，pg_bulkload显然是赢家，COPY和/ copy排在后面，而file_fdw则排在最后。
尽管无论我们使用哪种数据加载方法，加载到索引表中总是很慢，所以当要加载大量数据时，请务必考虑drop-index-> load-> create-index。
所有工具的比较是一次苹果对苹果的比较，客户端和服务器都在同一台机器上运行。因此，/ copy没有网络开销。如果PostgreSQL服务器和客户端在不同的计算机上，则/ copy命令的执行效果可能不及上述结果。
Muhammad Usama是HighGo Software的数据库架构师/ PostgreSQL顾问，也是Pgpool-II核心提交者。 Usama自2006年以来一直从事数据库开发（PostgreSQL），他是开源中间件项目Pgpool-II的核心提交者，并且在驱动和增强产品方面发挥了关键作用。在进行开源开发之前，Usama从事软件设计和开发，主要侧重于系统级嵌入式开发。加入EnterpriseDB（一家企业PostgreSQL的公司）后，他于2006年开始了他的开源事业，特别是在PostgreSQL和Pgpool-II中。他是Pgpool-II项目的主要贡献者，为许多性能和高可用性相关功能做出了贡献。
[…]批量加载到PostgreSQL中：选项和比较首先出现在Highgo Software上[…]
不错的文章和比较。如果您必须每24小时将数据添加到表中，虽然drop-index-> load-> create-index应该不合适。你怎么看？
下次我评论时，请在此浏览器中保存我的姓名，电子邮件和网站。