Question: Votes Table with Partitions, BRIN & B-Tree indexes

[Yoo]

Hi! For context, I have a votes table that is like a through model of Many-to-many (M2M). I’m using Postgres because BRIN and other stuff. Here’s the table:

class UpVote(models.Model):

    id = models.UUIDField(primary_key=True, default=uuid4, editable=False)

    post = models.ForeignKey(Post, on_delete=models.CASCADE, db_index = False)

    user = models.ForeignKey(User, on_delete=models.CASCADE)

    class Meta: 

        indexes = [BrinIndex(fields=(‘post’))]

I have this table for 2 reasons but they’re also questions:

1. When a user grabs a “post” instance, a query will go out to count. Por ejemplo, UpVote.objects.filter(post=self.request.GET.get(‘postID’)).count() But question: will this improve performance compared to a B-Tree index? Plus, there are many... MANY issues with counting in Postgres, but I only need an estimate. To me, BRIN makes sense, right?

2. Users are able to see the posts they’ve upvoted. The catch? I mentioned partitioning... I’m planning on horizontally partitioning the tables based on the BRIN Index on the “post” attribute. How will this affect the index on the “user” attribute? (Index on “user” attribute meant for filtering so user is able to see all posts previously upvoted). Note: I stumbled upon partitioning awhile ago and never bothered to learn much of it since Django docs never mentioned. Implicitly, I’ve done vertical, but horizontal seems confusing with the context/situation I have. Apparently, Architect is a package that can do partitioning, and so I’ve finally come full-circle.

Thanks for your help (or teachings if I totally got this wrong...)