pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning tables...
14+ messages / 10 participants
[nested] [flat]
* Partitioning tables...
@ 2003-07-31 19:22 Joe Maldonado <jmaldonado@webehosting.biz>
0 siblings, 1 reply; 14+ messages in thread
From: Joe Maldonado @ 2003-07-31 19:22 UTC (permalink / raw)
To: pgsql-admin
Hello all,
I currently have a project which will store roughly 10B records in
postgres which will be inserted at a rate of about 40K/s. While we have
adequate disk storage it is paramount that subsets of the data be
queryable quickly, without hindering the insert process to a crawl.
I know in Oracle you can partition the tables into logical subsets and
was wondering if this was also possible in postgres.
Thanks in advance,
-Joe
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning tables...
@ 2003-08-01 08:43 Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
parent: Joe Maldonado <jmaldonado@webehosting.biz>
0 siblings, 0 replies; 14+ messages in thread
From: Sam Barnett-Cormack @ 2003-08-01 08:43 UTC (permalink / raw)
To: Joe Maldonado <jmaldonado@webehosting.biz>; +Cc: pgsql-admin
On Thu, 31 Jul 2003, Joe Maldonado wrote:
> I know in Oracle you can partition the tables into logical subsets and
> was wondering if this was also possible in postgres.
I *think* that a partial index is probably your best bet.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
^ permalink raw reply [nested|flat] 14+ messages in thread
* Partitioning tables
@ 2007-10-09 14:02 Enrico <scotty@linuxtime.it>
0 siblings, 1 reply; 14+ messages in thread
From: Enrico @ 2007-10-09 14:02 UTC (permalink / raw)
To: pgsql-admin
Hi all,
Is it possible to partition a large table in differtent disks?
For example
names from A to K in one disk and from L to Z in another.
Regards
Enrico
--
Enrico Pirozzi
Web: http://www.enricopirozzi.info
E-Mail: info@enricopirozzi.info
Skype: sscotty71
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning tables
@ 2007-10-09 14:35 Scott Marlowe <scott.marlowe@gmail.com>
parent: Enrico <scotty@linuxtime.it>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2007-10-09 14:35 UTC (permalink / raw)
To: info@enricopirozzi.info; +Cc: pgsql-admin
On 10/9/07, Enrico <scotty@linuxtime.it> wrote:
> Hi all,
> Is it possible to partition a large table in differtent disks?
>
> For example
>
> names from A to K in one disk and from L to Z in another.
Yep.
Just create file systems belonging to the postgres super user, create
tablespaces on those file systems, and then create your inherited
tables on those tablespaces as appropriate.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Partitioning tables
@ 2018-07-06 18:05 Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Campbell, Lance @ 2018-07-06 18:05 UTC (permalink / raw)
To: pgsql-admin
Assume you are reading and writing regularly to a given table.
1) When using Amazon RDS PostgreSQL 10, is there any reason to use table partitioning?
2) If so what might be a few simple examples of when to use it?
3) Also, if there is a reason then how large should a table be before it should be partitioned? I am just wanting some general idea or scope.
It is always a good idea to occasionally revaluate if going down a particular path still makes sense.
Thanks,
Lance
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning tables
@ 2018-07-06 21:39 Jernigan, Kevin <kmj@amazon.com>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 14+ messages in thread
From: Jernigan, Kevin @ 2018-07-06 21:39 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; pgsql-admin
Assume you are reading and writing regularly to a given table.
1) When using Amazon RDS PostgreSQL 10, is there any reason to use table partitioning?
2) If so what might be a few simple examples of when to use it?
3) Also, if there is a reason then how large should a table be before it should be partitioned? I am just wanting some general idea or scope.
It is always a good idea to occasionally revaluate if going down a particular path still makes sense.
Thanks,
Lance
RDS for PostgreSQL 10, and prior versions of RDS for PostgreSQL, run on EBS storage. One key benefit of table partitioning is partition elimination for queries that would otherwise scan the entire table, and this benefit applies to RDS for PostgreSQL. More generally, the benefits described in the postgresql.org doc here - https://www.postgresql.org/docs/10/static/ddl-partitioning.html - are applicable for RDS for PostgreSQL, except for the last one listed – Seldom-used data can be migrated to cheaper and slower storage media.
Kevin Jernigan
Senior Product Manager
Amazon Aurora PostgreSQL
1-415-710-8828 (m)
kmj@amazon.com<mailto:kmj@amazon.com>
27 Melcher Street
Boston, MA 02210
^ permalink raw reply [nested|flat] 14+ messages in thread
* Partitioning Tables
@ 2021-02-05 18:27 Campbell, Lance <lance@illinois.edu>
0 siblings, 2 replies; 14+ messages in thread
From: Campbell, Lance @ 2021-02-05 18:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
PostgreSQL 12
I have a challenge. I have two tables, group and group_member. A group table has a type indicator telling me which of three ways the table can be used. The group member table is made up of 50 million records that have a foreign key to the group table.
I really need the queries to be fast for one particular type of group. This type has less than a million members in it. So my first thought was to create a separate group_member table just for members of this type of group. But I have to change a lot of SQL.
The other idea I thought of is there a way to use table partitions? If the query goes against a group of type A then it would pull from the small partition but if it is a group of some other type it would query against the other partition.
So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other. However, how would PostgreSQL know on insert a group_member into the proper partition?
Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
Also, what happens if I did a query like this. Will it know to scan both group_member_a and group_member_other:
Select group_member.* from group_member where group_member.user=’bob smith’;
Thoughts?
Thanks,
Lance Campbell
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 18:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 2 replies; 14+ messages in thread
From: Scott Ribe @ 2021-02-05 18:42 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> wrote:
>
> So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other. However, how would PostgreSQL know on insert a group_member into the proper partition?
>
> Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
When you define the partition, you declare what values go into it. With PG 12, you can insert into the parent and the row will get redirected into the right place. Some older versions (I don't remember how old) required you to insert into the right table, or some voodoo with rewrite rules or triggers.
In other words, smarts about which partitions to scan in your example pre-dates smarts about inserting.
I don't remember for sure, but I think maybe PG 13 adds the feature where if you update the group type, the row gets moved. Prior, you'd have to delete and re-insert. Someone correct me?
> Also, what happens if I did a query like this. Will it know to scan both group_member_a and group_member_other:
>
> Select group_member.* from group_member where group_member.user=’bob smith’;
Yes.
Anyway, you are on the right track, in that what you're describing could work.
There are less invasive things you could try, which wouldn't give as much benefit--like partial indexes
for instance
create index ... on group_member(user) where type = 'A'
that gets you fast location of a single user, but if you looked for a range of users, reading the index would be fast, but you'd still wind up with the matching rows scattered all over the single big tablee
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 18:59 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 14+ messages in thread
From: Ron @ 2021-02-05 18:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 2/5/21 12:42 PM, Scott Ribe wrote:
>> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> wrote:
>>
>> So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other. However, how would PostgreSQL know on insert a group_member into the proper partition?
>>
>> Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
> When you define the partition, you declare what values go into it. With PG 12, you can insert into the parent and the row will get redirected into the right place. Some older versions (I don't remember how old) required you to insert into the right table, or some voodoo with rewrite rules or triggers.
>
> In other words, smarts about which partitions to scan in your example pre-dates smarts about inserting.
>
> I don't remember for sure, but I think maybe PG 13 adds the feature where if you update the group type, the row gets moved. Prior, you'd have to delete and re-insert. Someone correct me?
I don't know about v13, but v12 (and below) error on updating the partition
key. (That's a common limitation which helps the optimizer /know/ what's in
a partition.)
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 19:18 Campbell, Lance <lance@illinois.edu>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 2 replies; 14+ messages in thread
From: Campbell, Lance @ 2021-02-05 19:18 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
This is what I really want to do. But it does not look like PostgreSQL supports referencing the parent tables for deciding what partition to put the data in:
CREATE TABLE group_manager.group (id INTEGER, type TEXT, name TEXT, CONSTRAINT group_pkey PRIMARY KEY (id) );
-- type would have values of A , B or C
CREATE TABLE group_member (fk_group_id INTEGER, id INTEGER, user_id TEXT) PARTITION BY LIST(group.type);
-- Above PARTITION BY LIST(group.type) does not work.
CREATE TABLE group_member_A PARTITION OF group_member FOR VALUES IN ('A');
CREATE TABLE group_member_B PARTITION OF group_member FOR VALUES IN ('B');
CREATE TABLE group_member_C PARTITION OF group_member FOR VALUES IN ('C');
It does look like it would work if I added the "type" from the group table to the group_member table. But otherwise the PARITION BY LIST does not support referencing a parent table.
Lance
On 2/5/21, 12:42 PM, "Scott Ribe" <scott_ribe@elevated-dev.com> wrote:
> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> wrote:
>
> So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other. However, how would PostgreSQL know on insert a group_member into the proper partition?
>
> Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
When you define the partition, you declare what values go into it. With PG 12, you can insert into the parent and the row will get redirected into the right place. Some older versions (I don't remember how old) required you to insert into the right table, or some voodoo with rewrite rules or triggers.
In other words, smarts about which partitions to scan in your example pre-dates smarts about inserting.
I don't remember for sure, but I think maybe PG 13 adds the feature where if you update the group type, the row gets moved. Prior, you'd have to delete and re-insert. Someone correct me?
> Also, what happens if I did a query like this. Will it know to scan both group_member_a and group_member_other:
>
> Select group_member.* from group_member where group_member.user=’bob smith’;
Yes.
Anyway, you are on the right track, in that what you're describing could work.
There are less invasive things you could try, which wouldn't give as much benefit--like partial indexes
for instance
create index ... on group_member(user) where type = 'A'
that gets you fast location of a single user, but if you looked for a range of users, reading the index would be fast, but you'd still wind up with the matching rows scattered all over the single big tablee
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 19:19 Campbell, Lance <lance@illinois.edu>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 1 reply; 14+ messages in thread
From: Campbell, Lance @ 2021-02-05 19:19 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I had a typo:
This is what I really want to do. But it does not look like PostgreSQL supports referencing the parent tables for deciding what partition to put the data in:
CREATE TABLE group (id INTEGER, type TEXT, name TEXT, CONSTRAINT group_pkey PRIMARY KEY (id) );
-- type would have values of A , B or C
CREATE TABLE group_member (fk_group_id INTEGER, id INTEGER, user_id TEXT) PARTITION BY LIST(group.type);
-- Above PARTITION BY LIST(group.type) does not work.
CREATE TABLE group_member_A PARTITION OF group_member FOR VALUES IN ('A');
CREATE TABLE group_member_B PARTITION OF group_member FOR VALUES IN ('B');
CREATE TABLE group_member_C PARTITION OF group_member FOR VALUES IN ('C');
It does look like it would work if I added the "type" from the group table to the group_member table. But otherwise the PARITION BY LIST does not support referencing a parent table.
Lance
On 2/5/21, 1:18 PM, "Campbell, Lance" <lance@illinois.edu> wrote:
This is what I really want to do. But it does not look like PostgreSQL supports referencing the parent tables for deciding what partition to put the data in:
CREATE TABLE group_manager.group (id INTEGER, type TEXT, name TEXT, CONSTRAINT group_pkey PRIMARY KEY (id) );
-- type would have values of A , B or C
CREATE TABLE group_member (fk_group_id INTEGER, id INTEGER, user_id TEXT) PARTITION BY LIST(group.type);
-- Above PARTITION BY LIST(group.type) does not work.
CREATE TABLE group_member_A PARTITION OF group_member FOR VALUES IN ('A');
CREATE TABLE group_member_B PARTITION OF group_member FOR VALUES IN ('B');
CREATE TABLE group_member_C PARTITION OF group_member FOR VALUES IN ('C');
It does look like it would work if I added the "type" from the group table to the group_member table. But otherwise the PARITION BY LIST does not support referencing a parent table.
Lance
On 2/5/21, 12:42 PM, "Scott Ribe" <scott_ribe@elevated-dev.com> wrote:
> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> wrote:
>
> So if I do a query like the below it would ideally pull from partition group_member_a rather than group_member_other. However, how would PostgreSQL know on insert a group_member into the proper partition?
>
> Select group_member.* from group, group_member WHERE group.type=’A’ and group.id=group_member.user=’bob smith’;
When you define the partition, you declare what values go into it. With PG 12, you can insert into the parent and the row will get redirected into the right place. Some older versions (I don't remember how old) required you to insert into the right table, or some voodoo with rewrite rules or triggers.
In other words, smarts about which partitions to scan in your example pre-dates smarts about inserting.
I don't remember for sure, but I think maybe PG 13 adds the feature where if you update the group type, the row gets moved. Prior, you'd have to delete and re-insert. Someone correct me?
> Also, what happens if I did a query like this. Will it know to scan both group_member_a and group_member_other:
>
> Select group_member.* from group_member where group_member.user=’bob smith’;
Yes.
Anyway, you are on the right track, in that what you're describing could work.
There are less invasive things you could try, which wouldn't give as much benefit--like partial indexes
for instance
create index ... on group_member(user) where type = 'A'
that gets you fast location of a single user, but if you looked for a range of users, reading the index would be fast, but you'd still wind up with the matching rows scattered all over the single big tablee
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 19:25 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Ribe @ 2021-02-05 19:25 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Feb 5, 2021, at 12:19 PM, Campbell, Lance <lance@illinois.edu> wrote:
>
> But it does not look like PostgreSQL supports referencing the parent tables for deciding what partition to put the data in
correct, I missed that in your first post
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-05 19:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2021-02-05 19:26 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Friday, February 5, 2021, Campbell, Lance <lance@illinois.edu> wrote:
>
>
> It does look like it would work if I added the "type" from the group table
> to the group_member table. But otherwise the PARITION BY LIST does not
> support referencing a parent table.
>
Replace “parent table” with “some random table only related to the table
in question by a foreign key” and it probably will make more sense.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Partitioning Tables
@ 2021-02-06 04:28 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2021-02-06 04:28 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, 2021-02-05 at 18:27 +0000, Campbell, Lance wrote:
> I have a challenge. I have two tables, group and group_member. A group table has a
> type indicator telling me which of three ways the table can be used. The group member
> table is made up of 50 million records that have a foreign key to the group table.
>
> I really need the queries to be fast for one particular type of group. This type has
> less than a million members in it. So my first thought was to create a separate
> group_member table just for members of this type of group. But I have to change a lot of SQL.
>
> The other idea I thought of is there a way to use table partitions? If the query goes
> against a group of type A then it would pull from the small partition but if it is a group
> of some other type it would query against the other partition.
If you want to partition "group_member", the partitioning key cannot be in "group".
So you would have to (redundantly) add "group.type" to "group_member".
You could ensure consistency by including that column in the foreign key.
If you want partition pruning to take effect, you will have to include
a condition on "group_member.type" in the WHERE condition.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-02-06 04:28 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-31 19:22 Partitioning tables... Joe Maldonado <jmaldonado@webehosting.biz>
2003-08-01 08:43 ` Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2007-10-09 14:02 Partitioning tables Enrico <scotty@linuxtime.it>
2007-10-09 14:35 ` Re: Partitioning tables Scott Marlowe <scott.marlowe@gmail.com>
2018-07-06 18:05 Partitioning tables Campbell, Lance <lance@illinois.edu>
2018-07-06 21:39 ` Re: Partitioning tables Jernigan, Kevin <kmj@amazon.com>
2021-02-05 18:27 Partitioning Tables Campbell, Lance <lance@illinois.edu>
2021-02-05 18:42 ` Re: Partitioning Tables Scott Ribe <scott_ribe@elevated-dev.com>
2021-02-05 18:59 ` Re: Partitioning Tables Ron <ronljohnsonjr@gmail.com>
2021-02-05 19:18 ` Re: Partitioning Tables Campbell, Lance <lance@illinois.edu>
2021-02-05 19:19 ` Re: Partitioning Tables Campbell, Lance <lance@illinois.edu>
2021-02-05 19:25 ` Re: Partitioning Tables Scott Ribe <scott_ribe@elevated-dev.com>
2021-02-05 19:26 ` Re: Partitioning Tables David G. Johnston <david.g.johnston@gmail.com>
2021-02-06 04:28 ` Re: Partitioning Tables Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox