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"
>begin transaction;
>   delete from t where id=5;
>   insert into t (id,...) values (5,...);
>   ... [1-200k rows]
>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;

        SELECT ...
          FROM t
         WHERE = 5;

        SELECT ...
          FROM t INNER JOIN idmap ON (idmap.internalid = AND
         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:
        UPDATE idmap SET active = false WHERE visibleid = 5 AND active;
        UPDATE idmap SET active = true WHERE internalid = 12345;

Do the cleanup in off-peak hours (pseudocode):

        FOR delid IN (SELECT internalid FROM idmap WHERE NOT active)
                DELETE FROM t WHERE id = delid;
                DELETE FROM idmap WHERE internalid = delid;
        VACUUM ANALYSE idmap;

To prevent this cleanup from interfering with INSERTs in progress, you
might want to add a "beinginserted" flag to idmap.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to