Partitioning Tables

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