Re: Is there a reason _not_ to vacuum continuously? - Mailing list pgsql-performance

From Josh Berkus
Subject Re: Is there a reason _not_ to vacuum continuously?
Date
Msg-id 200309171313.31078.josh@agliodbs.com
Whole thread Raw
In response to Is there a reason _not_ to vacuum continuously?  ("Matt Clark" <matt@ymogen.net>)
Responses Re: Is there a reason _not_ to vacuum continuously?
List 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


pgsql-performance by date:

Previous
From: "scott.marlowe"
Date:
Subject: Re: Is there a reason _not_ to vacuum continuously?
Next
From: Robert Treat
Date:
Subject: Re: restore time: sort_mem vs. checkpoing_segments