Difference between INSERT and COPY in PostgreSQL

avelino

Thiago Avelino

Posted on December 30, 2020

Difference between INSERT and COPY in PostgreSQL

Quite a number of reasons, actually, but the main ones are:

  • Typically, client applications wait for confirmation of one INSERT's success before sending the next. So there's a round-trip delay for each INSERT, scheduling delays, etc (pREST supports pipelineing INSERTs in batches). This point is the most significant, It's all about network round-trips and rescheduling delays;
  • Each INSERT has to go through the whole executor. Use of a prepared statement bypasses the need to run the parser, rewriter and planner, but there's still executor state to set up and tear down for each row. COPY does some setup once, and has an extremely low overhead for each row, especially where no triggers are involved.

pREST of COPY batches "INSERT's" support?

Yes, we support the insertion operation using COPY when explained in the http protocol header.

Prest-Batch-Method: copy, when not declared pREST will use INSERT.

read more at.

💖 💪 🙅 🚩
avelino
Thiago Avelino

Posted on December 30, 2020

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related