What's the difference between partitions and many tables?
.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 30 mins ago
user2189731user2189731
61
migrated from stackoverflow.com 7 mins 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 30 mins ago
user2189731user2189731
61
migrated from stackoverflow.com 7 mins 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
20 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 30 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 30 mins ago
user2189731user2189731
61
asked 30 mins ago
user2189731user2189731
61
asked 30 mins ago
user2189731user2189731
61
asked 30 mins ago
user2189731user2189731
61
asked 30 mins ago
user2189731user2189731
61
61
migrated from stackoverflow.com 7 mins ago
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com 7 mins 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
20 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
20 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
20 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
20 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.
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
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.
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
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.
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
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 16 mins ago
Laurenz Albe
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
add a comment |
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
Thanks. I understand that core operation could be more efficient. But I do have scripts to messing around views. And I can get some benefit when query is about certain tables. I'll learn more about "you shouldn't have too many partitions, else query planning will become too slow". The old question again, "how many is too many"? Which I asked myself when I created those 10k tables...
– user2189731
3 mins ago
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
20 mins ago