Re: Autovacuum Improvements

No, how dirty a table isn't subjective, what is subjective is the
question "Does it need to be vacuumed?".  A that is 1% dirty (to use
your term) probably doesn't *need* to be vacuumed, but you might choose
to vacuum it anyway at least you might at night when the system isn't in
use.

See the docs for the current requirements for autovacuum to issue a
vacuum command.

Matt

Brandon Aiken wrote:
> You're saying that the dirtyness of a table is proportional to when you
> plan on vacuuming it next.  I don't see that connection at all.  The
> only correlation I might see is if it happens to be 5:59 AM when your DB
> decides your table is dirty, and your maintenance window closes at 6:00
> AM.  Then you have to program the maintenance window to gracefully
> unplug the vacuum.
>
> Currently, autovacuum runs every minute and checks to see if any tables
> meet the requirements for vacuuming.  Are the requirements the amount of
> time a vacuum would take, or the raw number of dirty tuples?  One might
> be a function of the other, for sure, but exactly what does the
> autovacuumer use to decide when to clean?
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Matthew
> O'Connor
> Sent: Tuesday, December 19, 2006 5:37 PM
> To: Glen Parker
> Cc: Postgres general mailing list
> Subject: Re: [GENERAL] Autovacuum Improvements
>
> Glen Parker wrote:
>> Erik Jones wrote:
>>> Matthew O'Connor wrote:
>>>> Glen Parker wrote:
>>>>> If it isn't there somewhere already, I would ask to add:
>>>>>  Expose a very easy way to discover autovacuum's opinion about a
>>>>> particular table, for example "table_needs_vacuum(oid)", ignoring
>>>>> any time constraints that may be in place.
>>>> This might be a nice feature however in the presence of the much
>>>> talked about but not yet developed maintenance window concept, I'm
>>>> not sure how  this should work.  That is, during business hours the
>>>> table doesn't need vacuuming, but it will when the evening
>>>> maintenance window opens up.
>>  >
>>> Well, what he's saying is, "Not taking into account any
>>> time/maintenance windows, does this table need vacuuming?"
>> Correct.  IOW, "does it need it?", not "would you actually do it at
> this
>> time?"...
>
> I understand that, but it's a subjective question.  The only question
> autovacuum answers is "Am I going to vacuum this table now?", so in the
> current setup you probably could create a function that answers your
> question, I was just pointing out in the future when maintenance windows
>
>   get implemented that this question becomes less clear.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>