Autovacuum does takes care of dead tuples and return space to table's allocated
size and can be re-used by fresh incoming rows or any updates.
Index bloat is still not being taken care of by autovacuum process. You should
use pg_repack to do index rebuild. Keep in mind that pg_repack requires double
the space of indexes, since there will be two indexes existing during rebuild
processes.
Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 2:26:11 PM PDT, Kevin Brannen
<kbran...@efji.com> wrote:
From: Michael Loftis <mlof...@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.
Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages
attached to it, may contain confidential information. If you are not the
intended recipient, or a person responsible for delivering it to the intended
recipient, you are hereby notified that any disclosure, distribution, review,
copy or use of any of the information contained in or attached to this message
is STRICTLY PROHIBITED. If you have received this transmission in error, please
immediately notify us by reply e-mail, and destroy the original transmission
and its attachments without reading them or saving them to disk. Thank you.
