From: Tomas Vondra <vondra@karneval.cz>
To: Ahmad Fajar <ahmadfajar@i2.co.id>
Cc: pgsql-performance@postgresql.org
Subject: Re: serious problems with vacuuming databases
Date: Mon, 24 Apr 2006 21:00:54 +0200
Message-ID: <444D2066.6090200@karneval.cz> (raw)
In-Reply-To: <003101c65c6b$ad079850$7f00a8c0@kicommunication.com>
References: <003101c65c6b$ad079850$7f00a8c0@kicommunication.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vondra@karneval.cz, ahmadfajar@i2.co.id
Subject: Re: serious problems with vacuuming databases
In-Reply-To: <444D2066.6090200@karneval.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox