Docs: document how to proper make batch inserts/updates #1388

Activity

1. charmander

   added this to the docs milestone on Jul 28, 2017
2. qas commented on Aug 2, 2017

   

   qas

   on Aug 2, 2017

   More actions

   👍
3. brianc

   self-assigned this

   on Aug 4, 2017
4. joelmukuthu

   added a commit that references this issue on Aug 18, 2017

   feat(query): support batch inserts

   ...

   03ad68a
5. ariofrio commented on May 8, 2020

   

   ariofrio

   on May 8, 2020

   Last edited by ariofrio

   More actions

   Any guidance on this? I'm thinking of this in comparison with the way JDBC's PostgreSQL driver works:

   2. JDBC batch API. That is PreparedStatement#addBatch() enables driver to send multiple "query executions" in a single network roundtrip. Current implementation, however would still split large batches into smaller ones to avoid TCP deadlock.

      The actions would be much better:

      execute query
      ...
      execute query
      execute query
      execute query
      sync <-- wait for the response from the DB
      

   It seems that using Promise.all() might be the equivalent in node-pg. However:

   1. Does node-pg client state get corrupted if a client.query() call occurs before another client.query() call has resolved, on the same Client?
   2. Does the second client.query() wait until the first client.query() has received a response from the server, or only until the first client.query() has finished sending the query to the server?
   3. [Especially if we don't wrap time-overlapping client.query() calls in a transaction], does node-pg queue server responses appropriately to resolve the correct Promise with the correct response?
   4. Is there any performance advantage to wrapping the calls in a transaction?

   I am planning to migrate a PostgreSQL JDBC microservice that uses 1000-batched inserts to achieve desired performance to node-pg, which is why I am trying to figure this out.
6. charmander commented on May 8, 2020

   

   charmander

   on May 8, 2020

   Collaborator

   More actions

   @ariofrio Batched queries – pipelining – is broader than batch insert. pg doesn’t have pipelining yet. I think @brianc is working on it.

   I am planning to migrate a PostgreSQL JDBC microservice that uses 1000-batched inserts to achieve desired performance to node-pg, which is why I am trying to figure this out.

   You should probably try to do it in fewer queries anyway, like #957 (comment).
7. sebastianrothbucher commented on Feb 20, 2022

   

   sebastianrothbucher

   on Feb 20, 2022

   More actions

   case it still helps: temp tables can achieve it - see this comment: #956 (comment)