Re: how to slow down parts of Pg - Mailing list pgsql-general

From: Michael Loftis <mloftis@wgops.com>

>>From: Kevn Brannen

>> I don't particularly like doing the vacuum full, but when it will release 20-50% of disk space for a large table, then it's something we live with. As I understand, a normal vacuum won't release all the old pages that a "full" does, hence why we have to do that. It's painful enough I've restricted it to once quarter; I'd do it only once a year if I thought I could get away with it. Still this is something I'll put on the list to go research with practical trials. I don't think the lock for the vacuuming hurts us, but I've heard of pg_repack and I'll look into that too.

> Why do vacuum full at all? A functional autovacuum will return the free pages to be reused. You just won’t see the reduction in disk usage at the OS level. Since the pages are clearly going to be used it doesn’t really make sense to do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping up a normal vacuum without the full. The on dusk sizes will stabilize and you’ll not be doing a ton of extra I/O to rewrite tables.

Sometimes I need the disk space back. It also makes me feel better. (OK, this may not a good reason but there is a hint of truth in this.) What this probably means is that I need to get a better understanding of vacuuming.