Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE
Query 3 is the Postgres syntax for "UPSERT" (= UPDATE or INSERT), introduced in Postgres 9.5.
From the documentation:
ON CONFLICT DO UPDATE
guarantees an atomicINSERT
orUPDATE
outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known asUPSERT
– “UPDATE
orINSERT
”.
This is the best practice for what you are trying to achieve.
I noticed/tested that is much faster for INSERTS (have yet to test UPSERTS) to use a WHERE NOT EXISTS in addition to ON CONFLICT. Typically about 3x faster than just allowing the ON CONFLICT to handle existence checks. I think this may carry over into UPSERTS, making it likely faster to do an INSERT and then and UPDATE. Here is my test for inserts only...
--so i can keep rerunning DROP TABLE if exists temp1; DROP TABLE if exists temp2; --create a billion rows SELECT GENERATE_SERIES AS id INTO TEMP temp1 FROM GENERATE_SERIES(1, 10000000); CREATE UNIQUE INDEX ux_id ON temp1(id); ALTER TABLE temp1 CLUSTER ON ux_id; --create a second table to insert from, with the same data SELECT * INTO TEMP temp2 FROM temp1; CREATE UNIQUE INDEX ux_id2 ON temp2(id); ALTER TABLE temp2 CLUSTER ON ux_id2; --test inserting with on conflict only INSERT INTO temp1(id) SELECT id FROM temp2 ON conflict DO nothing; --execution time: 14.71s (1million rows) --test inserting with not exists and on conflict INSERT INTO temp1(id) SELECT t2.id FROM temp2 t2 WHERE NOT EXISTS (SELECT 1 FROM temp1 t1 WHERE t2.id = t1.id) ON conflict DO nothing; --execution time: 5.78s (1million rows)