8

This question already has an answer here:

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


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

marked as duplicate by Paul White Mar 7 '16 at 6:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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