Re: optimization ideas for frequent, large(ish) updates in frequently accessed DB?

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"
<mjy@geizhals.at> 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