Holger Marzen wrote:
> On Sun, 19 Oct 2003, Greg Stark wrote:
>
>
>>Holger Marzen <holger@marzen.de> writes:
>>
>>
>>>I use PostgreSQL for counting network traffic, I use a sample every five
>>>minutes. Because my queries became too slow I simply added another table
>>>that holds the data per day. Every day, yesterday's data get added,
>>>inserted into the "day"-table and deleted from the 5-minutes-table. I
>>>don't need the 5 minutes accuracy for all of the data.
>>
>>The original poster said he needed the 5 minute data.
>
>
> Yes, but how long? Really for months? The above way of compressing data
> can be altered, e.g. he could keep the 5 minutes data for a week or a
> month and use the daily data for billing.
As was stated previously, the five-minute data is kept for 3 months to
examine traffic trends in more detail than can be gathered from weekly
or monthly data.
Each day, end-of-day totals for the five-minute data are calculated and
stored in a seperate database and kept for a minimum of 7 years.
The end-of-day data is used for billing. The five-minute data is used
when customers start to question the billing. We can look at the
five-minute data to see whether something unusual has happened and take
appropriate action.
I see nothing wrong with what I'm trying to achieve. Why should I
compromise the data that I store because I am having problems with the
speed of the database ?
The end-of-day data is held in a seperate database because the table
names are exactly the same as those in the five-minute database.
However the table formats are different (we don't have a time column).
Regards,
Nick.
--
Nick Burrett
Network Engineer, Designer Servers Ltd. http://www.dsvr.co.uk
