后端开发教程 - Java、Spring Boot 实战 - msg200.com
使用 COPY 加速 PostgreSQL 批量插入
您是否曾遇到过将数据从一个数据库迁移到另一个数据库？甚至从一个表迁移到另一个表？或者一次性执行过多的 INSERT 操作？如果是这样，那么本文正适合您：今天我们将讨论 COPY 命令。COPY 命令是执行密集数据库写入操作的绝佳替代方案，对于数据迁移导入和批量插入非常有用。
“我必须在 X 秒内插入 1mi 行”
在几秒钟内迁移/插入数据是一项非常常见的任务需求。当人们听到这个需求时，他们首先会对你说：
- “使用分布式处理工具插入数据库”
- “为数据库添加更多 CPU 和 RAM”
- “使用缓存”
- “改用 NoSQL 数据库”
- “分解成微服务，并在数据库前面放置一个 Apache Kafka 代理”
这真的有必要吗？也许没有必要。很多情况下，像 COPY 命令这样的迁移工具比上述选项要快得多，而且在基础设施方面也更便宜。COPY 是 PostgreSQL 的原生命令，其语法如下：
COPY table_name (column1, column2, ...) FROM '/path/to/data.csv';
或者
COPY table_name (column1, column2, ...) FROM STDIN;
PostgreSQL 中的命令COPY是执行批量插入和数据迁移的强大工具。它允许您快速高效地将大量数据插入表中。
通过使用该COPY命令，您可以避免使用分布式处理工具、为数据库添加更多 CPU 和 RAM，或使用 NoSQL 数据库。它为批量插入和数据迁移提供了更简单、更经济高效的解决方案。
因此，如果您的任务需要在短时间内插入大量行，请考虑使用COPYPostgreSQL 中的命令。它可以显著加快您的数据迁移和导入过程。
注意：PostgreSQL 16 将 COPY 的性能提高了 300% 以上。
COPY 内部如何运作？
在 PostgreSQL内部，该COPY命令利用服务器的文件系统来工作。当您使用该COPY命令从文件导入数据时，PostgreSQL 会直接从文件系统读取文件，并将数据直接写入表，从而绕过许多常规的 SQL 处理步骤。
与传统语句相比，这种直接从文件到表的传输方式可以实现更快、更高效的数据插入INSERT。它还能减少数据库服务器的开销，使其成为批量插入和数据迁移的首选方法。
但让我们更深入地了解所有细节。
COPY 与 INSERT（多行）
让我们快速比较一下 COPY 和 INSERT 命令，以了解为什么 COPY 性能更好。
| 复制 | 插入（多行） | |
|---|---|---|
| 日志记录 | 一根原木承载全部负载 | 每行/条目一个日志 |
| 网络 | 无延迟，数据流式传输 | 插入之间的延迟 |
| 解析 | 仅需一次解析操作 | 解析开销 |
| 交易 | 单笔交易 | 每个插入语句都是一个单独的事务 |
| 查询计划 | 更简单的查询执行计划 | 许多不同的查询执行计划 |
总而言之，COPY 速度更快，因为与多行 INSERT 语句相比，它减少了日志记录、网络延迟、解析和事务管理的开销。它允许更简单的查询执行计划，从而实现更快、更高效的批量插入和数据迁移。缺点是它需要直接访问文件系统，因此可能并不适合所有需要插入数据的场景。另一个缺点是持久性，COPY 生成的日志很少，并且所有操作都在一个事务中执行，这使得它的风险更高。
衡量绩效
我用 Bun（没错，就是 Bun）写了一些代码，比较了 INSERT 和 COPY 两种方式加载大量数据到 Postgres 的性能。我们来看看：
import { Client } from 'pg';
import fs from 'fs';
import { from as copyFrom } from 'pg-copy-streams';
import { pipeline } from 'node:stream/promises';
const client = new Client({
host: 'localhost',
port: 6472,
user: 'copy-insert-db',
password: 'copy-insert-db',
database: 'copy-insert-db'
});
const numRecords = 2000000;
const data = Array.from({ length: numRecords }, (_, i) => [`data_${i}`, new Date().toISOString()]);
async function measureInsert() {
const chunkSize = 1000;
const startTime = Date.now();
for (let i = 0; i < data.length; i += chunkSize) {
const chunk = data.slice(i, i + chunkSize);
const placeholders = chunk.map((_, index) => `($${index * 2 + 1}, $${index * 2 + 2})`).join(',');
const values = chunk.flat();
const insertQuery = `
INSERT INTO test_table (data, time_added) VALUES ${placeholders}
`;
await client.query(insertQuery, values);
}
const duration = (Date.now() - startTime) / 1000;
console.log(`Multi-line INSERT took: ${duration} seconds`);
}
async function measureCopy() {
return new Promise<void>((resolve, reject) => {
const writableStream = fs.createWriteStream("data.csv");
writableStream.on("finish", async () => {
try {
const startTime = Date.now();
const stream = fs.createReadStream("data.csv");
const copyPsqlStream = client.query(copyFrom('COPY test_table (data, time_added) FROM STDIN WITH (FORMAT CSV)'));
await pipeline(stream, copyPsqlStream);
const duration = (Date.now() - startTime) / 1000;
console.log(`COPY took: ${duration} seconds`);
resolve();
} catch (err) {
reject(err);
}
});
data.forEach(d => writableStream.write(d.join(',') + '\n'));
writableStream.end();
});
}
async function main() {
await client.connect();
await measureInsert();
await measureCopy();
await client.end();
}
main();
这段代码一次性将 2mi 行插入数据库。请注意，对于多行插入，每个 INSERT 语句最多只能插入 1000 行。运行这段代码，结果如下：
多行 INSERT 耗时：37.187 秒
COPY 耗时：10.088 秒
加速（5英里记录）
更改代码以加载 500 万条记录，我们得到以下结果：
多行 INSERT 耗时：78.957 秒
COPY 耗时：20.534 秒
加速（10英里记录）
现在，有了 10 英里的记录，我们得到：
多行 INSERT 耗时：134.412 秒
COPY 耗时：36.965 秒
我们可以看到 COPY 命令的速度快得惊人，以及为什么在某些情况下我们应该考虑使用它来代替 INSERT。但我们可以通过线程并行化，让 COPY 变得更快！
使用多线程提高 COPY 性能
对于我们用 200 万条记录进行的初步测试，我们有：
复制耗时：5.915 秒
加速（5英里记录）
我们要插入 500 万条记录的并行 COPY 操作执行如下：
复制耗时：15.41秒
加速（10英里记录）
我们要插入 1000 万条记录的并行 COPY 操作执行如下：
复制耗时：21.19秒
最终结果
我使用运行 Matplotlib 的笔记本生成的简单图表进行了性能比较。结果如下：
这张图清晰地展示了 COPY 命令相对于 INSERT 命令在这一特定任务中的卓越效率。速度上的差异非常显著。