Re: Cleaning up large objects

Hi Tom,

> "Ole Streicher" <ole-usenet-spam(at)gmx(dot)net> writes:
> To aggressively compact table files and return space to the OS, you need
> vacuum full, which is a great deal slower and requires exclusive table
> locks.

OK; this freed the space while running for about four hours.

> If you see space leakage in a database that should have a reasonably
> constant total size, the reason is usually that you don't have the FSM
> parameters set large enough to remember all the free space. Check your
> settings in postgresql.conf and increase if needed. (Note that an
> increase requires a postmaster restart to take effect.)

What is a reasonable value for these settings in my case? I write about 5
MBytes per hour (measured by the decrease of free disk space over some time)
to 157 LOBs (mostly by appending to existing LOBs), where the data remain
for about three months. This makes about 11 Gigabytes of needed Disk space.
But my disk went out of space at about 15 Gigabytes.

I have still no idea which value to put there; the (commented out) line in
the cfg file is

#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes

> You might also want to think about vacuuming more often than once a day,
> so that space can be recycled into the FSM sooner. When you have a
> periodic bulk delete process, it's not a bad idea to vacuum the table
> that had the deletes immediately after each deletion run.

My problem here is that vaccum takes always a few minutes (except in cases
when the database was not touched at all), and during that time the system
load is quite high. That disturbs the "normal" data storage processes on the
same server.

Is it possible to run the vacuum with a lower priority (while all other
postgres queries keeping the normal priority)? It does not help just to
decrease the priority of the vacuum command since it is the postmaster
thread that makes the load and not the command script).

Regards

Ole

--
NEU: WLAN-Router fr 0,- EUR* - auch fr DSL-Wechsler!
GMX DSL = supergnstig & kabellos http://www.gmx.net/de/go/dsl