Re: VACUUM, 24/7 availability and 7.2

Ian Barwick <SUNGLASSESbarwick(at)gmx(dot)net> writes:

> PostgreSQL would be my primary candidate. However the company's
> operating requirments mean that the data needed for interaction
> with customers / website users must be available on a 24/7 basis.
> This is primarily a) data related to product ordering and
> tables for storing order data; and b) website user authentication
> and personalisation data (logins, user preferences etc).
>
> It is therefore not an option to have these databases offline
> at regular intervals for any significant length of time for
> VACUUMing. Replicating data to say MySQL databases is
> technically feasible, at least in the case of b) above, but
> not desirable. Are there any existing "native" PostgreSQL solutions
> to this problem?

VACUUM doesn't require taking the DB offline. It does lock tables
while it's vacuuming them, which may or may not cause problems
depending on how long the vacuum takes. You seem to understand this;
I'm just correcting your terminology. ;)

You can reduce the time taken by VACUUM by running it more often,
which may make it a non-issue for you. Depends entirely on table size
and user activity.

> More importantly, what is the situation on VACUUM for release 7.2?
> It seems from the pgsql-hackers list that there are plans for
> a none-exclusively locking VACUUM, e.g.:

As I understand it:

* Non-intrusive VACUUM will be in 7.2
* 7.2 is about to enter beta (this week hopefully)

I would say it's likely that beta will last two or three months based
on my experience with the 7.1 beta period.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863