This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing. If stored, I think it is well done with partial indexes (B-tree) because only a subset of data is required in my application described here. Assume you have three orthonormal tables: measurements, events and headers where all have some static (size of events in bytes, etc) columns but wonder if the storage of some numbers from data analysis is beneficial or not.
Example of my table schema measurements without names:
SERIAL | NOT NULL
INTEGER | NOT NULL
INTEGER | NOT NULL
TIMESTAMP WITH TIME ZONE | DEFAULT CURRENT_TIMESTAMP
where I am worried about increasing the schema size by INTEGER fields because of my need in statistics. I do not want to store in the main tables variables which are used rarely. I do not want cause my indexes not being used as described in the book PostgreSQL Up & Running and this article Why is my index not being indexed?
Possible sources
- Gordon Linoff and his blog here
When should the static/dynamic statistics stored in tables? How can static/dynamic statistics be stored efficiently in partial indexes of PostgreSQL?
