On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew@xxxxxxxx> wrote: > Rafael Domiciano wrote: >> >> I'm not using autovacuum. Regular vacuum goes ok. >> To see the last 10 lines of verbose i will need to run vacuum tonight >> >> If a run a reindex before the vacuum full, increase the "speed" of doing >> vacuum? I found something about it googling. > > It might help a bit, but by the end of VACUUM FULL you would need to run > reindex again as VACUUM FULL tends to cause a lot of index bloat. It is > normal for tables to have some slack space, so if you do a regular vacuum > every day (or let autovacuum) it's normal for the table to be a bit bigger > than after a VACUUM FULL, but they should ready steady state and stop > growing. But there are certain use cases that would be classified as pathological in nature, that you can't handle with regular vacuum. It's only when you've proven that that's your case, and you can't program around it, that you should start using vacuum full though. vacuum full is so expensive in terms of time the system is largely unusable combined with the need to run reindex or replace the whole thing with cluster, that if regular or autovacuum can handle the load, then that's what you do. I've only seen cases where things like large imports were using a shared table where it would get bloated insanely if three or four imports were running at the same time with the occasional update with no where clause. Since you can't truncate the table, because it's shared with other imports, you have to vacuum it, but if you bloat it by 10x or 100x normal size in 30 seconds, no amount of regular vacuuming will help. So, users need to understand why they're always asked if they're running autovacuum or not. It's like asking someone with a nonfunctioning dryer if they've cleaned the lint trap. It's just something we assume someone should try first unless there's a good reason not to. Because it does work so well most of the time. I run autovacuum. I also email myself the output from vacuum verbose every week, to look through and see how the tables are looking. A quick look near the end tells you if you're mostly ok, and quick investigation can find bloated tables pretty fast. So, for the OP, have you tried autovacuum, and why aren't you using it. Most of the time people aren't running it it's for erroneous reasons. Also, look into updating to 8.3 or above. With its HOT update mechanism, and autovacuum enabled by default it handles these situations quite easily. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
