Re: Auto Vacuum

On Fri, Oct 19, 2012 at 11:49 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Oct 18, 2012 at 2:13 AM, teyking2003 <tchongking(at)hotmail(dot)com> wrote:
>> Can anyone provide me a sample of auto vacuum and also step by step to
>> configure it inside my linux ubuntu server?
>
> Here's the basic instructions on autovacuum for pgsql 8.2. Note that
> since it's out of support, if you run into any bugs or problems with
> it you're likely to be told to upgrade.
>
> http://www.postgresql.org/docs/8.2/static/runtime-config-autovacuum.html
>
> As long as it's on (first setting on that page) the default values are
> fine for most low to medium level usage patterns. If you are doing
> enough work that you need to tune autovacuum, then you DEFINITELY need
> to upgrade to a more recent version of pgsql than 8.2.
>
> The biggest issue with 8.2 is that autovacuum is not multi-threaded.
> One long running vacuum thread can allow multiple tables to get
> bloated in a busy server. Also some bugs in vacuum have been fixed
> since 8.2 was end of lifed, so you may run into them as well.

As a followup, you can use this page:

http://wiki.postgresql.org/wiki/Show_database_bloat

and the referenced check_postgresql script:

http://bucardo.org/wiki/Check_postgres

To keep track of how much bloat is in your db. I highly recommend
check_postgresql.