Postgres vacuum question (7.2)
jonathan at carnageblender.com
Mon Mar 5 14:17:01 MST 2007
On Mon, 5 Mar 2007 15:51:53 -0500, "Grant Shipley" <gshipley at gmail.com>
> I repeated steps 1-5 and noticed our disk space kept growing 400 meg each
> I found the following options that might help?:
> # - Free Space Map -
> #max_fsm_pages = 20000 # min max_fsm_relations*16, 6
> bytes each
That's the problem, this is way too low when you're deleting millions of
records between vacuums.
"vaccuum full verbose" will tell you what the minimum needs to be. (a
rule of thumb is, 100k for every GB of data.)
> I also read that indexes never free their space? Does anyone know
> more about this?
If you're really still on 7.2 (what is that, 6 years old?), maybe, but I
doubt it. certainly that's not the case in 8.x. Indexes get vacuumed
with the table that owns them.
In any case you should definitely upgrade asap. The planner
improvements alone are worth it.
> This is a heavy used table and I am seeing the data dir taking up 38gb
> of memory after long runs.
If that's really a normal use case (emptying out the whole table), use
truncate instead of delete; you don't need to vacuum after truncate.
(And if it's the similar case of "every so often we delete records older
than some date" then you might look into partitioning on the date, which
is more work but also means you'll never have to vacuum.)
More information about the PLUG