On Mon, Dec 19, 2005 at 11:10:50AM -0800, James Klo wrote:
> Yes, I've considered partitioning as a long term change. I was thinking
> about this for other reasons - mainly performance. If I go the
> partitioning route, would I need to even perform archival?
No. The idea is that you have your table split up into date ranges
(perhaps each week gets it's own table). IE: table_2005w01,
table_2005w02, etc. You can do this with either inheritence or
individual tables and a UNION ALL view. In your case, inheritence is
probably the better way to go.
Now, if you have everything broken down by weeks and you typically only
need to access 7 days worth of data, then generally you will only be
reading from two tables, so those two tables should stay in memory, and
indexes on them will be smaller. If desired, you can also play tricks on
the older tables surch as vacuum full or cluster to further reduce space
usage and improve performance.
> The larger problem that I need to solve is really twofold:
>
> 1. Need to keep reads on timeblocks that are from the current day
> through the following seven days very fast, especially current day reads.
>
> 2. Need to be able to maintain the timeblocks for reporting purposes,
> for at least a year (potentially more). This could probably better
> handled performing aggregate analysis, but this isn't on my current radar.
I've written an RRD-like implementation in SQL that might interest you;
it's at http://rrs.decibel.org (though the svn web access appears to be
down right now...)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461