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