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

On Tue, Nov 26, 2013 at 9:11 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> * 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.

The requirements around performance/bloat have a lot to do with making
the feature work reasonably well for multi-master conflict resolution.
They also have much more to do with the worst case than the average
case. If the worst case really is terribly bad, that ends up being a
major gotcha. I'm not concerned about bloat as such, but in any case
whether or not Heikki's design can mostly avoid bloat is, for now, of
secondary importance.

I feel the need to re-iterate something I've already said: I don't see
that I have a concession to make here with a view to pragmatically
getting something useful into 9.4. I am playing it as safe as I think
I can.

> 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.

Actually, it's very hard. I don't have license to burn through xids.

--
Peter Geoghegan