[btown]

Fun fact: if you do a lot of INSERT... ON CONFLICT calls in Postgres from automated systems that are updating much more often than you insert, your autoincrement primary key can increment far far faster than your data volume (since it doesn't de-increment on a conflict) and overflow an int, grinding things to a halt. One of the more maddening outages I've had to deal with!

[hu3]

Similar for MySQL.

If you open a transaction, INSERT with AUTO_INCREMENT, then rollback the transaction, no data is saved, except the auto generated id is used and the next INSERT uses id+1.

[bcrosby95]

But MySQL's equivalent, insert...on duplicate, does not cause extra auto increments. IMHO postgres' is a much larger problem.

That said, I say that in my experience based upon locking selects in MySQL - when the row doesn't exist is a big problem, oftentimes leading to deadlocks. So insert... on duplicate key is a life saver. I don't have as much experience using postgres, so I'm not sure how much of a problem that is there, so it's possible the "on conflict" isn't as useful.

[ryanianian]

Same with sequences in Oracle. They don't participate in transactions. Looking at the gaps between "consecutive" IDs shows surprisingly interesting behavior.