CODEWITHSUNDEEP
sqldatabasepostgresql
Kristijan
Kristijan

Reputation: 353

Postgres unique constraint performance, insert + fail on duplicate or check?

What is bigger performance hit on a postgres database when table has unique constraint:

  1. Trying to insert and let it throw unique violation constraint error
  2. Check if entry exist and not do insert if it does

I'm importing some data, and ORM is connecting some entries via many to many through connection table. It is not checking if connection exist, it just runs the query and fails with unique constraint when it exist.

Is it better to leave it like that, or to introduce a step where I would check if the entry exist and then do the insert if it doesn't?

Upvotes: 1

Views: 874

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246738

I would assume that your check from 2. would be an extra statement, so it is probably more expensive. I cannot say for sure, since you were rather vague in your question.

Besides the second approach is suffering from a race condition: you can never guarantee that no conflicting row gets inserted by a concurrent session after you checked.

If you want to avoid the error, the best approach would be

INSERT INTO ... VALUES (...) ON CONFLICT DO NOTHING;

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

performance hit:

  1. As unique constraint creates index on the specified column it will affect the rate of insertion and updation.And most abruptly, In batch operations where numbers of inserts and updates are very large.

Upvotes: 0

Related Questions