Etydhv

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.

At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.

id | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 | 4 | 13 | 15 | 0 | 19


vs


id
-------------------------
Fri Apr 13 2018 15:00:19

My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:

SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/

I would appreciate some best practices input on this.

Keep the timestamp and don't add columns for the parts.

If you need to search for part of a timestamp, you can always create indexes on extract expressions.

Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.

You seem to be engaging in premature optimization -- you should not assume performance characteristics of any particular design, but test them.

When you store components of a timestamp value in separate columns you may not gain noticeable performance benefits, but you will increase the risk of inconsistent data or the maintenance overhead (or both).

Having said that, there may be valid reasons to store some components of the timestamp as separate columns, for example:

• Components, such as year, quarter, month constitute valid dimensions in your data warehouse model.


• Your database physical design calls for data partitioning by time intervals to facilitate maintenance or improve performance of some operations.