Re: Growth planning

Possible - I’d have to dig into that with the script author. I was just handed an R script (I don’t work with R…) and told here’s the data it needs, here’s the output we need stored in the DB. I then spent just enough time with the script to figure out how to hook up the I/O. The schema is pretty much just a raw dump of the output - I haven’t really spent any resources figuring out what, exactly, the data is. Maybe I should :-)

Not sure. Here’s the output from pg_stat_all_indexes:

so they *have* been used (although not the station_data_idx so much), but this doesn’t tell me when it was last used, so some of those may be queries I was experimenting with to see what was fastest, but are no longer in use. Maybe I should keep an eye on this for a while, see which values are increasing.

While datetime partitioning seems to be the most common, I’m not clear on how that would help here, as the most intensive queries need *all* the datetimes for a given station, and even the smaller queries would be getting an arbitrary time range potentially spanning several, if not all, partitions. Now portioning on station seems to make sense - there are over 100 of those, and pretty much any query will only deal with a single station at a time. Perhaps if more partitioning would be better, portion by both station and channel? The queries that need to be fastest will only be looking at a single channel of a single station.

I’ll look into this a bit more, maybe try some experimenting while I still have *relatively* little data. My main hesitation here is that in the brief look I’ve given partitioning so far, it looks to be a royal pain to get set up. Any tips for making that easier?

Thanks for the suggestion!

---

Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320

Work: 907-474-5172
cell:  907-328-9145