Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 11/18/2013 06:44 AM, Heikki Linnakangas wrote:
> I think it's important to recap the design goals of this. I don't think
> these have been listed before, so let me try:
>
> * It should be usable and perform well for both large batch updates and
> small transactions.
>
> * It should perform well both when there are no duplicates, and when
> there are lots of duplicates
>
> And from that follows some finer requirements:
>
> * Performance when there are no duplicates should be close to raw INSERT
> performance.
>
> * Performance when all rows are duplicates should be close to raw UPDATE
> performance.
>
> * We should not leave behind large numbers of dead tuples in either case.

I think this is setting the bar way too high for an initial feature.
Would we like to eventually have all of those things? Yes. Do we need
to have all of them for 9.4? No.

It's more useful to measure this feature against the current
alternatives used by our users, which are upsert functions and similar
patterns. If we can make things easier and more efficient than those
(which shouldn't be hard), then it's a worthwhile step forwards.

That being said, the other requirement I am concerned about is being
able to support the syntax of this feature in commonly used ORMs. That
is, can I write a fairly small Django or Rails extension which does
upsert using this patch? Fortunately, I think I can ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com