Re: serious problems with vacuuming databases

> 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