6

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.

0

3 Answers 3

14

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, which also removes the race condition:

    INSERT INTO testins (id,t) SELECT %s,'hello, world'
    WHERE NOT EXISTS( SELECT FROM testins WHERE id=%s )
    RETURNING id
    
  • 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.

0
10

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.

0
8

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.