I have a scaling issue we are running into. I have a social media app - the user feed has posts from them, posts from the groups they are part of and posts where their groups are tagged in the comments (even if the group is private to the user).
Now my current model is posts (post id, group id , user id) and groupusermap (groupid, userid) and tags(postid, groupid). Now for the user feed, i get the posts from posts where userid = user or groupid in (groupusermap where userid = user) union tags where groupid in (groupusermap where userid = user). This is not scaling well and it takes a lot of time.
I thought of doing userpostmapping(postid, userid) - but this can hit a billion rows or more easily due to the config we face. How can we scale this efficiently? We are using PGSQL.
explain(analyze, verbose, buffers, settings. All in plain text, as an update of your question. And what PostgreSQL version do you use?