Re: reducing postgresql disk space

On 05/26/2010 07:50 AM, paladine wrote:
> It is default value ( #checkpoint_segments = 3    # in logfile segments, min
> 1, 16MB each )
> Many of my database configurations are default values. (plain TOAST  etc)
> my database is a log database so, some tables of db grow everytime.
> My ' /base ' directory contains a lot of compressed object (1GB size)
> These are maybe normal operations but I don't understand that
> although I delete many rows from my db and regularly vacuum , reindexing
> operations,
> how doesn't postgresql give back that deleted areas for reusing.
>
What is your PostgreSQL version? In older versions, you needed to set
the free space map high enough to manage the space that vacuum
identified as available. If that isn't high enough, vacuum won't be able
to fully do its job.

Also, if you are doing a typical form of logging where you delete
entries older than some set age, you should read up on table
partitioning. For example, if you keep log data for a year, set up an
empty parent table and create child tables spanning the appropriate
subset of the year (month, week, ...). After the child table is
no-longer needed it can be dropped or truncated depending on your
situation. Dropping or truncating is far faster than "delete
from...where..." and causes no table or index bloat.

Cheers,
Steve