Re: reducing postgresql disk space

Firstly, thanks for your explanations...

> Are you attempting a one-time space reduction or are you having general
> bloat issues?

Unfortunately, I have growing bloat issues so I want to reduce space as it
filled up.
Thus I wrote a script but as I said before it doesn't reclaim disk space.

> make sure you have upgraded and that autovacuum is enabled and correctly
> tuned

In my pg configuration, ' autovacuum = off '
but I run a script ( daily cronjob ) that controls the number of table row
and
if it expires a determined limit, run ' delete command ' and then run '
vacuum analyse verbose '
In your opinion, Is autovacuuming more efficient way ?

Steve Crawford wrote:
>
> On 05/26/2010 07:16 AM, paladine wrote:
>> Hi all,
>>
>> How can I reduce disk space postgresql used ?
>> I tried to delete many rows from my database and
>> I am running ' vacuum analyze reindexdb ' commands regularly
>> but my disk space on my linux machine didn't reduce.
>>
>> I know that ' vacuum full ' command can do that but I don't want to use
>> that command because of the disadvantages.
>>
>> Anyone know another method ?
>>
> Are you attempting a one-time space reduction or are you having general
> bloat issues?
>
> It is important to understand what is happening behind the scenes. Due
> to MVCC (multi-version concurrency control), when you update a record,
> PostgreSQL keeps the old one available until the transaction commits.
> When no transaction needs the old record, it is not physically removed
> but it is marked as dead. The basic vacuum process does not free
> disk-space but rather identifies space within the files that hold the
> table that has become available for reuse.
>
> In a modern version of PostgreSQL with autovacuum running and set
> appropriately for your workload, bloat should stay reasonably under
> control (i.e. make sure you have upgraded and that autovacuum is enabled
> and correctly tuned). But there are some things that can cause excess
> table bloat like updates that hit all rows (this will roughly double the
> size of a clean table) or deletes of substantial portions of a table.
> Vacuum will allow this space to be reclaimed eventually, but you may
> want to reduce disk-space sooner.
>
> Your options:
>
> Dump/restore. Not useful on a live, running database but can be useful
> when you have yourself wedged in a corner on a machine out-of-space as
> you can dump to another machine then do a clean restore back to your
> server. Depending on your situation (especially foreign-key
> constraints), you *may* be able to dump/restore just a specific
> offending table.
>
> Vacuum full. Reclaims the space, but is typically sloooow and requires
> an exclusive table lock. IIRC, should be followed by a reindex of the
> table. But vacuum-full runs "in-place" so it can be of use when you have
> little free-space remaining on your device.
>
> Cluster. Reclaims free-space and reindexes. Also reorders the table-data
> to match the specified index which is often useful. Cluster must be run
> on a table-by-table basis. Cluster also requires an exclusive lock but
> is *way* faster than vacuum-full. Cluster requires enough free-space to
> fully create the new clean copy of the table. This means a table can
> require as much as double it's original space for clustering though a
> heavily bloated table may require far less.
>
> Both cluster and vacuum full are safe. If you are in a tight place, you
> can carefully choose the method to use on a table-by-table basis:
> vacuum-full if your hand is forced and cluster when you have made enough
> free-space available.
>
> Once things are cleaned up, examine how they got bad to begin with so
> you aren't bitten again.
>
> 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-tp28681415p28690348.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.