Message-ID: <444D2066.6090200@karneval.cz>
Date: Mon, 24 Apr 2006 21:00:54 +0200
From: Tomas Vondra <vondra@karneval.cz>
User-Agent: Mail/News 1.5 (X11/20060131)
MIME-Version: 1.0
To: Ahmad Fajar <ahmadfajar@i2.co.id>
Cc: pgsql-performance@postgresql.org
Subject: Re: serious problems with vacuuming databases
References: <003101c65c6b$ad079850$7f00a8c0@kicommunication.com>
In-Reply-To: <003101c65c6b$ad079850$7f00a8c0@kicommunication.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

> Hi Tomas,
> 
> Tomas wrote:
> We've decided to remove unneeded 'old' data, which means removing about
> 99.999% of rows from tables A, C and D (about 2 GB of data). At the
> beginning, the B table (containing aggregated from A, C and D) was emptied
> (dropped and created) and filled in with current data. Then, before the
> deletion the data from tables A, C, D were backed up using another tables
> (say A_old, C_old, D_old) filled in using
> .....
> 1) drop, create and fill table B (aggregated data from A, C, D)
> 2) copy 'old' data from A, C and D to A_old, C_old a D_old
> 3) delete old data from A, C, D
> 4) dump data from A_old, C_old and D_old
> 5) truncate tables A, C, D
> 6) vacuum full analyze tables A, C, D, A_old, C_old and D_old
> ----
> 
> I think you do some difficult database maintainance. Why you do that, if you
> just want to have some small piece of datas from your tables. Why don't you
> try something like:
> 1. create table A with no index (don't fill data to this table), 
> 2. create table A_week_year inherit table A, with index you want, and some
> condition for insertion. (eg: table A1 you used for 1 week data of a year
> and so on..)
> 3. do this step for table B, C and D
> 4. if you have relation, make the relation to inherit table (optional).
> 
> I think you should read the postgresql help, for more information about
> table inheritance.
> 
> The impact is, you might have much table. But each table will only have
> small piece of datas, example: just for one week. And you don't have to do a
> difficult database maintainance like you have done. You just need to create
> tables for every week of data, do vacuum/analyze and regular backup.
> 
> 
> Best regards,
> ahmad fajar,

Thanks for your advice, but I've read the sections about inheritance and
I don't see a way how to use that in my case, as I think the inheritance
takes care about the structure, not about the data.

But I've read a section about partitioning (using inheritance) too, and
it seems useful. I'll try to solve the performance issues using this.

Thanks for your advices
Tomas