如何提高PostgreSQL的INSERT性能？

Question

如何提高PostgreSQL的INSERT性能？

javascriptnode.jsperformancepostgresqlsql-insert

5

我编写了一个 Node.js 应用程序，将大量记录写入到 PostgreSQL 9.6 数据库中。不幸的是，它感觉非常慢。为了能够测试，我创建了一个简短但完整的程序，在此处复现了该场景：

'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命令一次性插入多行记录。不幸的是，这不可能做到，因为实际上需要写入的记录数会因每次调用而异，并且可变数量的参数使使用预处理语句变得不可能。 - 使用COPY代替INSERT：我无法使用此选项，因为这是在运行时进行的，而不是在初始化时。 - 使用text代替jsonb：并没有改变任何情况。 - 使用json代替jsonb：也没有改变任何情况。

以下是有关实际数据的一些注释：

- revision不一定会增加，它只是一个数字。 - flag不总是true，它也可以是false。 - 当然，数据字段中还包含不同的数据。

最终问题就是：

有哪些可能显着加快多个单个INSERT调用的速度？

- Golo Roden

你确定瓶颈不是Node和Postgres之间的通信时间吗？你有在Postgres上直接运行这10个插入的基准测试吗？ - Tim Biegeleisen

无论如何，这还不够好;-) - Golo Roden

然后将其用作某种基准。我对Postgres的了解不足，无法帮助您进行调整以在一秒内完成。 - Tim Biegeleisen

如果您要在同一张表中插入10k条记录，可以使用以下语句：INSERT INTO yourTable (position, id, ...) VALUES (1, 435, ...), (2, 23, ...), (3, 546, ...).... - Philipp Nies

也许我误解了你的意思，你可能在建议不同的事情，但这就是我的意思：“使用单个INSERT命令一次插入多行。不幸的是，这是不可能的，因为实际上需要写入的记录数量因调用而异，并且可变数量的参数使得无法使用预处理语句。” 我有什么遗漏吗？ - Golo Roden

显示剩余5条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vitaly-t · Accepted Answer

使用单个INSERT命令一次性插入多行是不可能的。实际上，需要写入的记录数因调用而异，而参数数量的变化使得无法使用预处理语句。您可以在循环中生成多行插入，每个查询大约1000-10000条记录，具体取决于记录的大小。这完全不需要预处理语句。请参阅我关于同样问题的文章：Performance Boost。根据该文章，我的代码能够在50毫秒以下时间内插入10000条记录。相关问题：Multi-row insert with pg-promise。