Hi,
Does anyone run a very busy PostgreSQL datatabase, with lots of read
and write operations that run 24x7? (We're talking on the
neighbourhood of 40 to 60 queries/second, with probably 5% to 10% of
them being INSERT or UPDATE.)
Some of our clients run such a DB, and the nightly VACUUM slows things
down tremendously while it is running. I see that in 8.0, you can
reduce the VACUUM's I/O impact, but from reading the code, it also
looks like that means the VACUUM will hold locks for longer, which is
probably bad news.
Doing VACUUM more often than nightly is not a good idea; the tables
tend to be pretty large and it looks like VACUUM has to scan all the
tuples each time. (The nightly VACUUM is already taking several hours
in some cases.)
How do we handle this situation? Are there any plans for some kind
of "incremental" vacuum that recovers a few pages here and there in the
background? Is such a thing even possible?
If we defer some write operations until after the VACUUM has finished,
will that speed up the VACUUM? There are some things we can save up until
after VACUUM is finished.
Regards,
David.