How do you design a website that allows users to query a large amount of user data, more specifically:

  • there are ~100 million users with ~100TB of data, data is stored in HDFS (not a database)
  • number of (concurrent) queries is not important, but each query should be as fast as possible
  • support some simple queries such as: get user info by id, get accumulated data like monthly logins and monthly online time
  • query result is little (1 number, or a few hundred rows) so frontend performance doesn't matter

I'm more interested in the thought process on how to approach this requirement. For example:

  • at 100 users, what is the design?
  • at 1,000,000 users, what needs to be changed?
  • at 100,000,000 users, what is the design now?

I've searched around and see a lot of people talking about caching, load balancing,... Of course, those techniques are useful and can be used but how do you know it can help handling N users? Nobody seems to explain this point.

  • You haven’t told us much about the results of the queries, which is what matters in the context of web sites. For example, if you run a search over the 100 million users and return a single value in 100ms, then all of the engineering effort is going to be on the back end and doesn’t really have anything to do with web sites. However if you need to display a table with a million rows, that’s a different problem. So you need to be more specific. Commented Feb 19, 2019 at 2:26
  • You're are right, that is an important part. I've updated the question.
    – Minh Thai
    Commented Feb 19, 2019 at 4:15
  • 1
    Thanks. Now I want to ask you a question— forget the web site part. How would you make this work if the queries were submitted at the command line? Commented Feb 19, 2019 at 4:17
  • I would setup something that allows SQL queries and can connect with HDFS, for example Spark. I will add more servers until it cannot go faster. Then to save time on the same query later, I cache the result.
    – Minh Thai
    Commented Feb 19, 2019 at 5:35
  • 1
    great. Now you know what you need to build: an interface for defining queries that can be submitted to Spark. Commented Feb 20, 2019 at 1:26

2 Answers 2


It's fairly basic math.

The bottleneck is unlikely your database, but bandwidth.

Take your max bandwidth, divide by expected # of users, and subtract 15% for overhead.

If you really have unlimited bandwidth, then do the same calculation using your database throughput.


At this time in cloud tech, I would employ what others have already designed to handle data loads. Though you have a bit of data, I would put these data and future records into something akin to Google's BigQuery:

  • Easy to query via SQL,
  • Pay by query,
  • Handles many, many pebibytes,
  • Easily embedded into web/mobile app,
  • Maintains cache already,

By design, there is some non-cached query inertia time, but I would run away fast from trying to design, scale, script, pay-for, and maintain all the above.

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.