r.m.guerrero@xxxxxxxxxxx (Rafael Martinez) writes: > Heikki Linnakangas wrote: >> On a small table like that you could run VACUUM every few minutes >> without much impact on performance. That should keep the table size in >> check. >> > > Ok, we run VACUUM ANALYZE only one time a day, every night. But we would > espect the performance to get ok again after running vacuum, and it > doesn't. Only CLUSTER helps. You have characterized the shape of the problem Right There. If you only VACUUM that table once a day, then it has a whole day to get cluttered with dead tuples, which increases its size to encompass 651 pages, and NOTHING ever allows it to shrink back to a small size. Plain VACUUM (or VACUUM ANALYZE) does not attempt to shrink table sizes. Only VACUUM FULL and CLUSTER do that. Here are some options to "parameterize" your choices: - If you vacuum the table often enough that only 10% of the table consists of dead tuples, then you can expect the table to perpetually have 10% of dead space. - If you vacuum the table seldom enough that 90% of the table may be expected to consist of dead tuples, then you can expect this table to consistently have 90% of its space be "dead." It sounds like this particular table needs to be vacuumed quite a bit more frequently than once a day. On our systems, we have certain tables where tuples get killed off so frequently that we find it worthwhile to vacuum those tables once every two to three minutes. If we didn't, we'd see application performance bog down until it forced us to CLUSTER or VACUUM FULL the table. -- "cbbrowne","@","acm.org" http://linuxfinances.info/info/linux.html "How much more helpful could I be than to provide you with the appropriate e-mail address? I could engrave it on a clue-by-four and deliver it to you in Chicago, I suppose." -- Seen on Slashdot... ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
