今天，我花了一天时间来改进我的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)
该 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
这里有许多因素在起作用：
- 网络延迟和往返延迟
- PostgreSQL中的每语句开销
- 上下文切换和调度程序延迟
COMMIT 成本，如果每个插入一次提交的人（你不是）COPY - 批量加载的特定优化
网络延迟
如果服务器是远程的，您可能正在“支付”每个语句的固定时间“价格”，例如50ms（1/20秒）。或者对于某些云托管DB更多。由于下一个插入无法开始，直到最后一个成功完成，这意味着您的 最大 插入速率为每秒1000次/往返延迟（以毫秒为单位）。延迟为50毫秒（“ping时间”），即20行/秒。即使在本地服务器上，此延迟也是非零的。 Wheras 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 fsyncs /秒，而300,000非同步写入/秒。所以它只能加载200行/秒！有些服务器只能执行50 fsyncs /秒。有些可以做到20,000。因此，如果您必须定期提交，请尝试批量加载和提交，执行多行插入等。因为 COPY 只有一个提交最后，提交成本可以忽略不计。但这也意味着 COPY 无法通过数据中途从错误中恢复;它解除了整个批量负荷。
复制使用批量加载，这意味着它每次都插入多行，而简单的插入，一次只插入一行，但是您可以按照语法插入多行插入：
insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)
有关使用批量加载的更多信息，请参阅例如 Daniel Westermann在postgresql中加载1m行的最快方法。
你应该一次插入多少行的问题，取决于行长度，一个好的经验法则是每个插入语句插入100行。
在事务中执行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