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

Question

今天我花了一整天的时间来提高我的Python脚本性能，该脚本用于将数据推送到我的Postgres数据库中。之前我是这样插入记录的：

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)

< p > COPY 方法速度惊人地快。 < /p >

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

---

“Much faster”是什么意思？请提供一些基准测试数据。 - Willem Van Onsem

插入：1011.93秒| 复制：92.99秒。我的插入有一些使其变慢的东西，但是我见过的每个人都报告了巨大的改进。例如，请参见此处：https://gist.github.com/jsheedy/efa9a69926a754bebf0e9078fd085df6 - turnip

每个INSERT语句都会执行一个隐式事务。我很好奇COPY是否处理它们的方式不同。 - Kyle

复制是一个事务，没有 begin;commit; 将它们包装起来的单个插入是单独的事务。使用复制时，一个不良值会导致整个操作失败。使用自动提交的单个事务时，一个不良值意味着一个值失败。您可以使用多行插入（例如：insert into table values(a,b,c),(d,e,f),(g,h,i)...(x,y,z)）实现接近于复制速度的性能。 - Scott Marlowe

@Kyle 你确定吗？ psycopg2 默认情况下不自动提交，第一条语句会开启一个事务并保持打开状态直到显式提交。通常情况下你是对的，但对于 Python 来说不一定如此。 - Craig Ringer

Answer 1

这里有几个因素在起作用：

• 网络延迟和往返时延
• PostgreSQL中的每个语句开销
• 上下文切换和调度延迟
• COMMIT 成本，如果每次插入都进行一次提交(您不需要)
• 针对批量加载的 COPY 特定优化

网络延迟

如果服务器是远程的，您可能需要支付每个语句固定时间“价格”，例如50ms(1/20秒)。或者对于某些云托管的DB，则更高。由于下一个插入只能在最后一个成功完成之后开始，这意味着您的最大插入速率是1000/往返延迟(以毫秒为单位)行每秒。在50ms("ping时间")的延迟下，这是每秒20行。即使在本地服务器上，此延迟也不为零。而 COPY 只填充 TCP 的发送和接收窗口，并尽可能快地流式传输行，直到 DB 完成写入并且网络传输完成。它不会受到延迟的太多影响，并且可能在同一网络链路上每秒插入数千行。

PostgreSQL中的每个语句成本

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

任务/上下文切换成本

由于操作系统必须在 postgres 等待行而您的应用程序准备并发送该行之间进行切换，并且您的应用程序在等待 postgres 响应 postgres 处理行时，还要支付进一步的时间成本。每次从一个进程切换到另一个进程时，都会浪费一点时间。当进程进入和退出等待状态时，还可能浪费各种低级内核状态的暂停和恢复时间。

错过了 COPY 优化

除此之外，COPY 还有一些优化可用于某些类型的加载。例如，如果没有生成的键并且任何默认值都是常量，则它可以预先计算这些值并完全绕过执行器，将数据快速加载到表中，跳过 PostgreSQL 的正常工作的部分。如果你在同一事务中进行 CREATE TABLE 或 TRUNCATE 并且你使用 COPY，则可以通过绕过多客户端数据库中需要的正常事务记录来进行更多的技巧，以加快加载速度。

尽管如此，PostgreSQL 的 COPY 仍然可以做更多的工作来加速，它还不知道如何做的事情。例如，如果你更改表的比例超过一定的比例，则它可以自动跳过索引更新然后重建索引。它可以按批次进行索引更新，等等。

提交成本

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

- Craig Ringer

Answer 2

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

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

有关使用批量加载的更多信息，请参考例如Daniel Westermann的《在Postgresql中加载100万行的最快方法》。

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

- rachid el kedmiri

---

虽然多插入是单个插入的优化，但\COPY命令针对大型多插入进行了优化，通常比多插入更快，操作上也更复杂。 - mgoldwasser

@mgoldwasser，我想说的是我正在寻找单个插入 vs 多行插入 vs COPY 命令之间的比较，很高兴看到你的答案。有没有可以了解这方面的来源或基准测试呢？ - addicted

Answer 3

为加快速度，请在事务中执行INSERT操作。

如果不使用事务，在bash中进行测试：

>  time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c
 100000 INSERT 0 1

real    0m15.257s
user    0m2.344s
sys     0m2.102s

使用事务：

> time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c
      1 BEGIN
 100000 INSERT 0 1
      1 COMMIT

real    0m7.933s
user    0m2.549s
sys     0m2.118s

- OBi