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

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

When should the static/dynamic statistics stored in tables? How can static/dynamic statistics be stored efficiently in partial indexes of PostgreSQL?

  • 1
    I am not exactly sure what you are asking, but I updated my answer you referenced. – Erwin Brandstetter Oct 1 '15 at 13:34
  • @ErwinBrandstetter Outstanding addition in your old answer! Now, it is much clearer and concrete. – Léo Léopold Hertz 준영 Oct 1 '15 at 16:52
  • @ErwinBrandstetter It would be nice to get some summary of all those techniques what you have described in your answers here. Orthogonality, playing tetris with columns, ... You can assume to have billion units of data. – Léo Léopold Hertz 준영 Oct 1 '15 at 16:59