Re: VACUUM FULL memory requirements

On Mon, Dec 14, 2009 at 12:04 PM, Kevin Grittner <
Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> I hope you've been following that with a REINDEX every time;
> otherwise you're causing index bloat.

Yes, it REINDEXes afterwards.

Are these inserts happening in the same table(s) each time? If so,
> what are you gaining by moving the space from the database's free
> space manager to the file system's free space management (and back
> again) each time?

Normally the database will see inserts throughout each day, with older data
deleted at the end of the day. It's not a sharp cutoff, which is why moving
over to partitions is a little trickier than in most such cases.

Regular VACUUM is fine most of the time; it frees up space for re-use, the
space gets re-used, and the disk size stays constant. But at certain
non-predictable points in time, the database may expire several times more
rows than usual, and in that case I want to reclaim the space for the OS,
since it may not be used by the database again for some time.

Is my understanding of VACUUM v.s. VACUUM FULL correct, actually? It
appears to work that way, but the docs are a little vague on whether plain
VACUUM ever frees actual disk space, or just reclaims it for the DB.

You might want to reduce maintenance_work_mem. See this thread:
> http://archives.postgresql.org/pgsql-performance/2009-12/msg00120.php

I saw that, but I think it's unlikely to be what's happening. That case had
many databases running VACUUM simultaneously, with a maintenance_work_mem of
256MB. I have just a single database and maintenance_work_mem is at
default, which is something like 16 or 32 MB I believe.

The thread below appears to be the same question asked on the PG-hackers
list:

http://old.nabble.com/Re:-VACUUM-FULL-out-of-memory-td14895423.html

It ends with a post by Simon Riggs noting that VACUUM FULL doesn't limit
itself to maintenance_work_mem anyway.

David