Question :
I’m developing a database for time series analysis of price bars for stocks. The associated metrics (“technical indicators”) will derive on as many as 10 successive layers of dependent set operations. Using Postgres 10.3.
There are two ways to derive a layer-1 metric “metric1” that becomes an input to most of the other metrics. The only difference between the datasets after the 1st layer of derivations is the metric1 value. The two datasets use the same set of table specs.
I want to to see how the two different derivations of metric1 impact the higher-level metrics, with the implementation goal of doing so with minimal coding and operational overhead.
My impression is that putting the datasets into separate Postgres schemas could meet the implementation goal. However, all the example Postgres schema use cases I’ve googled relate to segregating data that remains separate, for privacy, or to manage different groups or data releases. I have not found examples where the intent is to compare derivations from one schema to those from another schema.
So two related questions: is there anything about a two-schema approach that would be problematic for this use case, and are there are other approaches that would be preferable ?
Answer :
The development process has falsified the premiss of the question, i.e. each of the 2 derivations of metric1 have become inputs to derivations at several metric levels.
If this had not been the case, I’m now inclined to implement the original scenario via alternate views that duplicate query logic while being based on one or the other derivation of metric1. This seems from my pov a simpler approach, in that it avoids an additional schema and instead adopts a view-naming convention where view names denote the particular metric1 derivation.