Re: Growth planning

Just a quick update to this topic from my testing: I whipped up a quick python script to create the partition tables for me, which went smoothly enough, and created a table LIST partitioned on station. Once populated with my current data, this table proved marginally faster than the unpartitione

Re: Growth planning

As for clustering, unfortunately, it's a one-time operation in Postgres (as far as I'm aware), so you'd have to "cluster" the index every time after an insert or update of data. If it is partitioned, I presume it can be run on the index of each partition table individually - but I'm not sure. On M

Re: Growth planning

On 10/4/21 3:37 PM, Israel Brewster wrote: On Oct 4, 2021, at 1:21 PM, Rob Sargent > wrote: My "strict" table per station suggestion was meant as an option to avoid the partitioning pain point entirely if it wasn't going to buy you anything. Namely querying more t

Re: Growth planning

> On Oct 4, 2021, at 9:22 AM, Rob Sargent wrote: > > 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 s

Re: Growth planning

> On Oct 4, 2021, at 1:21 PM, Rob Sargent wrote: > > On 10/4/21 3:09 PM, Israel Brewster wrote: >>> On Oct 4, 2021, at 12:46 PM, Ron >> > wrote: >>> >>> On 10/4/21 12:36 PM, Israel Brewster wrote: >>> [snip] Indeed. Table per station as opposed to partitionin

Re: Growth planning

On 10/4/21 3:09 PM, Israel Brewster wrote: On Oct 4, 2021, at 12:46 PM, Ron > wrote: On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.

Re: Growth planning

> On Oct 4, 2021, at 12:46 PM, Ron wrote: > > On 10/4/21 12:36 PM, Israel Brewster wrote: > [snip] >> Indeed. Table per station as opposed to partitioning? The *most* I can >> reasonably envision needing is to query two stations, i.e. I could see >> potentially wanting to compare station a to s

Re: Growth planning

On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.e. I could see potentially wanting to compare station a to some “baseline” station b. In general, though, the station

Re: Growth planning

> On 4 Oct 2021, at 18:22, Israel Brewster wrote: (…) > the script owner is taking about wanting to process and pull in “all the > historical data we have access to”, which would go back several years, not to > mention the probable desire to keep things running into the foreseeable > future

Re: Growth planning

> On Oct 4, 2021, at 9:22 AM, Rob Sargent wrote: > > On 10/4/21 11:09 AM, Israel Brewster wrote: >>> On Oct 4, 2021, at 8:46 AM, Rob Sargent >> > wrote: >>> On Oct 4, 2021, at 10:22 AM, Israel Brewster >>> > wrote: >>> Guessing the

Re: Growth planning

On 10/4/21 11:09 AM, Israel Brewster wrote: On Oct 4, 2021, at 8:46 AM, Rob Sargent > wrote: On Oct 4, 2021, at 10:22 AM, Israel Brewster > wrote: Guessing the “sd” is "standard deviation”?  Any chance those stddevs are easily calculab

Re: Growth planning

> On Oct 4, 2021, at 8:46 AM, Rob Sargent wrote: > >> On Oct 4, 2021, at 10:22 AM, Israel Brewster > > 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

Re: Growth planning

> On Oct 4, 2021, at 10:22 AM, Israel Brewster wrote: > > > - and the data table definition: > > Column | Type | Collation | Nullable | > Default > +--+---+--+--