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 Mon, Oct 4, 2021 at 6:05 PM Rob Sargent <robjsarg...@gmail.com> wrote: > On 10/4/21 3:37 PM, Israel Brewster wrote: > > On Oct 4, 2021, at 1:21 PM, Rob Sargent <robjsarg...@gmail.com> 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 than one station's data. > > > Ah, so in theory making “strict” tables for each would be easier than > creating partitions for each? Something to consider for sure if so. > > > In a write-once scenario such as this, would a "clustered index" on > datetime be stable, performant? Seems a read-for-export could put the head > down at time point A and just go? > > That’s beyond my level of DB admin knowledge, unfortunately :) I can > certainly read up on it and give it a try though! > > > I was hoping one of the smart people would chime in;) >
