做批量插入Postgres最快的方法是什么?

我需要通过编程的方式将数千万条记录插入Postgres数据库。目前，我在一个查询中执行了数千个插入语句。

有没有更好的方法来做到这一点，一些我不知道的批量插入语句?

439773 次浏览
小开
最佳答案

PostgreSQL有一个导游关于如何最好地初始填充数据库，他们建议使用复制命令批量加载行。该指南还提供了其他一些关于如何加快处理速度的好技巧，比如在加载数据之前删除索引和外键(然后再将它们添加回来)。

小开

加快速度的一种方法是在一个事务中显式地执行多个插入或复制(比如1000个)。Postgres的默认行为是在每条语句之后提交，因此通过批处理提交，可以避免一些开销。正如Daniel回答中的指南所说，您可能必须禁用自动提交才能工作。还要注意底部的注释，该注释建议将wal_buffers的大小增加到16mb也可能有所帮助。

小开

这主要取决于数据库中的(其他)活动。这样的操作会有效地冻结其他会话的整个数据库。另一个需要考虑的问题是数据模型和约束、触发器等的存在。

我的第一种方法总是:创建一个(临时)表，其结构类似于目标表(create table tmp AS select * from target where 1=0)，并从将文件读入临时表开始。 然后我检查哪些是可以检查的:重复项，目标中已经存在的键，等等

然后我只做do insert into target select * from tmp或类似的操作。

如果失败了，或者花费了太长时间，我将中止它并考虑其他方法(暂时删除索引/约束等)

小开

你可以使用COPY table TO ... WITH BINARY，也就是"比文本和CSV格式快一些 "。只有当您有数百万行要插入，并且您对二进制数据感到满意时才这样做。

这是一个Python中的示例食谱，使用psycopg2和二进制输入。

小开
我实现了非常快速的Postgresq数据加载器与本机libpq方法。 试试我的包https://www.nuget.org/packages/NpgsqlBulkCopy/

小开

使用COPY还有一种替代方法，即Postgres支持的多行值语法。从文档:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

上面的代码插入了两行，但是您可以任意扩展它，直到达到预处理语句令牌的最大数量(可能是999美元，但我不能100%确定)。有时不能使用COPY，对于这些情况，这是一个有价值的替代品。

小开

带有数组的UNNEST函数可以与多行VALUES语法一起使用。我认为这个方法比使用COPY慢，但它对我在psycopg和python (python list传递给cursor.execute变成pg ARRAY)的工作很有用:

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (
UNNEST(ARRAY[1, 2, 3]),
UNNEST(ARRAY[100, 200, 300]),
UNNEST(ARRAY['a', 'b', 'c'])
);

没有VALUES使用subselect额外的存在性检查:

INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
SELECT * FROM (
SELECT UNNEST(ARRAY[1, 2, 3]),
UNNEST(ARRAY[100, 200, 300]),
UNNEST(ARRAY['a', 'b', 'c'])
) AS temptable
WHERE NOT EXISTS (
SELECT 1 FROM tablename tt
WHERE tt.fieldname1=temptable.fieldname1
);

同样的语法用于批量更新:

UPDATE tablename
SET fieldname1=temptable.data
FROM (
SELECT UNNEST(ARRAY[1,2]) AS id,
UNNEST(ARRAY['a', 'b']) AS data
) AS temptable
WHERE tablename.id=temptable.id;
小开

我刚刚遇到了这个问题，建议将csvsql (释放)批量导入到Postgres。要执行批量插入，你只需createdb，然后使用csvsql，它连接到你的数据库，并为整个csv文件夹创建单独的表。

$ createdb test
$ csvsql --db postgresql:///test --insert examples/*.csv
小开

((这是一个WIKI，你可以编辑和增强答案!))

外部文件是最好的和典型的批量数据

术语“批量数据”;与“大量数据”有关，因此使用原始数据是很自然的，不需要将其转换为SQL。“批量插入”的典型原始数据文件;是CSV和JSON格式。

