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