Postgres Pro
Downloads
Home   >   mailing lists

Thread: When to Vacuum

When to Vacuum

From
Medi Montaseri
Date:
I posted this on 1/20 but never got delivered....trying again....

I was wondering if I can use any of the pg_stat* related tables to
determine if I
need to vacuum or not.

More specifically, instead of assuming a period (based on one's usage),
if I was
to vacuum more adaptively, I could have a cron job to check certain
statistics table
every 10 minutes (or hour) and if a threashold is reached a vauum be
performed.

If this is possible, please specifiy the condition for vacuum and vacuum
full please.

Thanks

Re: When to Vacuum

From
Bruce Momjian
Date:
Yes.  The pg_stat tables do have info on the number of deleted rows,
stuff like that.  You can use that to determine the number of number of
changes since you last vacuumed.  There is also /contrib/pgstattuple
that shows table info.

---------------------------------------------------------------------------

Medi Montaseri wrote:
> I posted this on 1/20 but never got delivered....trying again....
>
> I was wondering if I can use any of the pg_stat* related tables to
> determine if I
> need to vacuum or not.
>
> More specifically, instead of assuming a period (based on one's usage),
> if I was
> to vacuum more adaptively, I could have a cron job to check certain
> statistics table
> every 10 minutes (or hour) and if a threashold is reached a vauum be
> performed.
>
> If this is possible, please specifiy the condition for vacuum and vacuum
> full please.
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: When to Vacuum

From
Medi Montaseri
Date:
Interesting...

So who/where is RelationGetNumberOfBlocks() defined, are these actual
source codes
that makeup the postgres/postmaster ?


Bruce Momjian wrote:

>Yes.  The pg_stat tables do have info on the number of deleted rows,
>stuff like that.  You can use that to determine the number of number of
>changes since you last vacuumed.  There is also /contrib/pgstattuple
>that shows table info.
>
>---------------------------------------------------------------------------
>
>Medi Montaseri wrote:
>
>
>>I posted this on 1/20 but never got delivered....trying again....
>>
>>I was wondering if I can use any of the pg_stat* related tables to
>>determine if I
>>need to vacuum or not.
>>
>>More specifically, instead of assuming a period (based on one's usage),
>>if I was
>>to vacuum more adaptively, I could have a cron job to check certain
>>statistics table
>>every 10 minutes (or hour) and if a threashold is reached a vauum be
>>performed.
>>
>>If this is possible, please specifiy the condition for vacuum and vacuum
>>full please.
>>
>>Thanks
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>>
>
>
>