Skip site navigation (1) Skip section navigation (2)

Re: Is there a reason _not_ to vacuum continuously?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Matt Clark" <matt(at)ymogen(dot)net>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Is there a reason _not_ to vacuum continuously?
Date: 2003-09-17 20:13:31
Message-ID: 200309171313.31078.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Matt,

> Is there any reason for me not to run continuous sequential vacuum analyzes?
> At least for the 6 tables that see a lot of updates?

No.  You've already proven that the performance gain on queries offsets the 
loss from the vacuuming.   There is no other "gotcha".   

However: 
1) You may be able to decrease the required frequency of vacuums by adjusting 
your FSM_relations parameter.  Have you played with this at all?  The default 
is very low.
2) Are you sure that ANALYZE is needed?   Vacuum is required whenever lots of 
rows are updated, but analyze is needed only when the *distribution* of 
values changes significantly.
3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes 
often (daily?).   This issue will go away in 7.4, which should make you an 
early adopter of 7.4.

> I hear 10% of tuples updated as a good time to vac-an, but does my typical
> count of 3 indexes per table affect that?

Not until 7.4.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


In response to

Responses

pgsql-performance by date

Next:From: Robert TreatDate: 2003-09-17 20:15:46
Subject: Re: restore time: sort_mem vs. checkpoing_segments
Previous:From: scott.marloweDate: 2003-09-17 19:54:42
Subject: Re: Is there a reason _not_ to vacuum continuously?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group