Re: Running vacuumdb -a taking too long

On Mon, Jul 27, 2009 at 02:21:02PM -0600, Keaton Adams wrote:
> We are upgrading to 8.3.7 in September, if that helps the situation at all.

This is good. Since 8.2 VACUUM age is done per table instead of per
database. This should solve most of your problems.

> So my questions are:
>
> 1. Will the Postgres cluster eventually shut down because I never
> do a true "database-wide VACUUM" using a vacuumdb -a command on all
> of the databases, even though I vacuum the tables in the production
> database that have a lifespan of greater than 14 days?

On older versions you need to do a database-wide vacuum (note this is
not vacuumdb -a) once every billion transaction.

Did you take the advice in the email you responded to with respect to
speeding up vacuum? And using

> > SELECT datname, age(datfrozenxid) FROM pg_database;

to determine if it's an actual problem (just post the results if you
can't interpret them).

> 2. Would I ever be at risk of losing data in a table that is only
> around for a two week (14 day period) if I never do this "database
> wide VACUUM" on the actual production DB?

You won't lose data, but you need to do a DB wide (not cluster-wide)
vacuum to advance the wraparound counter...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.