批量加载是将大量数据导入 PostgreSQL 数据库的最快方法。有多种方法可以促进大规模导入，也可以使用许多不同的扩展方法。这篇文章将向您展示如何使用其中一些技巧，并解释导入的速度有多快。您可以使用这些知识来优化数据仓库或任何其他数据密集型工作负载。
为了加速使用 PostgreSQL 批量加载大量数据，需要考虑以下几点：
- 插入与复制
- 优化检查点
- 已记录与未记录的表
- 重新创建索引
- 启用和禁用触发器
- 改善列顺序和空间消耗
让我们更详细地看看这些事情。
插入与复制
首先要考虑的是 COPY 通常比普通插入要好很多。原因是 INSERT 有很多开销。人们经常问：有什么样的开销？是什么让 COPY 比 INSERT 快得多？原因有多种：在INSERT的情况下，每条语句都要检查锁，检查表和表中的列是否存在，检查权限，查找数据类型等等。在 COPY 的情况下，这仅执行一次，速度要快得多。每当你想写入大量数据时，数据 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 呢？嗯，有一个。它被称为“未记录的表”。一般的想法是什么？通常我们会得到以下事件序列：
- 将大量数据加载到 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位置。我们可以看到写了很多数据：
1
2
3
4
5
6
7
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。查看这篇文章以了解更多信息。
批量装载工具
如果到目前为止您所看到的还不够，我们可以推荐一些工具来进一步改进批量加载。可以推荐以下工具：
这两种工具都非常有名并被广泛使用。您可以安全地使用它们。
如果您对这些工具还有其他疑问，请随时在评论部分提问，或给我们发送电子邮件。
原文链接：https://www.cybertec-postgresql.com/en/postgresql-bulk-loading-huge-amounts-of-data/
