public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Herrera <[email protected]>
To: [email protected]
Cc: 'Laurenz Albe' <[email protected]>
Cc: [email protected]
Subject: Re: Bulk DML performance
Date: Mon, 17 Mar 2025 11:53:37 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

On 2025-Mar-13, [email protected] wrote:

> I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on
> the data, so sadly I cannot use COPY.
> 
> I have discovered that for some reason, performing the original insert
> without the ON CONFLICT statement is twice as fast as performing the
> original insert with an ON CONFLICT ... DO UPDATE clause, completing
> in 4 seconds instead of 8. That seems strange to me because I wouldn't
> have thought it would be doing any additional work since a unique
> constraint is on the primary key, so each inserted value would need to
> be checked in either case, and there is no extra work to be done in
> either case.

As I recall, INSERT .. ON CONFLICT UPDATE requires to _insert_ a value
in the index prior to inserting the heap tuple, to guarantee uniqueness
in face of potentially concurrent inserters of the same value.  Maybe
have a look at the WAL produced by the operation with "pg_waldump -z" to
get some idea of the volume of each type of record.


Maybe you could try to use MERGE rather than INSERT .. ON CONFLICT
UPDATE.  The concurrency modelling there is different, and it will
probably have lower overhead.  But you may need to lock the table
explicitly to prevent concurrency problems.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem."  (Tom Lane)

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Bulk DML performance
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox