COPY 是如何工作的，为什么它比 INSERT 快得多？

Question

今天我花了一整天的时间来改进将数据推送到我的 Postgres 数据库中的 Python 脚本的性能。我以前是这样插入记录的：

 query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
    cursor.execute(query, d)

然后我重新编写了我的脚本，以便它创建一个内存文件而不是用于 Postgres 的 COPY 命令，它允许我将数据从文件复制到我的表：

 f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f)

COPY 方法 快得惊人。

 METHOD      | TIME (secs)   | # RECORDS
=======================================
COPY_FROM   | 92.998    | 48339
INSERT      | 1011.931  | 48377

但是我找不到任何关于为什么的信息？它与多行 INSERT 不同，从而使它变得更快？

也请参阅 此基准：

 # original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert

#  10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert

# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert

# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert

# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert

原文由 turnip 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这里有许多因素在起作用：

• 网络延迟和往返延迟
• PostgreSQL 中的每条语句开销
• 上下文切换和调度程序延迟
• COMMIT 成本，如果人们每次插入都做一次提交（你不是）
• COPY -针对批量加载的特定优化

网络延迟

如果服务器是远程的，您可能要“支付”每条语句的固定时间“价格”，例如 50 毫秒（1/20 秒）。对于某些云托管的数据库，或者更多。由于下一个插入要等到最后一个插入成功完成后才能开始，这意味着您的 最大 插入速率为每秒 1000/round-trip-latency-in-ms 行。延迟为 50 毫秒（“ping 时间”），即每秒 20 行。即使在本地服务器上，这种延迟也不是零。 Wheras COPY 只填充 TCP 发送和接收窗口，并以数据库可以写入它们并且网络可以传输它们的速度流式传输行。它受延迟的影响不大，并且可能每秒在同一网络链接上插入数千行。

PostgreSQL 中的每条语句成本

在 PostgreSQL 中解析、规划和执行语句也有成本。它必须锁定、打开关系文件、查找索引等 COPY 尝试在开始时将所有这些都做一次，然后只专注于尽可能快地加载行。

任务/上下文切换成本

由于操作系统必须在您的应用程序准备和发送行时等待 postgres 之间切换，然后您的应用程序在 postgres 处理该行时等待 postgres 的响应，因此需要支付更多的时间成本。每次从一个切换到另一个，都会浪费一点时间。当进程进入和离开等待状态时，可能会浪费更多时间来挂起和恢复各种低级内核状态。

错过 COPY 优化

最重要的是， COPY 有一些优化可以用于某些类型的负载。例如，如果没有生成键并且任何默认值都是常量，它可以预先计算它们并完全绕过执行器，将数据快速加载到较低级别的表中，从而完全跳过 PostgreSQL 的部分正常工作。如果你 CREATE TABLE 或 TRUNCATE 在同一笔交易中你 COPY ，它可以通过绕过负载的记账技巧来更快地完成正常交易在多客户端数据库中。

尽管如此，PostgreSQL 的 COPY 仍然可以做更多的事情来加快速度，这是它还不知道如何做的事情。如果您更改表的一定比例以上，它可以自动跳过索引更新然后重建索引。它可以批量进行索引更新。还有很多。

承担成本

最后要考虑的一件事是提交成本。这对你来说可能不是问题，因为 psycopg2 默认打开一个事务并且在你告诉它之前不提交。除非你告诉它使用自动提交。但对于许多数据库驱动程序来说，自动提交是默认设置。在这种情况下，您将为每个 INSERT 执行一次提交。这意味着一次磁盘刷新，服务器确保将内存中的所有数据写到磁盘上，并告诉磁盘将它们自己的缓存写到持久存储中。这可能需要 _很长时间_，并且因硬件而异。我的基于 SSD 的 NVMe BTRFS 笔记本电脑每秒只能执行 200 次 fsync，而每秒可执行 300,000 次非同步写入。所以它只会加载 200 行/秒！某些服务器每秒只能执行 50 个 fsync。有些可以做20,000。因此，如果您必须定期提交，请尝试分批加载和提交，进行多行插入等。因为 COPY 只做一次提交，提交成本可以忽略不计。但这也意味着 COPY 无法从数据中途的错误中恢复；它撤消了整个批量负载。

原文由 Craig Ringer 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

复制使用批量加载，这意味着它每次插入多行，而简单插入一次插入一行，但是您可以使用 insert 插入多行，语法如下：

 insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)

有关使用批量加载的更多信息，请参阅 Daniel Westermann 在 postgresql 中加载 1m 行的最快方法。

一次应该插入多少行的问题取决于行的长度，一个好的经验法则是每个插入语句插入 100 行。

原文由 rachid el kedmiri 发布，翻译遵循 CC BY-SA 3.0 许可协议