blocks|key|1365118|text|PostgreSQL在最初如何最好地填充数据库方面有a+guide，他们建议使用COPY命令来批量装载行。该指南还提供了一些关于如何加速该过程的好技巧，比如在加载数据之前删除索引和外键(并在加载后再添加它们)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1365119|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/interactive/populate.html|1|https://www.postgresql.org/docs/current/interactive/sql-copy.html^0|Q|7|0|14|4|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

PostgreSQL has <a href="https://www.postgresql.org/docs/current/interactive/populate.html" rel="noreferrer">a guide</a> on how to best populate a database initially, and they suggest using the <a href="https://www.postgresql.org/docs/current/interactive/sql-copy.html" rel="noreferrer">COPY</a> command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).

blocks|key|1365389|text|除了使用COPY之外，还有一种替代方法，即Postgres支持的多行值语法。从documentation|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1365390|INSERT+INTO+films+(code,+title,+did,+date_prod,+kind)+VALUES
++++('B6717',+'Tampopo',+110,+'1985-02-10',+'Comedy'),
++++('HG120',+'The+Dinner+Game',+140,+DEFAULT,+'Comedy');|code-block|syntax|javascript|1365391|上面的代码插入两行，但是您可以任意扩展它，直到达到预置语句标记的最大数量(可能是$999，但我不能百分之百确定)。有时人们不能使用COPY，对于这些情况，这是一个有价值的替代品。|1365392|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.4/static/sql-insert.html^0|13|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

There is an alternative to using COPY, which is the multirow values syntax that Postgres supports. From the <a href="http://www.postgresql.org/docs/9.4/static/sql-insert.html" rel="noreferrer">documentation</a>:

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

The above code inserts two rows, but you can extend it arbitrarily, until you hit the maximum number of prepared statement tokens (it might be $999, but I'm not 100% sure about that). Sometimes one cannot use COPY, and this is a worthy replacement for those situations.

blocks|key|1580238|text|提高速度的一种方法是在一个事务中显式地执行多次插入或复制(比如1000次)。Postgres的默认行为是在每条语句之后提交，因此通过对提交进行批处理，可以避免一些开销。正如Daniel答案中的指南所说，您可能必须禁用自动提交才能正常工作。还要注意底部的注释，该注释建议将wal_buffers的大小增加到16MB，这可能也会有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580239|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One way to speed things up is to explicitly perform multiple inserts or copy's within a transaction (say 1000). Postgres's default behavior is to commit after each statement, so by batching the commits, you can avoid some overhead. As the guide in Daniel's answer says, you may have to disable autocommit for this to work. Also note the comment at the bottom that suggests increasing the size of the wal_buffers to 16 MB may also help.

blocks|key|1580598|text|带数组的UNNEST函数可以与多行值语法一起使用。我认为这种方法比使用COPY慢，但它对我在使用COPY和python时很有用(传递给cursor.execute的python+list变成了pg+ARRAY)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1580599|INSERT+INTO+tablename+(fieldname1,+fieldname2,+fieldname3)
VALUES+(
++++UNNEST(ARRAY[1,+2,+3]),+
++++UNNEST(ARRAY[100,+200,+300]),+
++++UNNEST(ARRAY['a',+'b',+'c'])
);|code-block|syntax|javascript|1580600|在不使用带有附加存在检查的subselect的VALUES的情况下：|1580601|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
);|1580602|批量更新的语法相同：|1580603|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;|1580604|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/devel/static/functions-array.html^0|4|6|Z|4|1C|4|1V|E|2H|4|2R|5|4|6|0|0|0|N|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@$9|1D|A|1E|1|1F]]|E|$]]|$1|F|3|G|5|H|7|1G|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1H|8|@$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1K|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1L|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1M|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|1N|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

<a href="http://www.postgresql.org/docs/devel/static/functions-array.html" rel="noreferrer"><code>UNNEST</code></a> function with arrays can be used along with multirow VALUES syntax. I'm think that this method is slower than using <code>COPY</code> but it is useful to me in work with psycopg and python (python <code>list</code> passed to <code>cursor.execute</code> becomes pg <code>ARRAY</code>):

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

without <code>VALUES</code> using subselect with additional existance check:

<pre><code>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
);
</code></pre>

the same syntax to bulk updates:

<pre><code>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;
</code></pre>

blocks|key|1365258|text|您可以使用“somewhat+faster+than+the+text+and+CSV+formats”的COPY+table+TO+...+WITH+BINARY。只有当您有数百万行要插入，并且您熟悉二进制数据时，才能执行此操作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1365259|这是一个example+recipe+in+Python,+using+psycopg2+with+binary+input。|1365260|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/sql-copy.html|1|https://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2/8150329#8150329^0|1H|T|6|19|0|0|4|1M|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|H|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

You can use <code>COPY table TO ... WITH BINARY</code> which is "<a href="http://www.postgresql.org/docs/current/interactive/sql-copy.html" rel="noreferrer">somewhat faster than the text and CSV formats</a>." Only do this if you have millions of rows to insert, and if you are comfortable with binary data.

Here is an <a href="https://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2/8150329#8150329">example recipe in Python, using psycopg2 with binary input</a>.

blocks|key|1580310|text|这主要取决于数据库中的(其他)活动。这样的操作有效地冻结了其他会话的整个数据库。另一个考虑因素是数据模型和约束、触发器等的存在。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580311|我的第一种方法总是:创建一个结构类似于目标表的(+temp+)表(create+table+tmp+AS+select+*+from+target+where+1=0)，并从将文件读入临时表开始。然后，我检查可以检查的内容:重复项、目标中已存在的键等。|1580312|然后我只做一个"do+insert+into+target+select+*+from+tmp“或类似的操作。|1580313|如果这失败了，或者花费的时间太长，我会中止它并考虑其他方法(临时删除索引/约束等)|1580314|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

It mostly depends on the (other) activity in the database. Operations like this effectively freeze the entire database for other sessions. Another consideration is the datamodel and the presence of constraints,triggers, etc.

My first approach is always: create a (temp) table with a structure similar to the target table (create table tmp AS select * from target where 1=0), and start by reading the file into the temp table.
Then I check what can be checked: duplicates, keys that already exist in the target, etc.

Then I just do a "do insert into target select * from tmp" or similar.

If this fails, or takes too long, I abort it and consider other methods (temporarily dropping indexes/constraints, etc)

blocks|key|1580422|text|我使用原生libpq方法实现了非常快速的Postgresq数据加载器。试用我的包https://www.nuget.org/packages/NpgsqlBulkCopy/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1580423|entityMap|0|LINK|mutability|MUTABLE|url|https://www.nuget.org/packages/NpgsqlBulkCopy/^0|14|1A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

I implemented very fast Postgresq data loader with native libpq methods.
Try my package <a href="https://www.nuget.org/packages/NpgsqlBulkCopy/" rel="noreferrer">https://www.nuget.org/packages/NpgsqlBulkCopy/</a>

blocks|key|1365556|text|我刚刚遇到这个问题，建议使用csvsql+(releases)进行批量导入到Postgres。要执行批量插入，只需使用createdb，然后使用csvsql，它将连接到数据库并为整个CSV文件夹创建单独的表。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1365557|$+createdb+test+
$+csvsql+--db+postgresql:///test+--insert+examples/*.csv|code-block|syntax|javascript|1365558|entityMap|0|LINK|mutability|MUTABLE|url|https://csvkit.readthedocs.io/|1|https://github.com/wireservice/csvkit/releases^0|1N|8|20|6|E|6|0|M|8|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]|S|$5|N|O|P|E|$Q|T]]]]

I just encountered this issue and would recommend <a href="https://csvkit.readthedocs.io" rel="nofollow noreferrer">csvsql</a> (<a href="https://github.com/wireservice/csvkit/releases" rel="nofollow noreferrer">releases</a>) for bulk imports to Postgres. To perform a bulk insert you'd simply <code>createdb</code> and then use <code>csvsql</code>, which connects to your database and creates individual tables for an entire folder of CSVs.
<pre><code>$ createdb test 
$ csvsql --db postgresql:///test --insert examples/*.csv
</code></pre>

