CODEWITHSUNDEEP
postgresqlbulkinsert
user3756488
user3756488

Reputation: 233

Difference between INSERT and COPY

As per the documentation,

Loading large number of rows using COPY is always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction.

Why COPY is faster than INSERT (multiple insertion are batched into single transaction) ?

Upvotes: 12

Views: 9572

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324751

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. (PgJDBC supports pipelineing INSERTs in batches, but I'm not aware of any other clients that do).

  • 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.

The first point is the most significant. It's all about network round-trips and rescheduling delays.

Upvotes: 14

Renzo
Renzo

Reputation: 27424

This is because COPY is a single statement, while each INSERT is a separate statement. Since each single statement is normally subject to logging (manual), even inside a unique transaction, the use of many INSERT is slower than the use of a single COPY.

Upvotes: 2

Related Questions