Re: vacuum, vacuum full and problems releasing disk space

thanks Laurenz, I'll keep an eye on pg_stat_all_tables. I checked just
now and apparently autovacuum has been ran, but this is after my recent
upgrade to 9.1 from 8.3 (I upgraded hoping this problem would go away
and so far the application hasn't been running for long enough for me to
confirm whether it's gone or not).
I don't see any clients idle in connection and I don't think I use long
running transactions. I may be using a lot of short-lived transactions 24x7.

If pg_stat_all_tables has a date on last_autovacuum, does this mean all
deleted tuples should be marked for reuse or is there any scenario where
autovacuum runs but some deleted tuples are not marked so the space is
being reused at some point? In other words, if last_autovacuum has a
recent date, can I forget about checking idle clients or long/short
running transactions or can this still be a problem even if
last_autovacuum shows autovacuum ran?

thanks,

H


On 10/05/12 16:50, Albe Laurenz wrote:
> Horaci Macias wrote:
>> 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?)
> That is possible.
>
> You can check the last_autovacuum field in pg_stat_all_tables to see
> when the table was last vacuumed.
> Do you have any long running transactions? Either long statements or
> sessions that are "idle in connection". Those can also block vacuum.
> Do you use two phase commit?
>
> I would try to make autovacuum more aggressive (see the documentation)
> and see if that helps.
>
>> 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?
> You need VACUUM FULL once the bloat of the table is unacceptable,
> i.e. if you don't want to leave the empty space in the tables but
> want to return it to the operating system.
>
>> * 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?
> If you cannot keep up using autovacuum, that will be the other option.
>
> If you want to run VACUUM, say, once daily, cron is a good way to do it.
> If it turns out to be necessary, you can block new connections with
> pg_hba.conf or by revoking connect permissions on the database.
>
> Yours,
> Laurenz Albe