Search Postgresql Archives

Re: vacuum, vacuum full and problems releasing disk space

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

after tuning the autovacuum settings I can now see the tables vaccumed and the number of dead tuples dropping whenever an autovacuum happens, which makes sense. What I don't see though is the size of the tables ever decreasing, but I'm not sure I should see this.

Can somebody please confirm whether vacuum (not vacuum full) will ever reduce the size of a table or will the table always have whatever maximum size it ever reached, even if under the hood some inserts don't result in size increasing because space is being reused? For example, starting from an empty table, I insert tuples until the table is 1G in size. Then I insert another bunch of tuples and the table reaches 2G. If I delete this second bunch of tuples and vacuum (not vacuum full) the table, should I expect the table to be ~1G in size again or is it "normal" that the table stays at 2G (although ~1G contains dead tuples)? If I add again the bunch of tuples I deleted, should I expect the table to remain at ~2G (since the dead tuples space was reused) or would the table grow to ~3G? Is there any easy way to see how much of the size of a table is occupied by dead tuples and how much is occupied by live tuples?


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
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
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.
using autovacuum but this doesn't seem to be solving the problem
(perhaps because while vacuum is running the application keeps
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
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
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
rely on cron-type jobs for this? and is there any way to prevent
external connections at certain times of day to make sure
don't happen while vacuum is going, or again I should use cron-type
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.

Laurenz Albe

Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:

[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux