具有pg-promise的大量插入 [英] Massive inserts with pg-promise
问题描述
我正在使用 pg-promise ，并且我想多次插入一张桌子。我见过一些解决方案，例如使用pg-promise进行多行插入和如何正确地将多行插入PG与node-postgres？，我可以使用pgp.helpers.concat来连接多个选择。
I'm using pg-promise and I want to make multiple inserts to one table. I've seen some solutions like Multi-row insert with pg-promise and How do I properly insert multiple rows into PG with node-postgres?, and I could use pgp.helpers.concat in order to concatenate multiple selects.
但是现在，我需要插入一个一个表中有很多度量，具有10,000多个记录，并且在 https中：//github.com/vitaly-t/pg-promise/wiki/Performance-Boost 说：
您可以像这样连接多少条记录-取决于记录的大小，但是我用这种方法将永远不会超过10,000条记录。因此，如果您必须插入更多记录，则希望将它们分成多个串联的批处理，然后逐个执行一个。
But now, I need to insert a lot of measurements in a table, with more than 10,000 records, and in https://github.com/vitaly-t/pg-promise/wiki/Performance-Boost says: "How many records you can concatenate like this - depends on the size of the records, but I would never go over 10,000 records with this approach. So if you have to insert many more records, you would want to split them into such concatenated batches and then execute them one by one."
我阅读了所有文章，但我不知道如何将插入的内容拆分成批然后逐个执行。
I read all the article but I can't figure it out how to "split" my inserts into batches and then execute them one by one.
谢谢！
推荐答案
更新
最好阅读以下文章：数据导入。
作为 pg-promise 我最终不得不为这个问题提供正确的答案，因为之前发布的那个并没有真正做到
As the author of pg-promise I was compelled to finally provide the right answer to the question, as the one published earlier didn't really do it justice.
为了插入大量/无限数量的记录，您的方法应基于方法序列，可在任务和事务中使用。
In order to insert massive/infinite number of records, your approach should be based on method sequence, that's available within tasks and transactions.
var cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tableName'});
// returns a promise with the next array of data objects,
// while there is data, or an empty array when no more data left
function getData(index) {
if (/*still have data for the index*/) {
// - resolve with the next array of data
} else {
// - resolve with an empty array, if no more data left
// - reject, if something went wrong
}
}
function source(index) {
var t = this;
return getData(index)
.then(data => {
if (data.length) {
// while there is still data, insert the next bunch:
var insert = pgp.helpers.insert(data, cs);
return t.none(insert);
}
// returning nothing/undefined ends the sequence
});
}
db.tx(t => t.sequence(source))
.then(data => {
// success
})
.catch(error => {
// error
});
从性能的角度来看，这是将大量行插入数据库的最佳方法
This is the best approach to inserting massive number of rows into the database, from both performance point of view and load throttling.
所有您需要做的就是根据应用程序的逻辑实现功能 getData ，也就是说，根据序列的索引，您的大数据来自哪里，一次返回大约1,000-10,000个对象，具体取决于对象的大小和数据可用性。
All you have to do is implement your function getData according to the logic of your app, i.e. where your large data is coming from, based on the index of the sequence, to return some 1,000 - 10,000 objects at a time, depending on the size of objects and data availability.
另请参见一些API示例：
See also some API examples:
- spex -> sequence
- Linked and Detached Sequencing
- Streaming and Paging
相关q uestion：具有大量查询的node-postgres 。
Related question: node-postgres with massive amount of queries.
在需要获取所有插入记录的生成ID的情况下，可以按以下方式更改两行：
And in cases where you need to acquire generated id-s of all the inserted records, you would change the two lines as follows:
// return t.none(insert);
return t.map(insert + 'RETURNING id', [], a => +a.id);
和
// db.tx(t => t.sequence(source))
db.tx(t => t.sequence(source, {track: true}))
请小心，因为在内存中保留过多的记录id会造成过载。
just be careful, as keeping too many record id-s in memory can create an overload.
这篇关于具有pg-promise的大量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
