Re: Growth planning

On 10/4/21 11:09 AM, Israel Brewster wrote:
>> On Oct 4, 2021, at 8:46 AM, Rob Sargent <robjsargent@gmail.com 
>> <mailto:robjsargent@gmail.com>> wrote:
>>
>>> On Oct 4, 2021, at 10:22 AM, Israel Brewster <ijbrewster@alaska.edu 
>>> <mailto:ijbrewster@alaska.edu>> wrote:
>> Guessing the “sd” is "standard deviation”?  Any chance those stddevs 
>> are easily calculable from base data?  Could cut your table size in 
>> half (and put those 20 cores to work on the reporting).
>
> 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 :-)
>
>>  And I wonder if the last three indices are strictly necessary? They 
>> take disc space too.
>
> Not sure. Here’s the output from pg_stat_all_indexes:
>
> volcano_seismology=# select * from pg_stat_all_indexes where 
> relname='data';
>  relid | indexrelid | schemaname | relname |   indexrelname        | 
> idx_scan | idx_tup_read | idx_tup_fetch
> -------+------------+------------+---------+---------------------------+----------+--------------+---------------
>  19847 |      19869 | public     | data    | data_pkey           |    
>     0 |            0 |             0
>  19847 |      19873 | public     | data    | 
> date_station_channel_idx  |   811884 |  12031143199 |   1192412952
>  19847 |      19875 | public     | data    | station_channel_epoch_idx 
> |        8 |       318506 |   318044
>  19847 |      19876 | public     | data    | station_data_idx        
>   |     9072 |         9734 |   1235
>  19847 |      19877 | public     | data    | station_date_idx        
>   |   721616 |  10927533403 |   10908912092
>  19847 |      20479 | public     | data    | 
> data_station_channel_idx  |    47293 | 194422257262 |   6338753379
> (6 rows)
>
> 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.
>
>>
>> But my bet is you’re headed for partitioning on datetime or perhaps 
>> station.
>
> 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?
>
>
If no queries address multiple stations you could do a table per 
station.  Doesn't smell good but you have a lot of data and well, speed 
kills.

I think the date-station-channel could "take over" for the 
station-date.  Naturally the latter is chosen if you give just the two 
fields, but I would be curious to see how well the former performs given 
just its first two fields(when station-date doesn't exist).