Purging strategy

gatesucks_at_hotmail.com (Bob Smith) wrote in message news:<b87ff855.0205031032.4b7c1e6b_at_posting.google.com>...
> We are designing an application that needs to use
> a relational database to hold quite a large amount
> of data.
>
> In particular there is one table that has about 33
> fields, 18 indexes, and 120 bytes per record.
> Additionally, we are going to need to add about 2
> million records per day to the table, delete about 2
> million records per day from the table, hold 2 weeks
> worth of data within the database (approx. 30 million
> records), and sustain an average add rate of about 23
> records per second while, at the same time, sustaining
> an average delete rate of 23 records per second.
>
> My questions are, what database software should we use,
> what kind of hardware platform will be needed to
> support the specifications enumerated above, and what
> kind of average query performance can we expect?

Hi Bob,
I wonder how you guys are planning to purge the data. I think deleting a single record takes about rhe same amount of work as its inserting does.
My ideas are:
1. If you are deleting more than 10% of data at once, it's definitely faster to copy the data to a new tabl (NOT LOGGED INITIALLY) and drop the old one. Or at least to drop all the indexes before purging and recreate them after.
2, If you manage to keep 15 tables with same structure, you could just empty / drop then 1 every day. However, having to UNION ALL 15 tables is cumbersome.
3. If you manage to have a table across 15 partitionsand just load and empty file against 1 of them every day... 4. If you have a view hiding the records more than 2 weeks old, you don't have to purge every day.

And so on. Countless more combinations exist.

Hope it helps,
Alexander Received on Sun May 05 2002 - 04:38:22 CEST