blocks|key|1365630|text|也许我已经迟到了。但是，Bytefish有一个名为pgbulkinsert的Java库。我和我的团队能够在15秒内批量插入100万条记录。当然，我们还执行了一些其他操作，例如，从Minio上的文件中读取1M%2B记录，在1M%2B记录的顶部执行几个处理，如果重复则过滤记录，最后将100万条记录插入Postgres数据库。所有这些过程都在15秒内完成。我不记得做DB操作到底花了多少时间，但我想大概不到5秒。从https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html查找更多详细信息|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1365631|entityMap|0|LINK|mutability|MUTABLE|url|https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html^0|P|C|5L|1O|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

May be I'm late already. But, there is a Java library called <code>pgbulkinsert</code> by Bytefish. Me and my team were able to bulk insert 1 Million records in 15 seconds. Of course, there were some other operations that we performed like, reading 1M+ records from a file sitting on Minio, do couple of processing on the top of 1M+ records, filter down records if duplicates, and then finally insert 1M records into the Postgres Database. And all these processes were completed within 15 seconds. I don't remember exactly how much time it took to do the DB operation, but I think it was around less then 5 seconds. Find more details from <a href="https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html" rel="nofollow noreferrer">https://www.bytefish.de/blog/pgbulkinsert_bulkprocessor.html</a>

blocks|key|1580841|text|正如其他人所指出的，当将数据导入Postgres时，Postgres为您设计的检查将会减慢速度。此外，您通常需要以这样或那样的方式操作数据，以便它适合使用。所有这些都可以在Postgres进程之外完成，这意味着您可以使用复制协议进行导入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1580842|为了方便使用，我经常使用pgloader从httparchive.org项目导入数据。由于源文件是由MySQL创建的，因此您需要能够处理一些MySQL怪事，比如对空值使用\N以及编码问题。文件也太大了，至少在我的机器上，使用FDW会耗尽内存。pgloader可以很容易地创建一个管道，让你选择你想要的字段，转换成相关的数据类型，并在进入主数据库之前做任何额外的工作，以便最大限度地减少索引更新等。|offset|length|style|CODE|1580843|entityMap|0|LINK|mutability|MUTABLE|url|https://pgloader.io/|1|https://legacy.httparchive.org/^0|0|2D|2|C|8|0|L|F|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@$D|V|E|W|1|X]|$D|Y|E|Z|1|10]]|A|$]]|$1|H|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]|P|$5|K|L|M|A|$N|Q]]]]

As others have noted, when importing data into Postgres, things will be slowed by the checks that Postgres is designed to do for you. Also, you often need to manipulate the data in one way or another so that it's suitable for use. Any of this that can be done outside of the Postgres process will mean that you can import using the COPY protocol.
For my use I regularly import data from the <a href="https://legacy.httparchive.org" rel="nofollow noreferrer">httparchive.org</a> project using <a href="https://pgloader.io" rel="nofollow noreferrer">pgloader</a>. As the source files are created by MySQL you need to be able to handle some MySQL oddities such as the use of <code>\N</code> for an empty value and along with encoding problems. The files are also so large that, at least on my machine, using FDW runs out of memory. pgloader makes it easy to create a pipeline that lets you select the fields you want, cast to the relevant data types and any additional work before it goes into your main database so that index updates, etc. are minimal.

I need to programmatically insert 10's of millions of records into a postgres database. Presently I am executing 1000's of insert statements in a single "query". 

Is there a better way to do this, some bulk insert statement I dont know about?

What's the fastest way to do a bulk insert into Postgres?

PostgreSQL

数据库

MySQL

Java

我需要以编程方式将10条数百万条记录插入postgres数据库。目前，我在一个“查询”中执行了1000条insert语句。有没有更好的方法来做这件事，一些我不知道的大容量insert语句？

问在Postgres中进行批量插入的最快方法是什么？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中进行批量插入的最快方法是什么？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中进行批量插入的最快方法是什么？
EN