PostgreSQL：批量加载大量数据

批量加载是将大量数据导入 PostgreSQL 数据库的最快方法。大规模导入方式多种多样，也有许多不同的方法来导入大量数据。这篇文章将向您展示如何使用其中一些技巧，并解释导入的速度有多快。您可以使用这些知识来优化数据仓库或任何其他数据密集型工作负载。

为了加速使用 PostgreSQL 批量加载大量数据，需要考虑几件事：

• 插入与复制

• 优化检查点

• 记录表与未记录表

• 重新创建索引

• 启用和禁用触发器

• 改善列顺序和空间消耗

让我们更详细地看看这些事情。

插入与复制

首先要考虑的是 COPY 通常比普通插入要好很多。原因是 INSERT 有很多开销。人们经常会问：有什么样的开销？是什么让 COPY 比 INSERT 快得多？原因有很多种：在INSERT的情况下，每条语句都要检查锁，检查表和表中的列是否存在，检查权限，查找数据类型等等。在 COPY 的情况下，这只完成一次，这要快得多。每当你想写入大量数据时，数据复制通常是要走的路。

为了说明这种变化对性能有什么样的影响，我编写了一个简短的示例。让我们创建一个表以及一些示例数据：

test=# CREATE TABLE t_sample
(
     a              varchar(50),
     b              int,
     c              varchar(50),
     d              int
);
CREATE TABLE
复制

示例表由 4 列组成，非常简单。在下一步中，我们将在单个事务中编译包含 100 万条 INSERT 语句的脚本：

BEGIN;
INSERT INTO t_sample VALUES ('abcd', 1, 'abcd', 1);
INSERT INTO t_sample VALUES ('abcd', 1, 'abcd', 1);
INSERT INTO t_sample VALUES ('abcd', 1, 'abcd', 1);
…
COMMIT;
复制

可以使用 psql 运行脚本：

iMac:~ hs$ time psql test < /tmp/sample.sql > /dev/null
 
real 1m20.883s
user 0m11.515s
sys 0m10.070s
复制

我们需要大约 81 秒来运行这个简单的测试，这是很多时间。因此，单个 INSERT 语句显然不是执行快速导入和高效批量加载的解决方案。

正如我已经提到的，COPY 比 INSERT 效率高得多，所以让我们使用相同的数据，但将其提供给 COPY；

COPY t_sample FROM stdin;
abcd 1 abcd 1
abcd 1 abcd 1
abcd 1 abcd 1
abcd 1 abcd 1
…
\.
复制

运行脚本又是一件容易的事：

iMac:~ hs$ time psql test < /tmp/bulk.sql > /dev/null
 
real 0m2.646s
user 0m0.110s
sys 0m0.043s
复制

哇，速度从 81 秒提高到了 2.6 秒：

请记住，我在一台相当旧的机器上执行了这个测试，在一个完全未调整的数据库上。在现代硬件和更现代的操作系统上，可以实现比我的本地 iMac 台式机更多的功能。加载 100 万行或更多行在现实世界中并不少见。当然，这些数据取决于“记录”的长度等等。但是，重要的是要了解什么是可能的，什么是不可能的。

注意：运行时可能会有所不同。这有很多原因。其中之一肯定与这里使用的硬件有关。我们已经看到许多 SSD 为我们提供了相当不稳定的响应时间。

调整检查点以加快批量加载

PostgreSQL 配置确实对批量加载性能有影响。有许多配置参数对数据库性能至关重要，尤其是加载。但是，我明确希望您将注意力集中在检查点和 I/O 性能上。如果要加载数十亿行，I/O 为王。有多种角度来探讨这个话题：

• 减少写入的数据量

• 尽可能缩小表格（列顺序）

• 减少 WAL 写入量

• 更高效地写入数据

• 更长的检查站距离

• 更好的 I/O 调度

以下设置很重要：

• max_wal_size：要创建的 WAL 的最大数量（软限制）

• checkpoint_completion_target：控制检查点行为

一般来说，将检查点延长很多是一个非常好的主意。在大容量负载密集型工作负载的情况下将此值设置为 100 或 200 GB 绝对不会超出范围。

请记住，增加检查点距离不会使您的服务器处于危险之中。它只影响 PostgreSQL 写入数据的方式。另请记住，如果发生崩溃，将消耗更多磁盘空间并且恢复可能需要更长的时间。

如果您想了解有关检查点的更多信息，请查看这篇关于减少 WAL 编写量的文章。

创建表与创建未记录表

但是，如果有办法完全摆脱 WAL 怎么办？嗯，有一个。它被称为“未记录表”。总体思路是什么？通常我们会得到以下事件序列：

• 将大量数据加载到 PostgreSQL（“暂存区”）中

• 执行一些聚合

• 删除初始导入

这是使用未记录表提供的 WAL 绕过的理想方案：

test=# DROP TABLE t_sample ;
DROP TABLE
test=# CREATE UNLOGGED TABLE t_sample
(
     a            varchar(50),
     b            int,
     c            varchar(50),
     d            int
);
CREATE TABLE
test=# \d t_sample
Unlogged table "public.t_sample"
Column  | Type                  | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
a       | character varying(50) |           |          |
b       | integer               |           |          |
c       | character varying(50) |           |          |
d       | integer               |           |          |
复制

让我们再次加载相同的数据：

iMac:~ hs$ time psql test < /tmp/sample.sql > /dev/null
 
real 0m59.296s
user 0m10.597s
sys 0m9.417s
 
iMac:~ hs$ time psql test < /tmp/bulk.sql > /dev/null
 
real 0m0.618s
user 0m0.107s
sys 0m0.038s
复制

