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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
