Re: High activity short table and locks

Guillaume Bog wrote:
> It seems I'm a bit stuck here. I'd appreciate some help. My main general
> question is "how to handle very small but hot status table that has to be
> updated every 30 seconds by 100 different persons, read and updated from
> many sides, and also joined with some more common tables (i.e. much larger
> but less hot)"

Remove all indexes except the one backing the primary-key. Run a VACUUM
FULL and REINDEX or CLUSTER the table. Vacuum *very frequently* -
you'll want custom values in pg_autovacuum. Add indexes back one at a
time to see what's really necessary. If you can keep the dead rows to a
reasonable level, I'd have thought you could get by without indexes.

You might want to consider setting synchronous_commit=off for updates to
the table. I'm assuming the information in the table isn't vital in the
event of a system crash, and that could reduce WAL activity if you're
limited by disk bandwidth.

--
Richard Huxton
Archonet Ltd