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
