Node Promises.all() takes too long to execute

Question

I have to insert a a table with data regarding sent emails, after each email is sent.

Inside a loop I'm stuffing an array to be solved by the Promise.all(). insertData is a function that inserts Data, given two arguments, connector, the connection pool and dataToInsert, an object with data to be inserted.

async function sendAndInsert(payload) {
  for (data of payload) {    
    let array = [];
   
    const dataToInsert = {
      id: data.id,
      campaign: data.campaign,
    }

    for (email of data) {
      array.push(insertData(connector, dataToInsert));
   }        
    await Promise.all(array);
  }

}

Afterwards, the function is invoked:

async invoke () {
  await sendAndInsert(toInsertdata);
}

To insert 5000 records, it takes about 10 minutes, which is nuts.

Using

nodejs v10

pg-txclient as DB connector to PostgreSql.

What I've done and can be discarded as possible source of error:

Inserted random stuff to the table using the same connection.

I'm sure there is no issue with DB server, connection. The issue must be in the Promise.all(), await sutff.

Doing 5000 inserts in parallel on the same table means they still all have to wait for each other. You might get better performance if you reduce the number of parallel requests to say, 4 (play around with the number). — Evert, Aug 05 '21 at 22:39
what DB are you using? Also, its not clear in your code but are you 'inserting' one at a time or doing bulk insert? — proxim0, Aug 05 '21 at 22:59
as stated in my question DB is Postgresql, 10+. one at a time insert. — digitai, Aug 05 '21 at 23:06
"The issue must be in the Promise.all(), await sutff" yes yes, the issue *must* be in a language feature that literally millions of devs use every day and Google spent hundreds to thousands of engineer-hours implementing. On an unrelated note, you may find this helpful https://stackoverflow.com/questions/42468723/how-to-do-a-bulk-insert-with-node-postgres — Jared Smith, Aug 06 '21 at 00:22
@JaredSmith when I say it must be in Promise.all, off course I'm not questioning the utility or quality of this language function, I'm not stupid, only a retarded could think of that. I'm questioning the way my own code is handling such load. — digitai, Aug 06 '21 at 12:46
@digitai I misunderstood, and yes people really do say stuff like that (and mean it) *especially* about Javascript for some reason. On a happier note though, as others have pointed out your perf problem is almost certainly from doing the serial inserts. Serial inserts with new connections < serial insert with reused/pooled connection < batch inserts. — Jared Smith, Aug 06 '21 at 13:01

score 3 · Accepted Answer · answered Aug 06 '21 at 00:19

It looks like each record is being inserted through a separate call to insertData. Each call is likely to include overhead such as network latency, and 5000 requests cannot all be handled simultaneously. One call to insertData has to send the data to the database and wait for a response, before the next call can even start sending its data. 5000 requests over 10 minutes corresponds to 1.2 seconds latency per request, which is not unreasonable if the database is on another machine.

A better strategy is to insert all of the objects in one network request. You should modify insertData to allow it to accept an array of objects to insert instead of just one at a time. Then, all data can be sent at once to the database and you only suffer through the latency a single time.

Node Promises.all() takes too long to execute

1 Answers1