Should I split timestamp parts into separate columns?Should we add extra 5 columns or build a separate table?Should I separate frequently updated columns?Is it worth to separate columns into multiple tables for one-to-one relational tableAre triggers bad for created/timestamp columns?Split two rows into two columnsShould I split this large table into three smaller tables?Should I move repeating foreign keys into separate table?Extracting 'hot columns' into a separate tableTransfer microsecond timestamp into table using COPYDatabase performance improvements for current setup. (mysql - marriaDB)
Why is only the fundamental frequency component said to give useful power?
Overlapping String-Blocks
Source that a married woman seduced by a “messianic figure” is still permitted to her husband
Are there any important biographies of nobodies?
How to construct an hbox with negative height?
Watts vs. volts amperes
Winning Strategy for the Magician and his Apprentice
Do simulator games use a realistic trajectory to get into orbit?
How is water heavier than petrol, even though its molecular weight is less than petrol?
An average heaven where everyone has sexless golden bodies and is bored
Thread Pool C++ Implementation
Is open-sourcing the code of a webapp not recommended?
Compiling c files on ubuntu and using the executable on Windows
Grover algorithm for a database search: where is the quantum advantage?
Passing multiple files through stdin (over ssh)
What is the `some` keyword in SwiftUI?
How can electric fields be used to detect cracks in metals?
SQL counting distinct over partition
Why did the Herschel Space Telescope need helium coolant?
Can I make plugins required?
Recommended tools for graphs and charts
Are there any instruments that don't produce overtones?
What is wrong with this proof that symmetric matrices commute?
Logarithm of exponential
Should I split timestamp parts into separate columns?
Should we add extra 5 columns or build a separate table?Should I separate frequently updated columns?Is it worth to separate columns into multiple tables for one-to-one relational tableAre triggers bad for created/timestamp columns?Split two rows into two columnsShould I split this large table into three smaller tables?Should I move repeating foreign keys into separate table?Extracting 'hot columns' into a separate tableTransfer microsecond timestamp into table using COPYDatabase performance improvements for current setup. (mysql - marriaDB)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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.
postgresql database-design query-performance optimization timestamp
edited May 21 at 20:07
MDCCL
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
add a comment |
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.
postgresql database-design query-performance optimization timestamp
edited May 21 at 20:07
MDCCL
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
@JonofAllTrades 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
– Khabz
May 22 at 9:41
add a comment |
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.
postgresql database-design query-performance optimization timestamp
edited May 21 at 20:07
MDCCL
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
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.
postgresql database-design query-performance optimization timestamp
postgresql database-design query-performance optimization timestamp
edited May 21 at 20:07
MDCCL
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
edited May 21 at 20:07
MDCCL
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
edited May 21 at 20:07
MDCCL
6,99831847
edited May 21 at 20:07
MDCCL
6,99831847
edited May 21 at 20:07
MDCCL
6,99831847
6,99831847
asked May 21 at 9:59
KhabzKhabz
1113
asked May 21 at 9:59
KhabzKhabz
1113
asked May 21 at 9:59
KhabzKhabz
1113
1113
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
@JonofAllTrades 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
– Khabz
May 22 at 9:41
add a comment |
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
@JonofAllTrades 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
– Khabz
May 22 at 9:41
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
@JonofAllTrades 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
– Khabz
May 22 at 9:41
@JonofAllTrades 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
– Khabz
May 22 at 9:41
add a comment |
2 Answers
2
active
oldest
votes
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.
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
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
add a comment |
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.
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238669%2fshould-i-split-timestamp-parts-into-separate-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
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
add a comment |
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.
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
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
add a comment |
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.
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
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.
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
answered May 21 at 11:03
Laurenz AlbeLaurenz Albe
71014
71014
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
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
add a comment |
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
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
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
In that case should I have a separate table or have the timestamp object inside each table?
– Khabz
May 21 at 12:20
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
Also, is it not ok to have some redundancy if that's gonna (positively) impact performance when querying the data?
– Khabz
May 21 at 12:30
2
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
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
add a comment |
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.
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
add a comment |
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.
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
add a comment |
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.
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
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.
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
answered May 21 at 15:24
mustacciomustaccio
10.7k72343
10.7k72343
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238669%2fshould-i-split-timestamp-parts-into-separate-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Add fields you need and update them in triggers.
– Akina
May 21 at 10:06
Is the timestamp updated when the record is updated? If not, the warehouse will need to use other methods to identify changed records, like checksums, and the timestamp may not be useful at all.
– Jon of All Trades
May 21 at 15:37
@JonofAllTrades 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
– Khabz
May 22 at 9:41