On Wed, Jul 23, 2008 at 4:50 PM, Richard Huxton <dev@archonet.com> wrote:
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.
Yes, such a small table, very frequently updated, would suggest no index at all. I understand that I may have many dead rows, that would explain that fact that indexes do speed up the selects. We have a daily VACUUM FULL on the database, but it may be not enough. I'll check tomorrow if a verboze vacuum tell me that many rows are dead. The problem is that I need to work directly on production server, as everything goes very well when there is not enough people actually working.
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.
Yes, this table's data is very short lived and can be lost without problem in case of a crash. I could even have no WAL at all for this table if it is possible. In my mind, this data should be stored and modified in a fixed chunk of RAM and never go to hard-drive.
Thanks for your help. I'll try your suggestions tomorrow.