PostgreSQL中UPSERT哪种方案更优？（多更新少插入场景）

Hey there! Let's break this down tailored to your specific use case—where updates are the overwhelming majority of operations, and inserts are rare. This makes the choice between the two options pretty clear once we dig into the details.

First, Let's Look at Your Proposed "Update First, Insert on Failure" PL/pgSQL Function #

You're considering this function, which prioritizes updates first:

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ 
BEGIN 
  LOOP 
    -- First try to update the existing key
    UPDATE db SET b = data WHERE a = key; 
    IF found THEN 
      RETURN; 
    END IF; 
    -- If the key doesn't exist, attempt to insert it
    -- Handle concurrent unique key conflicts gracefully
    BEGIN 
      INSERT INTO db(a,b) VALUES (key, data); 
      RETURN; 
    EXCEPTION WHEN unique_violation THEN 
      -- Do nothing, loop back to try the UPDATE again
    END; 
  END LOOP; 
END; 
$$ LANGUAGE plpgsql;

Why This Shines for Your Scenario #

• Minimal overhead for common operations: Since most of your actions are updates, this function hits the UPDATE statement first. 99% of the time, it'll find the row, update it, and exit immediately—no unnecessary insert checks or conflict handling to slow things down.
• Concurrency-safe: The loop handles edge cases where another transaction inserts the same key right between your UPDATE and INSERT. The unique_violation exception catches the conflict, and the loop retries the UPDATE (which will now find the newly inserted row and update it correctly).

How Does INSERT ... ON CONFLICT DO UPDATE Compare? #

PostgreSQL's native UPSERT is a clean, atomic one-liner, structured like this:

INSERT INTO db(a, b) VALUES (key, data)
ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;

When It's Less Ideal for Your Case #

• Backwards logic for your workload: This approach always tries to insert first, only switching to update if a unique conflict occurs. Since your operations are mostly updates, nearly every call will trigger a conflict check and then fall back to the update path. This adds consistent, unnecessary overhead that the "update first" approach avoids.
• It's not all bad, though: This native UPSERT is perfect if your insert and update frequencies are balanced, or if you want to skip writing a PL/pgSQL function entirely. Its atomicity is handled natively by PostgreSQL, so you don't have to manage loop/exception logic yourself.

Final Recommendation #

For your specific scenario—most operations are updates, very few inserts—your proposed "update first, insert on failure" PL/pgSQL function will be more performant. It cuts out the unnecessary insert check for the vast majority of your operations, keeping things fast and efficient.

If your workload ever shifts to a point where inserts are more common, switching to INSERT ... ON CONFLICT DO UPDATE would make sense for its simplicity and native atomicity.

内容的提问来源于stack exchange，提问作者Tim Sazon