How can I put many script-generated records in PostgreSQL quickly from node.js?

Question

The following code, which inserts 200,000 records into PostgreSQL server from node.js, is taking about 17 minutes on my laptop PC, which feels horribly slow.

var pg = require('pg');
var Client = pg.Client;
var async = require('async');

var client = new Client(connectionString);
client.connect();

var rollback = function(client) {
  client.query('ROLLBACK', function() {
    client.end();
    process.kill();
  });
};

client.query('BEGIN',function(err,result){
  if(err){ console.error(err); rollback(client);};
  async.waterfall([
    function(cb){
      client.query('DROP INDEX idx',function(err,result){
        client.query('TRUNCATE TABLE tbl',function(err,result){
          async.forEach(values,function(value,valueNext){
            client.query('INSERT INTO tbl ('
                         + 'col1,'
                         + 'col2) VALUES ($1,$2)',[
                           value,
                           generatedSomething(value)
                         ],function(err){
                           valueNext();
                         });
          },function(err,result){
            if(err){ console.error(err); rollback(client);cb(false);return;};
            client.query('CREATE INDEX idx ON tbl',function(err,result){
              cb(null);
            });
          });
        });
      });
    });
  },
],function(err){
  client.query('COMMIT', client.end.bind(client));
});

There are some strategies I've applied to speed up.

Drop all indices before insertion, create it after all insertion is done ... ok
Use TRUNCATE TABLE instead of DELETE FROM ... ok
Use COPY FROM instead of INSERT INTO ... not done

It seems that using COPY FROM instead of INSERT INTO will make effect, but it's used for imported CSV files, not for script-generated values.

So, does it mean that exporting script-generated values to temporary CSV file, and importing values using COPY FROM, is the most effictive way to insert values into PostgreSQL quickly?

Why are you using waterfall if you're only passing 1 function in the array? — FuriousFolder
– FuriousFolder, Commented Oct 26, 2014 at 16:54

Vlad · Accepted Answer · 2014-10-27 10:25:50Z

1

copyFrom will return a WritableStream that you can append your values to as CSV, like:

var stream = client.copyFrom("COPY tbl (col1, col2) FROM STDIN WITH CSV");
stream.on('close', function() {
  client.query("COMMIT");
});
stream.on('error', rollback);
async.forEach(values, function(value, valueNext) {
  stream.write(value + "," + generatedSomething(value) + "\n");
});
stream.end();

Of course you will need to properly escape your values

answered Oct 27, 2014 at 10:25

Vlad

10.8k3 gold badges40 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I put many script-generated records in PostgreSQL quickly from node.js?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related