Being a relative newcomer to PostgreSQL, can someone give me quick tour of the vacuum function. The application that I built uses PostgreSQL as a statistics database (running on RedHat 6.2). It will typically make 10000-20000 SQL updates/inserts per hour. This is fine at first, but as the day progresses, the updates start falling behind, and the CPU usage by the postmaster process starts heading toward 70-80%. When I do a vacuum, the updates speed up dramatically, and the cpu usage goes back down to about 10%.
The questions are:
Is there a general rule on how often to run a Vacuum?
Is there an Auto Vacuum setting to have the postmaster to this automatically?
Is there a better approach to keeping this situation from happening (like bundling many updates into a single transaction)?
Any info would be appreciated.
John Sokel