From: | paladine <yasinmalli(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: reducing postgresql disk space |
Date: | 2010-05-26 16:13:31 |
Message-ID: | 28682916.post@talk.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
My PostgreSQL version is 8.1.11.
My log entries are on filesytem and I write these logs to db from unix
stream.
Another interesting situation is that while my logs on filesystem contains
about 5-6 GB,
db directory (/base/OID) contains 40 GB. Is that normal ?
I implement your suggestions by using shell scripts ( when the disk filled
up I delete it from db etc. )
Steve Crawford wrote:
>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context: http://old.nabble.com/reducing-postgresql-disk-space-tp28681415p28682916.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2010-05-26 18:41:16 | Does update = delete + insert ? |
Previous Message | Steve Crawford | 2010-05-26 15:48:21 | Re: reducing postgresql disk space |