|
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 |
