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?