2

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.

  • The records aren't supposed to be updated, each one maps to an occurrence in a webpage, therefore it makes sense to have static records and timestamps – GRoutar May 22 at 9:41
6

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.

  • In that case should I have a separate table or have the timestamp object inside each table? Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data? – GRoutar May 21 at 12:20
  • 2
    Normally you shouldn't store the same information redundantly. If you get a notable performance benefit, exceptions are ok. If that is the case for storing the same timestamps in several tables depends on your data model and your queries. But I am quite sure that you won't get a performance benefit from storing the several parts of a timestamp separately. – Laurenz Albe May 21 at 13:52
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.