Return to Question

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?

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?

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?

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?

This answer motivated think again my following tables but I am still uncertain how to store statistic data for quick selection. I think it is well done with partial indexes because only a subset of data is required in my application described here. I have three tables: measurements, events and headers where all have some static (never changes) columns about sizes in bytes:

• originalMeasurementSizeInBytes in measurements - static, size of original .raw -file
• measurementHeaderSizeInBytes in headers - static, never changes

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 (just this intuition). 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?

For instance, removal of one event would decrease the measurement size which includes events and header.

Possible sources

• Gordon Linoff and his blog here

How can static/dynamic statistics be stored efficiently in partial indexes of PostgreSQL?

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?