Markus Benne <[EMAIL PROTECTED]> writes: > We have a highly active table that has virtually all > entries updated every 5 minutes. Typical size of the > table is 50,000 entries, and entries have grown fat.
> We are currently vaccuming hourly, and towards the end > of the hour we are seeing degradation, when compared > to the top of the hour. On something like this, you really need to be vacuuming more often not less so; I'd think about how to do it every five or ten minutes rather than backing off. With only 50K rows it should really not take more than a couple of seconds to do the vacuum. When you wait till there are 600K dead rows, it's going to take awhile, plus you are suffering across-the-board performance degradation from all the dead rows. If you are using PG 8.0, there are some "vacuum cost" knobs you can fiddle with to slow down vacuum so it doesn't impose as much I/O load. Ideally you could get it to where you could run vacuum as often as you need to without noticing much impact on foreground processing. If you're not using 8.0 ... maybe it's time to update. Another thing you might want to do is look at "vacuum verbose" output, which will give you some idea of the time spent in each step. It might be there are specific aspects that could be improved. > We are thinking of splitting the table in two: the > part the updates often, and the part the updates > infrequently as we suspect that record size impacts > vacuum. You just said that virtually all rows update constantly --- where's the "infrequent" part? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq