Thread: Vacuum problem

Hello!

Our 300GB database requires vacuum about once a week to prevent
transaction id wrap around. Database is heavily updated and sometimes
vacuuming takes over 12 hours.

We have a major problem with a table that tracks online users
(user_online). A row is inserted when a user logs in and a "seen" stamp is
updated every five minutes if he/she loads any pages. Periodical cron job
deletes all idle users - rows that have "seen" older than 20 minutes.
Frequent vacuuming is required to delete old row versions. We have usually
about 20k users online.

However, database wide vacuum prevents user_online vacuum from deleting
old row versions and the table gets incredibly bloated. Queries involving
the user_online practically cease to work.

Any suggestions for this problem?

Btw, we have PostgreSQL 8.1.


     |\__/|
     ( oo )    Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""

Kari Lavikka <tuner@bdb.fi> writes:
> However, database wide vacuum prevents user_online vacuum from deleting
> old row versions and the table gets incredibly bloated.

Update to 8.2.  There was a fix put in for this specific issue.

            regards, tom lane