Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\))
Subject: Re: Partitioning Tables
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
Date: Fri, 5 Feb 2021 11:42:26 -0700
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <B77077E0-3B97-4408-9BFA-D4F5137C2689@elevated-dev.com>
References: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
To: "Campbell, Lance" <lance@illinois.edu>
Precedence: bulk

> On Feb 5, 2021, at 11:27 AM, Campbell, Lance <lance@illinois.edu> =
wrote:
>=20
> 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?=20
> =20
> Select group_member.* from group, group_member WHERE =
group.type=3D=E2=80=99A=E2=80=99 and group.id=3Dgroup_member.user=3D=E2=80=
=99bob smith=E2=80=99;

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:
> =20
> Select group_member.* from group_member where group_member.user=3D=E2=80=
=99bob smith=E2=80=99;

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 =3D '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