2

Let's say I create my own website and design a discussion forum in that. Now, I have multiple types of users in my system, and millions of accounts per type of user. Since the website is very popular, 100s of new threads are created every second. People post on these threads (lets limit them to only text for now) rapidly so each thread gets 100+ posts every minute.

So, If I create a single table called thread, and add a new record everytime a new thread is created, and associate it with a posts table such that all posts belonging to that thread use it's ID as foreign key, I believe the database will bend down on it's knees in a matter of weeks.

Someone I asked told me to create a data warehouse and put old threads there and lock them. But what if I don't want to? Am I going wrong conceptually? I would like to know what experts say.

P.S. If you are going to delve into technical details, I am familiar with Ruby on Rails and Postgresql.

Improve this question
9
  • 4
    Between launching your website and getting to the situation described, you'll have plenty of time to figure it out and do some tests. Look at the database schema for some existing forum softwares for some ideas in the meantime.
    – whatsisname
    May 18, 2016 at 23:03
  • 5
    No startup has ever written the first version of their software with this kind of scalability in mind.
    – Robert Harvey
    May 18, 2016 at 23:15
  • 6
    100s of new threads per second means that after 3 hours, there are on the order of 1 million threads. Every one of those threads gets 100+ replies per minute, which means 100 million replies per minute, or in other words, every single one of your users posts a new message every second, without ever taking a break, eating, drinking, peeing, sleeping, or going on vacation. After 1 year, every person on the planet is posting one message per second. Are you sure that's a realistic model?
    – Jörg W Mittag
    May 18, 2016 at 23:34
  • 1
    Architecting Massively-Scalable Near-Real-Time Risk Analysis Solutions
    – Robert Harvey
    May 18, 2016 at 23:44
  • 1
    With that kind of load, you'll need to be more concerned with how to manage your data center infrastructure, either onsite or in the cloud, than with which language or DBMS you are using. You might find additional guidance on Serverfault.SE.
    – Pedro
    May 18, 2016 at 23:46

1 Answer 1

Reset to default
4

So, If I create a single table called thread, and add a new record everytime a new thread is created, and associate it with a posts table such that all posts belonging to that thread use it's ID as foreign key, I believe the database will bend down on it's knees in a matter of weeks.

I fail to see the database problem here.

100s of new threads are created every second ... each thread gets 100+ posts every minute.

Here's your problem. Your database will be fine. Your network, however, is experiencing a DDoS attack. Sure in a few days your storage will be at capacity but on day one people at your work site will be complaining that they can't watch kitten videos on their lunch breaks.

You need to limit access to your limited resources. Don't give users a blank check to post how ever much they feel like posting. The whole reason you force them to create accounts is so you can track what they're costing you. Don't let them cost you more than they give you.

As for scalable database design, keep it simple, decoupled, and replaceable. Some day it may need some fancy redesign. Until then just keep it simple so it doesn't take weeks for the redesign team to understand it. Decoupled so the rest of your code doesn't die with it. Replaceable so when the next big DB thing happens you can try it out without much pain.

Improve this answer

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.