为了加速使用PostgreSQL批量加载大量数据，需要考虑以下几点：
1.插入与复制
2.优化检查点
3.已记录与未记录的表
4.重新创建索引
5.启用和禁用触发器
6.改善列顺序和空间消耗改善列顺序和空间消耗
插入与复制
创建一个表以及一些示例数据：
test=# CREATE TABLE t_sample
(
a varchar(50),
b int,
c varchar(50),
d int
);
CREATE TABLE
将在单个事务中编译一个包含 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 来运行脚本：
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
…
.
运行脚本：
time psql test < /tmp/bulk.sql > /dev/null
real 0m2.646s
user 0m0.110s
sys 0m0.043s
速度从 81 提高到只有 2.6 秒：
调整检查点以加快批量加载
PostgreSQL配置确实对批量加载性能有影响。有许多配置参数对数据库性能至关重要，尤其是加载。
可以从多个角度来探讨该主题：
1.减少写入的数据量
2.尽可能缩小表格（列顺序）
3.减少WAL的写入量
4.更高效地写入数据
5.更长的checkpoint间隔
6.更好的 I/O 调度
以下设置很重要：
max_wal_size：要创建的最大 WAL 数量（软限制）
checkpoint_completion_target：控制检查点行为
一般来说，将检查点扩大是一个非常好的主意。将此值设置为100或200GB以应对大容量负载密集型工作负载,并且绝对不会超出范围。
请记住，增加检查点间隔不会使您的服务器处于危险之中。它仅影响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 | | |
再次加载相同的数据：
time psql test < /tmp/sample.sql > /dev/null
real 0m59.296s
user 0m10.597s
sys 0m9.417s
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
已经生成了80MB的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）比以前快了很多。总的来说，稍后生成索引会更快。
如果可能，请在导入数据后创建索引。
启用触发器与禁用触发器
触发器也是一个重要因素。
例如：
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倍。然而，真正的区别在很大程度上取决于触发器的复杂性、行的大小等等。
优化批量加载的列顺序
在PostgreSQL中，列顺序确实有很大的不同。将“固定长度”列放在前面通常是个好主意。换句话说：int8、int4、timestamptz 等应该在表的开头。可变长度数据类型如varchar、text 等应放在表的末尾。原因是CPU对齐是磁盘上的问题。这适用于普通堆表（不适用于zheap）。
在不更改内容的情况下缩小表的大小可以加快速度，因为它有助于避免或减少批量加载数据时的关键瓶颈之一：I/O。
批量加载工具
推荐一些工具来进一步改进批量加载：
PGLoader
pg_bulkload
