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.
However, perhaps a combination could be a good compromise. We used to keep raw
one-record-per-hit data in a table and queried that for statistics. Later we
aggregated the data once per hour but kept the raw data as well. The reports
used the aggregate data for speed but the raw data was still available for
debugging or auditing.
This was very handy when the database became too large, we started purging the
raw data after 30 days but the reports were all still fine as we could keep
the aggregate data indefinitely.
--
greg
