Postgresql: large table or union of small tablesBest way of finding rows referencing a given id on...
Sing Baby Shark
Boss asked me to sign a resignation paper without a date on it along with my new contract
How can changes in personality/values of a person who turned into a vampire be explained?
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
How can I prevent an oracle who can see into the past from knowing everything that has happened?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
Was there a pre-determined arrangment for division of Germany in case it surrendered before any Soviet forces entered its territory?
Connecting to SMTP server from AWS Lambda
What can I do to encourage my players to use their consumables?
If I tried and failed to start my own business, how do I apply for a job without job experience?
Is practicing on a digital piano harmful to an experienced piano player?
Can I legally make a website about boycotting a certain company?
How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?
How to deal with an underperforming subordinate?
How to know if I am a 'Real Developer'
How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?
Why can all solutions to the simple harmonic motion equation be written in terms of sines and cosines?
Crack the bank account's password!
Coworker asking me to not bring cakes due to self control issue. What should I do?
Renting a 2CV in France
Tikz: Perpendicular FROM a line
Can I use a single resistor for multiple LED with different +ve sources?
How can I keep my gold safe from other PCs?
Is the percentage symbol a constant?
Postgresql: large table or union of small tables
Best way of finding rows referencing a given id on PostgreSQLBest way to handle multiple query keysHow to modelling a union type in PostgreSQLStoring table history - is this a good use case for Postgres's table partitioning / inheritance?Improve performance for order by with columns from many tablesWhat is the best way to create tables for email campaigns?Filtering UNION ALL result is much slower than filtering each subqueryAdd column with default in postgresql without table level lockHow can I combine a large CROSS JOIN query and return 1 result?PostgreSQL performance with (col = value or col is NULL)
I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.
From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?
postgresql postgresql-performance
asked Feb 13 '18 at 14:51
matanpermatanper
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.
From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?
postgresql postgresql-performance
asked Feb 13 '18 at 14:51
matanpermatanper
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24
add a comment |
I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.
From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?
postgresql postgresql-performance
asked Feb 13 '18 at 14:51
matanpermatanper
61
I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.
From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?
postgresql postgresql-performance
postgresql postgresql-performance
asked Feb 13 '18 at 14:51
matanpermatanper
61
asked Feb 13 '18 at 14:51
matanpermatanper
61
asked Feb 13 '18 at 14:51
matanpermatanper
61
asked Feb 13 '18 at 14:51
matanpermatanper
61
asked Feb 13 '18 at 14:51
matanpermatanper
61
61
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 14 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24
add a comment |
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24
add a comment |
1 Answer
1
active
oldest
votes
Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.
Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
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%2f197794%2fpostgresql-large-table-or-union-of-small-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.
Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
add a comment |
Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.
Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
add a comment |
Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.
Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
Not really clear from the question what you are trying to accomplish, but given that you tagged the question as postgresql-performance here are some general thoughts relevant to your question for you to consider.
Postgresql is a row database. Data is stored as rows. See this link on difference between row and column DBs.
In a row database you want to have data that needs to be retrieved and used at the same time, or most of the time. Unless your different versions of the JSON need to be read at the same time then you don't want it in the same row.
For keeping track of older / different versions of data you can either use different rows or a separate table. If older versions are not used often, or only for future reference and no current use, you may want to consider having a separate table so that data is not read/cached in memory. This is more relevant if you have a large table. If you have a small table, les than GBs worth of data, then you could use different rows and likely won't really matter much.
Check the rules for normalizing tables. For example here. Normalization is NOT always the answer to all designs, specially when you are doing analytics/OLAP, but it is good to at least be familiar with the concepts as many common design mistakes can be avoided by knowing about normalization.
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
answered Feb 15 '18 at 16:04
Francisco1844Francisco1844
1464
1464
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%2f197794%2fpostgresql-large-table-or-union-of-small-tables%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
It depends. On what elements do you query (only the primary key value or part of the JSON value), what are your filters? Also do you always want to fetch all versions at once or only one/some specific versions?
– Patrick Mevzek
Feb 13 '18 at 16:16
Most queries on the PK, but still some on the Json so I need index on the column. And most of the time I want to fetch only a specific version
– matanper
Feb 13 '18 at 16:56
Tables = entities. If you have new entities, you should be creating new tables!
– Vérace
Feb 13 '18 at 17:24