带有一些转换的批量插入

在ETL应用程序和摄取过程中，我们需要在插入数据之前更改数据。临时表会消耗(大量)磁盘空间，而且这不是更快的方法。PostgreSQL外部数据包装器 (FDW)是最好的选择。

CSV例子。假设SQL和CSV文件中的tablename (x, y, z)

fieldname1,fieldname2,fieldname3
etc,etc,etc
... million lines ...

你可以使用经典的SQL COPY将(就像原始数据)加载到tmp_tablename中，他们将过滤后的数据插入到tablename中…但是，为了避免磁盘消耗，最好是直接摄取

INSERT INTO tablename (x, y, z)
SELECT f1(fieldname1), f2(fieldname2), f3(fieldname3) -- the transforms
FROM tmp_tablename_fdw
-- WHERE condictions
;

你需要为FDW准备数据库，而不是静态tmp_tablename_fdw，你可以使用生成它的函数:

CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE tmp_tablename_fdw(
...
) SERVER import OPTIONS ( filename '/tmp/pg_io/file.csv', format 'csv');

JSON的例子。myRawData1.json和Ranger_Policies2.json这两个文件的集合可以通过以下方式被摄取:

INSERT INTO tablename (fname, metadata, content)
SELECT fname, meta, j  -- do any data transformation here
FROM jsonb_read_files('myRawData%.json')
-- WHERE any_condiction_here
;

其中jsonb_read_files ()函数读取由掩码定义的文件夹中的所有文件:

CREATE or replace FUNCTION jsonb_read_files(
p_flike text, p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, fname text, fmeta jsonb, j jsonb) AS $f$
WITH t AS (
SELECT (row_number() OVER ())::int id,
f AS fname,
p_fpath ||'/'|| f AS f
FROM pg_ls_dir(p_fpath) t(f)
WHERE f LIKE p_flike
) SELECT id, fname,
to_jsonb( pg_stat_file(f) ) || jsonb_build_object('fpath', p_fpath),
pg_read_file(f)::jsonb
FROM t
$f$  LANGUAGE SQL IMMUTABLE;

缺少gzip流

“文件摄取”最常用的方法;(主要在大数据中)是在gzip格式上保存原始文件，并使用流算法传输它，任何可以在unix管道中快速运行且不消耗磁盘的东西:

 gunzip remote_or_local_file.csv.gz | convert_to_sql | psql

因此ideal (future)是格式.csv.gz的服务器选项。

@CharlieClark评论后注意:目前(2022年)无事可做，最好的替代方案似乎是pgloader STDIN:

  gunzip -c file.csv.gz | pgloader --type csv ... - pgsql:///target?foo
小开

也许我已经迟到了。但是，Bytefish有一个名为pgbulkinsert的Java库。我和我的团队能够在15秒内批量插入100万条记录。当然，我们还执行了一些其他操作，比如，从Minio上的文件中读取1M+记录，在1M+记录的顶部做一些处理，过滤掉重复的记录，然后最后将1M条记录插入Postgres数据库。所有这些过程都在15秒内完成。我不记得具体花了多少时间做DB操作，但我想大概不到5秒。从https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html中找到更多细节

小开

正如其他人所注意到的，在将数据导入Postgres时，会因为Postgres为您设计的检查而减慢速度。此外，您经常需要以某种方式操作数据，以使其适合使用。任何可以在Postgres进程之外完成的操作都意味着您可以使用COPY协议进行导入。

为了我的使用，我定期使用pgloader从httparchive.org项目导入数据。由于源文件是由MySQL创建的，你需要能够处理一些MySQL的奇怪之处，如使用\N作为空值，以及编码问题。文件也非常大，至少在我的机器上，使用FDW会耗尽内存。Pgloader可以很容易地创建一个管道，让你选择你想要的字段，转换为相关的数据类型和任何额外的工作，然后再进入你的主数据库，这样索引更新等是最小的。


提交答案