Re: vacuum confusion

Thanks Richard, I found the page too...

However the implication (and my confusion) is that you need to vacuum
your own databases only. It's not clear (to me) that you have to do the
same for template0 and template1 as well.

Perhaps when someone is updating the docs, something more explicit than this

<OLD> Recommended practice for most sites is to schedule a database-wide
VACUUM once a day at a low-usage time of day, supplemented by more
frequent vacuuming of heavily-updated tables if necessary. (If you have
multiple databases in a cluster, don't forget to vacuum each one; the
program vacuumdb may be helpful.) Use plain VACUUM, not VACUUM FULL, for
routine vacuuming for space recovery.</OLD>

could be replaced by this

<NEW> Recommended practice for most sites is to schedule a database-wide
VACUUM once a day at a low-usage time of day, supplemented by more
frequent vacuuming of heavily-updated tables if necessary. (If you have
multiple databases in a cluster, don't forget to vacuum each one
(INCLUDING template0 and template1); the program vacuumdb may be
helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for
space recovery.</NEW>

And perhaps an explicit reference to vacuuming template0/1 in the
section on "Preventing transaction ID wraparound failures" would be helpful.

I'll add a weekly cron job to vacuum these two template databases.

Thanks for your help again.

John Sidney-Woollett


Richard Huxton wrote:

> John Sidney-Woollett wrote:
>
>> Ah.., no we're not vacuuming template0 or 1.
>>
>> I didn't realise that the transaction ID would be stored here - I
>> assumed that they'd be in our database.
>>
>> Do I need to need to (plain) vacuum, or vacuum full these template0
>> and template1? And is this something that can be done once a week
>> rather than every night (like our main database)?
>
>
> Ah! Found the section of the manuals - see "Routine Database Maintenance
> Tasks" for details.
>
> AFAIK it's a simple vacuum and once a week is more than enough. The
> manual recommends once every 500million transactions, though you can
> leave it longer.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org