How experts program their databases?

Question

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.

Answer 1

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.