On Mon, Dec 14, 2009 at 12:04 PM, Kevin Grittner
<Kevin.Grittner@wicourts.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:
It ends with a post by Simon Riggs noting that VACUUM FULL doesn't limit itself to maintenance_work_mem anyway.
David