如何提高PostgreSQL在INSERT上的性能?

我写了一个Node.js应用程序,它将大量logging写入PostgreSQL 9.6数据库。 不幸的是,这感觉很慢。 为了能够testing一些东西,我创build了一个简短但完整的程序来重现场景:

'use strict'; const async = require('async'), pg = require('pg'), uuid = require('uuidv4'); const pool = new pg.Pool({ protocol: 'pg', user: 'golo', host: 'localhost', port: 5432, database: 'golo' }); const records = []; for (let i = 0; i < 10000; i++) { records.push({ id: uuid(), revision: i, data: { foo: 'bar', bar: 'baz' }, flag: true }); } pool.connect((err, database, close) => { if (err) { /* eslint-disable no-console */ return console.log(err); /* eslint-enable no-console */ } database.query(` CREATE TABLE IF NOT EXISTS "foo" ( "position" bigserial NOT NULL, "id" uuid NOT NULL, "revision" integer NOT NULL, "data" jsonb NOT NULL, "flag" boolean NOT NULL, CONSTRAINT "foo_pk" PRIMARY KEY("position"), CONSTRAINT "foo_index_id_revision" UNIQUE ("id", "revision") ); `, errQuery => { if (errQuery) { /* eslint-disable no-console */ return console.log(errQuery); /* eslint-enable no-console */ } async.series({ beginTransaction (done) { /* eslint-disable no-console */ console.time('foo'); /* eslint-enable no-console */ database.query('BEGIN', done); }, saveRecords (done) { async.eachSeries(records, (record, doneEach) => { database.query({ name: 'save', text: ` INSERT INTO "foo" ("id", "revision", "data", "flag") VALUES ($1, $2, $3, $4) RETURNING position; `, values: [ record.id, record.revision, record.data, record.flag ] }, (errQuery2, result) => { if (errQuery2) { return doneEach(errQuery2); } record.position = Number(result.rows[0].position); doneEach(null); }); }, done); }, commitTransaction (done) { database.query('COMMIT', done); } }, errSeries => { /* eslint-disable no-console */ console.timeEnd('foo'); /* eslint-enable no-console */ if (errSeries) { return database.query('ROLLBACK', errRollback => { close(); if (errRollback) { /* eslint-disable no-console */ return console.log(errRollback); /* eslint-enable no-console */ } /* eslint-disable no-console */ console.log(errSeries); /* eslint-enable no-console */ }); } close(); /* eslint-disable no-console */ console.log('Done!'); /* eslint-enable no-console */ }); }); }); 

插入10.000行的性能是2.5秒。 这不坏,但也不是很好。 我能做些什么来提高速度?

我到目前为止的一些想法:

  • 使用准备的语句。 正如你所见,我已经做到了这一点,这加快了30%的事情。
  • 使用一个INSERT命令一次插入多行。 不幸的是,这是不可能的,因为实际上,每次调用都需要写入的logging数量不同,而且参数数量也不一样,所以不可能使用预先准备好的语句。
  • 使用COPY而不是INSERT :我不能使用这个,因为这发生在运行时,而不是在初始化时。
  • 使用text而不是jsonb :没有改变的事情。
  • 使用json而不是jsonb :没有改变任何东西。

关于现实中发生的数据的更多注释:

  • revision不一定会增加。 这只是一个数字。
  • flag并不总是true ,它也可能是true ,也可能是false
  • 当然, data字段也包含不同的数据。

所以最终归结为:

  • 有什么可能性,以显着加快多个单一的调用INSERT

       

网上收集的解决方案 "如何提高PostgreSQL在INSERT上的性能?"

使用一个INSERT命令一次插入多行。 不幸的是,这是不可能的,因为实际上,每次调用都需要写入的logging数量不同,而且参数数量也不一样,所以不可能使用预先准备好的语句。

这是正确的答案,其次是无效的反驳。

您可以在一个循环中生成多行插入,每个查询大约有1000 – 10,000条logging,具体取决于logging的大小。

而你根本就不需要准备好这些语句。

看到这篇文章我写了关于相同的问题: 性能提升 。

在文章之后,我的代码能够在50ms以下插入10,000条logging。

一个相关的问题: 使用pg-promise的多行插入 。