Re: When does VACUUM FULL not clean out all deleted data?

On Jan 4, 2008 9:35 AM, James Cloos <cloos(at)jhcloos(dot)com> wrote:
> >>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> >> Why was the db using that extra five plus gigs?
>
> Tom> Smells like a hadn't-been-vacuuming-often-enough problem.
>
> Sorry, I forgot to mention: autovacuum was enabled and I also ran
> this script every night via cron:
>
> #!/bin/bash
> FULL=''
> test $(date +%w) -eq 0 && FULL='FULL'
> exec psql -U dbm -c "VACUUM ${FULL} VERBOSE ANALYZE" dbm

Note that routine vacuum FULL is not a good idea actually. vacuum
full is something you run when things have gone wrong (i.e. not enough
regular vacuuming) and it is usually best followed by reindexing all
your indexes.

It's far better to keep track of bloat and run vacuum full, if at all,
by hand, and only when needed. Running it regularly with no
reindexing often results in index bloat which makes the database
slower not faster.