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 10/4/21 3:37 PM, Israel Brewster 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;)