4 ms·

Is anyone aware of performance benchmarks comparing Postgres's new MERGE to the old INSERT ... ON CONFLICT? The "WHEN NOT MATCHED THEN DO NOTHING" is very appe

Is anyone aware of performance benchmarks comparing Postgres's new MERGE to the old INSERT ... ON CONFLICT? The "WHEN NOT MATCHED THEN DO NOTHING" is very appealing, and we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows.

hn_throwaway_99 3y ago
> we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows. Huh??? Why not just do an UPDATE then? After all, you seem to be saying "we want to do an upsert but without the insert part". Edit: I'm also trying to understand the value of "WHEN NOT MATCHED THEN DO NOTHING". How is that any different that just leaving off the "WHEN NOT MATCHED" clause altogether?
- compumike 3y ago
  Batch update many many rows at once with entirely different values. (Agree that the “WHEN NOT MATCHED DO NOTHING” is effectively the default behavior.) Use case is the Outbound Prober processes for https://heiioncall.com/ https://heiioncall.com/ which can do thousands of HTTP requests per second, then aggregate all those row updates into a single query (or single transaction, which in practice is a single query). We can do these batched updates very, very fast using INSERT … ON CONFLICT. No need to re-parse a differently shaped query, only a single index rebuild. Smooth :)
  - hn_throwaway_99 3y ago
    Still not really understanding - you can do all of that with plain UPDATEs (using join syntax if you want to "batch update many many rows at once with entirely different values.") I would be hella confused as a developer if I saw an INSERT ... ON CONFLICT statement where you specifically wanted to prevent any inserts.
    - compumike 3y ago
      Ah, by “join syntax” do you mean like this answer: https://stackoverflow.com/a/70803859 https://stackoverflow.com/a/70803859 ?
      - hn_throwaway_99 3y ago
        Unnesting is a bit orthogonal, I'm basically saying any UPDATE statement that has a FROM clause in Postgres.

hn_throwaway_99 3y ago

> we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows. Huh??? Why not just do an UPDATE then? After all, you seem to be saying "we want to do an upsert but without the insert part". Edit: I'm also trying to understand the value of "WHEN NOT MATCHED THEN DO NOTHING". How is that any different that just leaving off the "WHEN NOT MATCHED" clause altogether?

compumike 3y ago
Batch update many many rows at once with entirely different values. (Agree that the “WHEN NOT MATCHED DO NOTHING” is effectively the default behavior.) Use case is the Outbound Prober processes for https://heiioncall.com/ https://heiioncall.com/ which can do thousands of HTTP requests per second, then aggregate all those row updates into a single query (or single transaction, which in practice is a single query). We can do these batched updates very, very fast using INSERT … ON CONFLICT. No need to re-parse a differently shaped query, only a single index rebuild. Smooth :)
- hn_throwaway_99 3y ago
  Still not really understanding - you can do all of that with plain UPDATEs (using join syntax if you want to "batch update many many rows at once with entirely different values.") I would be hella confused as a developer if I saw an INSERT ... ON CONFLICT statement where you specifically wanted to prevent any inserts.
  - compumike 3y ago
    Ah, by “join syntax” do you mean like this answer: https://stackoverflow.com/a/70803859 https://stackoverflow.com/a/70803859 ?
    - hn_throwaway_99 3y ago
      Unnesting is a bit orthogonal, I'm basically saying any UPDATE statement that has a FROM clause in Postgres.