Re: Partitioning Tables

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