What's the difference between partitions and many tables? The 2019 Stack Overflow Developer...
How to change the limits of integration
Is "plugging out" electronic devices an American expression?
Why do UK politicians seemingly ignore opinion polls on Brexit?
Limit the amount of RAM Mathematica may access?
Why don't Unix/Linux systems traverse through directories until they find the required version of a linked library?
Is flight data recorder erased after every flight?
What is the motivation for a law requiring 2 parties to consent for recording a conversation
Could a US political party gain complete control over the government by removing checks & balances?
How to reverse every other sublist of a list?
Should I use my personal or workplace e-mail when registering to external websites for work purpose?
Why isn't airport relocation done gradually?
Where to refill my bottle in India?
What does "rabbited" mean/imply in this sentence?
Does duplicating a spell with Wish count as casting that spell?
What tool would a Roman-age civilization have to grind silver and other metals into dust?
Why is my p-value correlated to difference between means in two sample tests?
In microwave frequencies, do you use a circulator when you need a (near) perfect diode?
What can other administrators access on my machine?
Manuscript was "unsubmitted" because the manuscript was deposited in Arxiv Preprints
Does light intensity oscillate really fast since it is a wave?
Lethal sonic weapons
Could JWST stay at L2 "forever"?
How to make payment on the internet without leaving a money trail?
What is the steepest angle that a canal can be traversable without locks?
What's the difference between partitions and many tables?
The 2019 Stack Overflow Developer Survey Results Are In
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?
I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?
postgresql
asked 24 mins ago
user2189731user2189731
61
migrated from stackoverflow.com 1 min ago
This question came from our site for professional and enthusiast programmers.
add a comment |
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?
I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?
postgresql
asked 24 mins ago
user2189731user2189731
61
migrated from stackoverflow.com 1 min ago
This question came from our site for professional and enthusiast programmers.
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago
add a comment |
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?
I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?
postgresql
asked 24 mins ago
user2189731user2189731
61
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:
"Partitioning refers to splitting what is logically one large table
into smaller physical pieces"
And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?
I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?
postgresql
postgresql
asked 24 mins ago
user2189731user2189731
61
asked 24 mins ago
user2189731user2189731
61
asked 24 mins ago
user2189731user2189731
61
asked 24 mins ago
user2189731user2189731
61
asked 24 mins ago
user2189731user2189731
61
61
migrated from stackoverflow.com 1 min ago
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com 1 min ago
This question came from our site for professional and enthusiast programmers.
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago
add a comment |
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago
add a comment |
1 Answer
1
active
oldest
votes
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.
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%2f234387%2fwhats-the-difference-between-partitions-and-many-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
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.
add a comment |
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.
add a comment |
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.
The key difference between having many smaller tables and having one partitioned table is that the partitioned table can appear as a single table in SQL statements. You don't have to mess around with views and triggers (the latter are probably also less efficient), it is all handled in core.
However, you shouldn't have too many partitions, else query planning will become too slow, and you will suffer from all the other problems that probably made you abandon a design with tens of thousands of tables.
Maybe you can use partitioning to your advantage if you manage to split your table into fewer partitions.
Partitioning is a comparatively new feature in PostgreSQL, and v11 has many notable improvements in this area. So if you want partitioning, use v11 by all means.
answered 11 mins ago
Laurenz Albe
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%2f234387%2fwhats-the-difference-between-partitions-and-many-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
Thanks. That one additional information "partitioning is index plus more" helped. I can arrange my data/table for partitioning. The question is, since partitioned tables are stored separately, why can't we just use multiple tables? Is there any specific benefit using 1 logical table? And since PGSQL10 has limitation on partitioning while I'm using 10, should I use partitioning in PgSQL10 or should I upgrade to PgSQL11 and use it?
– user2189731
15 mins ago