On 20 Jun 2005, at 15:59, Jacques Caron wrote:


At 16:44 20/06/2005, Alex Stapleton wrote:

We never delete
anything  (well not often, and not much) from the tables, so I am not
so worried about the VACUUM status

DELETEs are not the only reason you might need to VACUUM. UPDATEs are important as well, if not more. Tables that are constantly updated (statistics, session data, queues...) really need to be VACUUMed a lot.

We UPDATE it even less often.

but I am wary of XID wraparound
nuking us at some point if we don't sort vacuuming out so we VACUUM
at least once every year ;)

That would give you a maximum average of 31 transactions/sec... Don't know if that's high or low for you.

It's high as far as inserts go for us. It does them all at the end of each minute.

 However not running ANALYZE for such huge
periods of time is probably impacting the statistics accuracy
somewhat, and I have seen some unusually slow queries at times.
Anyway, does anyone think we might benefit from a more aggressive
autovacuum configuration?

ANALYZE is not a very expensive operation, however VACUUM can definitely be a big strain and take a looooong time on big tables, depending on your setup. I've found that partitioning tables (at the application level) can be quite helpful if you manage to keep each partition to a reasonable size (under or close to available memory), especially if the partitioning scheme is somehow time- related. YMMV.


That's not currently an option as it would require a pretty large amount of work to implement. I think we will have to keep that in mind though.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to