We have a few 8.1 installations where the vacuumdb -a command takes
2-3 days to run ..(with a vacuum delay of 10ms)...autovac does not
work for us as we have tables that get constantly dropped due to
partitioning.(autovac would never finish given the size of our
database and the fact that we have some "idle transactions" caused by
our application server coneection pools.)
We have tables that get dropped every day (partitions) and we have
some big ones that dont (the total table sizes range from 2G to 20G
per table for many tables)..
If we manually schedule vacuums on these large permanent tables..will
a one-time VACUUM in the future be smart to figure out how much
vacuuming has been done and run faster ?
On Tue, Oct 13, 2009 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Anj Adu <fotographs@gmail.com> writes:
>> Assuming that autovacuum is off in 8,2 and upwards versions, would I
>> still have to do a database-wide vacuumdb OR would vacuuming
>> individual tables that are permanent be sufficient to take care of XID
>> wraparound?
>
> In recent releases it is not possible to turn off autovacuum to the
> extent of preventing it from doing anti-wraparound vacuuming, so your
> question is a bit mis-posed. But yes, you do need a database-wide
> manual vacuum if you are trying to forestall automatic anti-wraparound
> vacuuming. Vacuuming individual tables isn't sufficient unless you get
> *every single one*, including the system catalogs.
>
> In practice, I think worrying about this is pointless in modern PG.
> If you want control over the timing of vacuuming on individual large
> tables, do them when you want to. The system will occasionally force
> vacuums on small tables to prevent wraparound, but that isn't going
> to cause you any performance problems.
>
> regards, tom lane
>