如您所见，整个过程要快得多。81 秒对 59 秒和 2.6 对 0.6 秒。差异是巨大的。

原因是未记录的表不必两次写入数据（不需要 WAL）。但是，它附带了一个价格标签：

• 在正常关闭的情况下，未记录的表就像普通表一样

• 如果发生崩溃，未记录的表保证为空

• 未记录表的内容不会被复制

这些限制意味着未记录的表不适合存储“正常”数据。然而，它是集结区和散装装载的理想选择。

表可以被记录和取消记录。许多人认为这些是廉价的操作，但事实并非如此。让我们来看看会发生什么：

test=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
5/3AC7CCD0
(1 row)
 
test=# ALTER TABLE t_sample SET LOGGED;
ALTER TABLE
 
test=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
5/3F9048A8
(1 row)
复制

除了将表从 UNLOGGED 设置为 LOGGED 之外，我还测量了当前的 WAL 位置。我们可以看到，已经写入了很多数据：

test=# SELECT '5/3F9048A8'::pg_lsn - '5/3AC7CCD0'::pg_lsn;
?column?
-----------
80247768
(1 row)
 
Time: 11.298 ms
复制

哇，我们已经生成了 80 MB 的 WAL（如果你在一张空表上只做一个 COPY——如果你运行更多的导入，这个数量会增加）。在 COPY + INSERT 的情况下，容量会高很多。

由此，我们得出的结论是，如果我们想要进行高效的批量加载，将表从 LOGGED 设置为 UNLOGGED，导入数据并将其设置回 LOGGED 可能不是所有想法中最好的——因为一旦设置了表回到 LOGGED，表的全部内容必须发送到 WAL，以确保副本可以接收表的内容。

直接导入与重新创建索引

test=# SELECT count(*) FROM t_sample;
count
---------
1000001
(1 row)
 
test=# CREATE TABLE t_index (LIKE t_sample);
CREATE TABLE
 
test=# CREATE INDEX idx_a ON t_index (a);
CREATE INDEX
test=# CREATE INDEX idx_b ON t_index (b);
CREATE INDEX
 
test=# \timing
Timing is on.
test=# INSERT INTO t_index SELECT * FROM t_sample;
INSERT 0 1000001
Time: 8396.210 ms (00:08.396)
复制

复制数据大约需要 8 秒。让我们稍后通过创建索引来尝试同样的事情：

test=# CREATE TABLE t_noindex (LIKE t_sample);
CREATE TABLE
test=# INSERT INTO t_noindex SELECT * FROM t_sample;
INSERT 0 1000001
Time: 4789.017 ms (00:04.789)
test=# SET maintenance_work_mem TO '1 GB';
SET
Time: 13.059 ms
test=# CREATE INDEX idx_aa ON t_noindex (a);
CREATE INDEX
Time: 1151.521 ms (00:01.152)
test=# CREATE INDEX idx_bb ON t_noindex (b);
CREATE INDEX
Time: 1086.972 ms (00:01.087)
复制

我们可以看到复制过程（= INSERT）比以前快了很多。总的来说，稍后生成索引会更快。还要记住，我在这里使用的是 Mac OSX 上的合成数据（效率不是很高）。如果您使用更多真实数据重复测试，则差异会更大。

底线是：

如果可能，在导入数据后创建索引。

启用触发器与禁用触发器

触发器也是一个重要因素。可以说触发器是批量加载性能的“天敌”。让我们看一下下面的例子：

iMac:~ hs$ head -n 20 /tmp/bulk.sql
BEGIN;
 
CREATE FUNCTION dummy()
RETURNS trigger AS
$$
    BEGIN
       NEW.b := NEW.b + 1;
       NEW.d := NEW.d + 1;
       RETURN NEW;
    END;
$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER mytrig BEFORE INSERT ON t_sample
FOR EACH ROW EXECUTE PROCEDURE dummy();
 
COPY t_sample FROM stdin;
abcd 1 abcd 1
abcd 1 abcd 1
abcd 1 abcd 1
abcd 1 abcd 1
复制

我们的触发器非常简单。它所做的只是修改我们数据中的两个条目。但是，触发器将为每一行添加一个额外的函数调用，这确实加起来了。在我们的例子中，我们得到了以下数据：触发器的变化慢了大约 3 倍。然而，真正的区别在很大程度上取决于触发器的复杂性、行的大小等等。没有办法说“触发器会使事情减慢 X 倍”。必须逐案查看会发生什么。

优化批量加载的列顺序

将大量数据导入 PostgreSQL 的过程远比想象的要多。到目前为止，我们已经优化了检查点、触摸索引、触发器等。但是列顺序呢？让我们试着找出答案。

在 PostgreSQL 中，列顺序确实有很大的不同。将“固定长度”列放在前面通常是个好主意。换句话说：int8、int4、timestamptz 等应该在表的开头。varchar、text 等可变长度数据类型应放在表的末尾。原因是 CPU 对齐是磁盘上的一个问题。这适用于普通堆表（不适用于zheap）。

在不更改内容的情况下缩小表的大小可以加快速度，因为它有助于避免或减少批量加载数据时的关键瓶颈之一：I/O。查看这篇文章以了解更多信息。

批量装载工具

如果到目前为止您所看到的还不够，我们可以推荐一些工具来进一步改进批量加载。可以推荐以下工具：

• 加载程序

• pg_bulkload

这两种工具都是众所周知的并且被广泛使用。您可以安全地使用它们。

如果您对这些工具还有其他疑问，请随时在评论部分提问，或给我们发送电子邮件。

最后 …

如果您想了解有关PostgreSQL 性能的更多信息，我们还建议您查看我们的咨询服务。我们帮助您调整数据库并确保您的服务器运行良好。