Does ConstraintViolationException have performance impact in Postgres?

Question

I have a table with unique constraint on 2 columns (user1 and user2), so that there cannot be any duplicated pair of users. It looks like this:

@Table(name = "relation", uniqueConstraints ={ @UniqueConstraint(columnNames = { "user1", "user2" }) }) class Relation{} ...

Columns user1 and user2 are uuid-char's

When I receive data from some remote end, I get a list of pairs which I have to insert to my table. This is the list I receive:

{ user1: some-uuid, user2: some-other-uuid } { user1: some-uuid, user2: some-other-uuid } { user1: some-uuid, user2: some-other-uuid } ... + 1000 records

Then, I iterate over that list and insert all the pairs:

for(Pair p : data){ try{ relationRepository.save(p); } catch(PostgresConstraintViolationException ex){ log("Pair already exists, but that's ok") } }

My question is:

Does intentionally catching ConstraintViolationException have any performance impact? It seems much more better, than checking if the pairs exist in the database and insert if it doestn't. (1 query instead of 2).

I once heard that "This is what exceptions are for". I understand that, but I'm not sure how Postgres behaves in this situation.

It's not really a question about how PostgreSQL behaves. It's a question about how hibernate behaves. Personally, I'd be more concerned about iterating over 1000 records rather than treating them as a single set. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jun 21, 2019 at 15:30

Ilya Sereb · Accepted Answer · 2019-06-21 14:15:21Z

In terms of performance, inserting and hoping for the exception is better, because an additional query to a remote server will never beat simple exception handling. However, sometimes there are things that database is not aware of and you might need to execute a query or couple before inserting the data. As of personal experience, it's better to execute those queries, because they make your code readable and easily understandable. Additionally, exceptions sometimes are ambiguous (in case of SQLException), and sometimes are very hard to predict, what leads to floating bugs.

This is a very good post you should take a look at.

HeatZync · Accepted Answer · 2022-10-19 13:57:56Z

Relying on the constraint violation will have better performance, but only if you do it right. If you don't then it will be bad for performance, dead tuples, autovacuum and storage.

The right way is to make use of INSERT ... ON CONFLICT DO NOTHING as explained in this article over at the AWS Database Blog.

Collectives™ on Stack Overflow

Does ConstraintViolationException have performance impact in Postgres?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related