Most effective insert or replace

From: Sergei Politov <spolitov(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Most effective insert or replace
Date: 2009-07-03 11:06:13
Message-ID: b6a8273d0907030406y1584f5amf0f2abfcbf507abd@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

As far as I recall postgres does not have built-in support for "insert or
replace" feature.
But there is a lot of ways to obtain the same result.
The problem is that performance of these techniques is quite bad.
It is about two times slower than simple insert.

I tried the following ways:
1) Add the following rule on insert:
CREATE RULE replace_dummy AS
ON INSERT TO dummy
WHERE
EXISTS(SELECT 1 FROM dummy WHERE key = NEW.key)
DO INSTEAD
(UPDATE dummy SET value = NEW.value);

2) Use the function:
CREATE FUNCTION merge_dummy(ikey int, ivalue text) RETURNS VOID AS
$$
BEGIN
UPDATE dummy SET value = ivalue WHERE key = ikey;
IF found THEN
RETURN;
END IF;
INSERT INTO dummy VALUES (ikey, ivalue);
RETURN;
END;
$$
LANGUAGE plpgsql;
3) Last the most effective in a short period, but seems produces a lot of
work for vacuum.
Add extra column, (time int) into table. I can guarantee that next
insert has time greater than previous.
And use the following rule:
create rule dummy_insert as on insert to dummy do also
delete from dummy
where key == NEW.key and time != NEW.time;

Please comment these ways and propose effective ways to simulate "insert
or replace" behavior.
Also in may case I'm making a lot of inserts in a batch.

Note: insert or replace I meant.
Suggest we have:
dummy with columns: key int, value text.
Filled with:
insert into dummy values (1, "one"), (2, "two"), (3, "three")
When user tries to "insert or replace" pair into this table then in should
be inserted if there is no row with the same key.
Otherwise value of appropriate row is updated.

Best Regards,
Sergei

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Wakeling 2009-07-03 11:20:50 Re: Most effective insert or replace
Previous Message Scott Marlowe 2009-07-03 00:21:16 Re: slow DELETE on 12 M row table