Hi everybody,
I'm running postgres 9.1 and having disk space problems.
My application captures information 24x7 and stores it into the
database. This includes several bytea and can be ~5M entries a day, so
the size can be an issue after several days.
My application also cleans up entries older than 10 days; it does this
every night and the delete operations are happening successfully. I
cannot truncate the tables as they contain both stale and active data.
The database is able to store all the entries for ~15 days without
problems, but for some reason the deletion of old entries is not freeing
up the space (or the insertion of new entries is not reusing the space
used by old entries) because after running the application for ~20days I
run out of space on disk.
I've been reading on this forum and the postgres documentation; vacuum
full is not recommended and apparently vacuum should be all I need. I'm
using autovacuum but this doesn't seem to be solving the problem
(perhaps because while vacuum is running the application keeps inserting
entries 24x7?)
Just to clarify, I don't really care if the disk space is returned to
the OS; what I need though is to be sure that I can keep a window of 10
days of records (assuming of course my HD is big enough for those 10
days, which seems to be the case).
Some questions:
* Although not being generally recommended, I've read that vacuum full
is sometimes the only choice when large deletions are in place in order
to maintain the database. Is this the case here?
* Should I try to have a "maintenance window" and stop all
inserts/writes while vacuum is running? If so, is there any way to
configure at what time vacuum will be executed by autovacuum or should I
rely on cron-type jobs for this? and is there any way to prevent
external connections at certain times of day to make sure inserts/writes
don't happen while vacuum is going, or again I should use cron-type jobs
for this?
* Any other suggestions/ideas to troubleshoot this or any pointers to
further documentation?
thank you,
Horaci