Hacker News Reader

Comment by pilif | Hacker News Reader

In that regard, have a look at http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate... which is a very good article explaining why you have to continue to wait.

Getting MERGE right is one of these "pick two" things. You get to chose between "fast", "doesn't corrupt data" and "doesn't throw errors at times" but you only get to pick two.

View on HN

Pretty much every Pg related post on depesz.com is a gold mine. You should read them all, especially where he explains upcoming features in new Pg releases. I've learned a ton simply be browsing through his site, which is very readable with great examples to put the concepts into practice.

I pick correct semantics NOW and fix performance issues at your leisure.

upsert has been an embarrassment for Postgres for far too long now. Particularly obnoxious was the "upsert is hard! Roll your own" stance taken.

A completely reasonable stance.

However, it's not so much a performance question as one of concurrency. The easiest way to get the right semantics is by locking the entire destination table against concurrent writes. Maybe that's fine for a lot of users. But it has caused some hesitation, because other users might be disappointed.

It would be fair to say "just do it" though.

Part of the motivation behind PgREST is to get a reusable and generic enough upsert implementation as a stored procedure; see https://github.com/pgrest/pgrest/blob/master/test/upsert.ls for details.

That's a really great blog post, as well as the most informative blog I've seen on postgresql in general! Thanks for the link!

Reply on news.ycombinator.com