INSERT ON CONFLICT DO NOTHING efficiency (PostgreSQL)

In Postgres, is it inefficient to perform an “INSERT… ON CONFLICT DO NOTHING” if >99.9% of the time the record exists and nothing is done?

E.g. let’s say I have a Node process which fetches 100k records which map to rows, of which 99,980 already exist. I could:

• Insert them all, but do nothing on uniqueness constraint violation

• Insert where not exists query

• Select all, do de-duplication “client” side, then insert only the 20 new ones

The first approach is easiest but I wonder if it will be too inefficient for Postgres. I have read that it increments serial numbers in such a “do nothing” case, but would it be OK if I skipped using a serial and used my unique field as the primary (string) key instead?

INSERT ... ON CONFLICT is as efficient as it can be, but the question is beside the point.

It may well be that an UPDATE is faster, but that UPDATE wouldn't do the right thing for 0.1% of the rows, so it is not a solution.

Note: it is always possible to be faster if you don't have to be correct.

StackExchange Database Administrators Q#299747, answer score: 3