Marinos, while you are busy answering my first set of questions :-),
here is an idea that might help even out resource consumption.
On Fri, 13 Feb 2004 01:58:34 +0100, "Marinos J. Yannikos"
<[EMAIL PROTECTED]> wrote:
>begin transaction;
> delete from t where id=5;
> insert into t (id,...) values (5,...);
> ... [1-200k rows]
>end;
>
>The problem is, that a large update of this kind can delay SELECT
>queries running in parallel for several seconds, so the web interface
>used by several people will be unusable for a short while.
CREATE TABLE idmap (
internalid int NOT NULL PRIMARY KEY,
visibleid int NOT NULL,
active bool NOT NULL
);
CREATE INDEX ipmap_visible ON idmap(visibleid);
Populate this table with
INSERT INTO idmap
SELECT id, id, true
FROM t;
Change
SELECT ...
FROM t
WHERE t.id = 5;
to
SELECT ...
FROM t INNER JOIN idmap ON (idmap.internalid = t.id AND
idmap.active)
WHERE idmap.visibleid = 5;
When you have to replace the rows in t for id=5, start by
INSERT INTO idmap VALUES (12345, 5, false);
Then repeatedly
INSERT INTO t (id, ...) VALUES (12345, ...);
at a rate as slow as you can accept. You don't have to wrap all INSERTs
into a single transaction, but batching together a few hundred to a few
thousand INSERTs will improve performance.
When all the new values are in the database, you switch to the new id in
one short transaction:
BEGIN;
UPDATE idmap SET active = false WHERE visibleid = 5 AND active;
UPDATE idmap SET active = true WHERE internalid = 12345;
COMMIT;
Do the cleanup in off-peak hours (pseudocode):
FOR delid IN (SELECT internalid FROM idmap WHERE NOT active)
BEGIN
DELETE FROM t WHERE id = delid;
DELETE FROM idmap WHERE internalid = delid;
END;
VACUUM ANALYSE t;
VACUUM ANALYSE idmap;
To prevent this cleanup from interfering with INSERTs in progress, you
might want to add a "beinginserted" flag to idmap.
HTH.
Servus
Manfred
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
