2010/4/14 John R Pierce <pierce@xxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
