Is there a performance penalty for unique key constraint violations in Postgres?

Question

In my API, the user can potentially send a request which tries to create a new row when a row with that unique key exists.

Currently, I'm catching the unique key error and returning a message to say that X already exists. But, is it more performant to lookup the row first (on the same connection), and only run the INSERT statement if that row doesn't exist?

My intuition says that reading the error from PostgreSQL should be more efficient, but I'd like to be sure I'm doing things idiomatically.

PostgreSQL is version 12

The unique key in my API isn't a surrogate ID value, it's a composite made up of a foreign key, combined with a text value. The database does already generate its own surrogate ID for this row if the unique key constraint doesn't fail. So the ID of the row is not what I'm checking. The correct behavior is to not insert the row, because the FK/text value needs to be unique in the table. If the request contains a FK/text value that already exists in the table, then no row should be inserted.

Answer 1

is it more performant to lookup the row first (on the same connection), and only run the INSERT statement if that row doesn't exist?

If someone else inserts a duplicate concurrently, there's no issue: the select won't see it, but the unique constraint will be enforced. You still have to duplicate your error handling code, though. However if someone deletes the duplicate after the select saw it, then it won't be inserted.

I ran a Python benchmark, source code is available on pastebin. This is a simple example using a table with only a primary key and a dummy text column. For each id in the range 0..99, it inserts it 100 times. Only the first time will work, the rest will be rejected by the unique constraint.

The candidates are:

• insert_only: sends the insert, then either it works or it fails the unique constraint.

• select_then_insert: does one select to check, then the insert.

• insert_select combines the previous two queries into one, doesn't remove the race condition but shortens the time window when it can occur:

  INSERT INTO testins (id,t) SELECT %s,'hello, world'
  WHERE NOT EXISTS( SELECT FROM testins WHERE id=%s )
  RETURNING id
  

(in both previous cases, the query can fail with a duplicate error, which should be handled).

• on_conflict uses the upsert feature:

  INSERT INTO testins (id,t) VALUES (%s,'hello, world') 
  ON CONFLICT (id) DO NOTHING 
  RETURNING id
  

RETURNING id simply returns the id if the row was inserted, so you know it was. If the query returns nothing, it means there was a duplicate.

Results: "Latency" is time per INSERT attempt. "No. of rows" is the number of successful INSERTs having left a row in the table. There are 10k INSERT attempts total.

Method	Latency	No. of rows	Table size
on_conflict:	68.3µs	100 rows	8.000 kB
insert_only:	85.0µs	100 rows	512.000 kB
select_then_insert:	73.6µs	100 rows	8.000 kB
insert_select:	61.5µs	100 rows	8.000 kB

This test has 99 duplicates for each insert, so let's try a more reasonable amount of 1 duplicate per insert:

Method	Latency	No. of rows	Table size
on_conflict:	74.3µs	5000 rows	256.000 kB
insert_only:	78.2µs	5000 rows	512.000 kB
select_then_insert:	94.3µs	5000 rows	256.000 kB
insert_select:	66.8µs	5000 rows	256.000 kB

No duplicates:

Method	Latency	No. of rows	Table size
on_conflict:	81.6µs	10000 rows	512.000 kB
insert_only:	69.1µs	10000 rows	512.000 kB
select_then_insert:	184µs	10000 rows	512.000 kB
insert_select:	77.7µs	10000 rows	512.000 kB

In all cases, most of the time is spent doing roundtrips on the connection, and committing transactions.

Conclusion:

The issue with the straight INSERT is the fact that it still writes the row in the table, then tries to write it in the index and fails on a duplicate, then rolls back the transaction. This results in disk writes (table and WAL) and it bloats the table with dead rows which will need VACUUMing. Doing all this stuff explains the small performance penalty.

The other solutions don't insert the row if there is a duplicate, which avoids useless writes and table bloat.

The most idiomatic for postgres would be ON CONFLICT.

So if you expect to have lots of duplicates, ie most of the time the INSERT will fail, and traffic on this query is high, it would be advantageous to use ON CONFLICT.

If you expect few duplicates, ie most of the times the INSERT will work, then you can just let it throw the error.

If this is part of a larger transaction that you'd rather not fail, rollback, and do all the work again, then ON CONFLICT can help since it won't throw an error in case of a duplicate.

Answer 2

But, is it more performant to lookup the row first (on the same connection), and only run the INSERT statement if that row doesn't exist?

Regardless if it's more performant or not, it's not guaranteed to be accurate without locking the entire table for the time of the lookup til the time of the INSERT. Without locking the table, someone can theoretically INSERT the same key of data between when you check and when you do your INSERT (even if they run nanoseconds apart). At that rate, it's probably less performant for the entire system, from a holistic perspective, than to just rely on the unique key constraint.

Answer 3

In PostgreSQL, unique constraints are implemented by inserting the record first, then rolling it back if it violates the constraint.

If your constraint is deferred, then the duplicate B-Tree entry is also inserted for the time being, then an internal trigger called unique_key_recheck is run to verify that the newly inserted records don't violate the constraint.

It looks like this:

test=# CREATE TABLE mytable (id INT NOT NULL, value TEXT NOT NULL, CONSTRAINT ux_mytable_id UNIQUE (id) DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE
test=# INSERT INTO mytable VALUES (1, 'test');
INSERT 0 1
test=# INSERT INTO mytable VALUES (1, 'test2');
ERROR:  duplicate key value violates unique constraint "ux_mytable_id"
DETAIL:  Key (id)=(1) already exists.
test=# SELECT * FROM heap_page_items(get_raw_page('mytable', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     33 |    759 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000b74657374
  2 |   8112 |        1 |     34 |    760 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x010000000d7465737432
(2 rows)

test=# SELECT * FROM bt_page_items('ux_mytable_id', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
------------+-------+---------+-------+------+-------------------------+------+-------+------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |
          2 | (0,2) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,2) |
(2 rows)

The second records in these resultsets are dead records that had been rolled back when your constraint failed. These records clutter the tablespace and the WAL.

So answering your question directly: yes, there are things affecting the overall performance that do happen when you violate the constraint and don't happen when you don't.

My intuition says that reading the error from Postgres should be more efficient

This depends on how often these constraint violations happen.

Reading the record in advance requires traversing the B-Tree twice, and if your error rate is very low (like it should be), it might be worth it taking the dead-entry performance hit once in a very long while over doing the check on every insert.

Note that in a properly designed system the unique constraint should be there regardless of whether you check it in advance or not.