News for hackers. Stuff that matters.

A comment on How Postgres chooses which index to use for a query. Parent comment

Re:How Postgres chooses which index to use for a query by dspillett on Friday April 22, 2022 @03:05AM (#31119069)
> use with a merge statement in order to get an atomic upsert - holdlock in sql server

Because of all the potential edge cases that MERGE didn't handle properly when introduced to SQL Server (2008?), I've avoided its use. Despite several revisions over the years there are apparently still significant behaviours that I'd consider bugs¹, some of which have been closed as WONTFIX² so are a fact of life you just have to accept if you use MERGE in SQL Server.

[1] see https://sqlsunday.com/2021/05/04/how-merge-can-deadlock-you/ & https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-... and many other similar articles

[2] https://www.mssqltips.com/sqlservertip/3074/use-caution-with...

[ Parent | on HN ]
Re:How Postgres chooses which index to use for a query by gfody on Friday April 22, 2022 @02:49PM (#31126317)
I'm in an agree-to-disagree state with various sql server consultants on this matter. I disagree that merge is harmful and should be avoided. Using merge w/hints for upserts has a decent performance advantage over the alternatives and comes with some theoretical risk of deadlocks that depend on implementation details of your use case and/or the shape of your data (I've yet to encounter it in practice, I tend to batch upserts and limit concurrency and suspect the folks having trouble do the opposite)
[ Parent | on HN ]