I designed a social-media application (such as Facebook) where there is this strong concept of audience-based content system.

For example, let's say this user A can create posts (public, private or groups only), based on that configuration, the post will only be available to that audience.


What I did, is just to build relationships users-posts, and then we can look up those relationships and see if that content is or not available. But, this worked good for small subsets of users, because within time, groups became bigger (500 users or more), and then you want to query posts availables to a subset of groups, and doing these joins takes forever. And yeah, we have the database properly indexed, but it seems like we have major problem here, a design problem maybe?.


We thought that a SQL database would be the best fit at the beginning, some people talked about Neo4js but honestly, at the time it seemed too complicated for the MVP, so we discard that, I’m thinking now that a Graph DB may solve our problem, or maybe not, that’s why I need someone who have experienced a similar situation like this one.

THE Problem

As I mentioned before, this solution worked just fine, but eventually, the community is growing so fast, now users have multiple groups, and to calculate audience for a given post it's kinda heavy task, since posts can have multiple audience:

  • Private
  • Public (current user's audience)
  • Groups only
  • Friend's audience

And, for a given user, what's visible for him?

So, what’s the best approach to make this system fully-scalable for a high-traffic system?

  • whats the actual query which is slow?
    – Ewan
    Commented Mar 16, 2018 at 15:16
  • Thanks @Ewan, I've updated the question by describing the problem, or part of it. Commented Mar 16, 2018 at 18:39

2 Answers 2


You may find this to be a useful resource:

Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems


It discusses at a high-level how twitter dealt with a similar problem.

It is a good read. In the mean time, I hope the below can help you on your journey:

I see this is tagged with OOP. This is more of a database technology/design question. A well-tuned database RDBMS can probably get you far, but you'd want to consider a blend of batch-processing, and real-time db updates. These will require trade offs that are unique to your application: Can a friends post be delayed by seconds, minutes or hours? keep the data users find of high value data flowing quickly, and slow down the lower value data.

What I did, is just to build relationships users-posts, and then we can look up those relationships and see if that content is or not available.

This is needs to evolve as your user-base & usage grows.

Some things to consider:

You have de-normalized your Post-users relationship. this is bound to cause scale problems. Even users that signup, but do not view content are costing you space, perf and $$. as user relationships between groups, & friends change, the need to modify data in thins table could get tricky.

Consider storing the data in a way that represents the way that you described your domain model:

  • Public-Posts (post-id - post-date)
  • User-posts (user-id post-id, post-date)
  • Friends-posts (user-id post-id, post-date)
  • Group-posts (group-id post-id, post-date)

This way a user's relationship to friends, and groups will impact what they see. (which is probably what users expect). Also, consider what a Public-Posts table would mean in reducing the data, and need for writes and writes to the database.

Make caching a 1st class concern.

Have you exhausted caching opportunities on the server, on the client and across application tiers. If you have, exhaust them even further. One efficient way to reduce the DB-as-bottleneck problem is to lay off the DB as much as possible.

  • Can you cache recent "public" posts in memory?
  • Can you cache recent & popular "groups" posts in memory?
  • Can you identify common groups that have high read rates?

If your system can take data from a blend of cached data and SQL results, you'll be in much better shape. Even better if you are blending that data with cached data in the browser.

Consider ways that your system can gather cached data quickly, and augment, client side with slower db queried. ex: - Load common posts (synchronous operation) from cache. - Start to collect personalized posts (groups/freinds), - renders the UI - UI receives the personalized posts and updates the UI. - UI may cache personalized posts for future requests.

It can help increase the feeling of a fast load time, and reduce the need to retrieve the dame data from the database across requests,

Another suggestion: be sure to know the usage patterns of your users. Some of these suggestions will not make sense when you consider how often posts are published.

Consider storing what read and write operations you need to perform, to look for common patterns.

Review how Friendships are being formed

  • If most people have 10 friends, and you design and test for that, but outliers have 100,000 bad things could happen.

  • It can also impact how you cache, and rules that keeps outiers from messing up the cache for everyone else. Or, maybe catering to those outliers

Lastly, i would recommend gathering real-world performance data.

  • Benchmark
  • Improve
  • Benchmark
  • Improve

When a large system that has specific performance needs, only make changes that you can prove have demonstrable impact. It will make your code more complicated. Ensure you are introducing complexity (and cost of maintenance) for benefits for your users. Throwing complexity at a problem that may be helping users is a sure way to make a system you'd rather not maintain.


The problem is inherently unscalable unless you restrict the number of recipent groups.

If I have only 10 users there are up to 3628800 different possible recipient groups.

Each post will be in a single recipient group

Each user will be a member of multiple recipient groups.

Over time users will friend and de-friend each other creating more and more recipient groups even if the user base stays static.

To see their feed the user must query all the recipient groups of which they are a member. Which is effectively unlimited.

Your only hope is to limit the total number of groups, but that requires the cooperation of all the users. It just doesn't work with the concept of 'friending'

For example. Say I can be an max of 2 groups (which i already am) and a new user friends me. This would create a new group of all their friends + me. Unless all their friends are already my friend I can't be this persons friend as it would put me in 3 groups.

You could allow new 'friends' to see all a users old posts, essentially limiting each user to one recipient group. But I can still friend everyone and have to check all the recipient groups for new posts

You could pre-define the groups, say each city gets a group. you can post to a city, but everyone who joins the city will be able to see all posts ever posted to that city group.

You can offline the process of delivering posts to users (like email), instead of querying their groups a user queries their feed and a backend process loops through new posts, gets the recipient groups and copies the post to each feed. This speeds the users page load, but is still unscalable, as it delays the delivery of messages. Plus stores multiple copies of the same post.

Essentially Facebook is email, where everyone has an email server, and you run all the servers

  • 2
    Well, there must be some way to do it, because Facebook makes it work. 500 users doesn't seem like a lot of data to me, and your math seems suspicious. I suspect that Facebook already limits results to, say, two degrees. (think six degrees to Kevin Bacon). Commented Mar 16, 2018 at 17:39
  • well exactly, fb doesnt make it work, thats why they have 'top posts' and you dont see half the things you pages you like post. plus throwing money at servers != scaling
    – Ewan
    Commented Mar 16, 2018 at 18:47
  • 1
    Alright, I'll give you that one. Frankly, I have no idea how my own personal Facebook page works (its behavior appears random to me), and without deterministic behavior I have little incentive to use it regularly. Commented Mar 16, 2018 at 18:49
  • I just get kitten videos these days
    – Ewan
    Commented Mar 16, 2018 at 18:59
  • @RobertHarvey Part of the reason it works is people aren't going to (or even going to want to) explore the vast majority of the space of possibilities. Ewan's argument is a bit like saying we need to limit user names to 6 characters because if we allowed, say, 10 characters simply storing 50^10 (or whatever) user names would require enormous amounts of disk space. (God forfend we allowed Unicode characters!) Commented Mar 17, 2018 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.