From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Partitioning Tables
Date: Fri, 5 Feb 2021 11:42:26 -0700
Message-ID: <B77077E0-3B97-4408-9BFA-D4F5137C2689@elevated-dev.com> (raw)
In-Reply-To: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
References: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, lance@illinois.edu, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning Tables
In-Reply-To: <B77077E0-3B97-4408-9BFA-D4F5137C2689@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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