I tend to think that UPSERT is the right solution to the wrong problem.

Take the example of a user profile. The problem for me is not typically "the first time the user shows up, I need to create a record, and all other times, I need to overwrite it." In the systems I build, there is almost always a use case for having full access to the history of data. So the better solution is, "I must ALWAYS update whatever record exists AND insert a new record".

That means my UserProfile table also has two extra columns, CreatedOn and InvalidatedOn. Then, creating/updating a user's email address is always the same, two statement operation:

    -- If UserName does not yet exist, this does nothing
    UPDATE UserProfile
    SET InvalidatedOn = NOW()
    WHERE UserName = ?
    AND NOW() BETWEEN CreatedOn AND InvalidatedOn;

    INSERT INTO UserProfile(UserName, Email, <etc.>, CreatedOn, InvalidatedOn)
    VALUES(?, ?, <etc.>, NOW(), MAX_DATE());

In practice I would use default value constraints for the CreatedOn and InvalidatedOn columns. Writing it explicitly is just for this example.

This becomes really important once you've lived with your database for several months/years, and especially when dealing with customers. For example, say you send an important email to a user, then they change their email address, then you send more important emails referring to the first one, which they now claim they never received in the first place. With an UPSERT, that user's email address for all of time looks like the most current version, and you can't figure out why they didn't receive the first one.

It's better to capture more data and realize down the road that you don't need it than it is to not capture enough and realize you need more. Databases are temporal objects. They grow over time and they change over time.

Another example from my past: a client would send us a dump of their data, every morning. We were performing analysis of their data and were supposed to wipe out our database and re-import it every morning. This meant on day N+1 we were re-importing the first N days again. I implemented this as a temporal database, which caught me some hell when the database grew in size. But, after 6 months, we discovered someone had attempted to alter the historical data. If we had blindly taken the data, they would have gotten away with their crime (and it literally was a crime, we had several government regulations we had to fulfill, the project being used in both a health-care and a financial capacity).

I've never regretted implementing my tables temporally. I've sometimes changed the implementation to a data warehouse setup, after learning that the historical data wasn't necessarily important to day-to-day operation, but I've never regretted having the historical data available, and I have almost always learned to regret NOT having historical data.