Re: VACUUM process running for a long time

2010/4/14 John R Pierce <pierce(at)hogranch(dot)com>:
> Jan Krcmar wrote:
>>
>> hi
>>
>> i've got the database (about 300G) and it's still growing.
>>
>> i am inserting new data (about 2G/day) into the database (there is
>> only one table there) and i'm also deleting about 2G/day (data older
>> than month).
>>
>> the documentation says, one should run VACUUM if there are many
>> changes in the database, but the vacuumdb never finishes sooner than
>> the new data should be imported.
>>
>> is there any technique that can solve this problem?
>>
>
> your table is currently in a messy state, as its apparently not been
> vacuumed (what version of postgres is this, anything since 8.1 should have
i'm using postgresql-server-8.4.2-1PGDG.rhel5
autovacuum is running, but used space is always rising

> autovacuum running by default).    in theory your table has about 60GB of
> data in it, the fact that its 300GB indicates there's a lot of 'dead'
> tuples.
the database was dumper&recreated&restored about 2 weeks ago (this
removes allocated "empty" space, isn't it?). dump had about 250G

i agree that there should be some 'dead' tuples, but how should i
unallocate them?

> You might consider partitioning this table by date, either by day or by
> week, and instead of deleting old rows, drop entire old partitions
this is not really good workaround...

>
>
>
> --
> 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
>