Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

There are many questions already on meta-stackoverflow regarding the database schema, but here is another, that I don't think is a duplicate, so please read on.

I read a description of the schema after I read this article on the High Scalability website. To quote the article, in the "Lessons learned" section:

Stack Overflow copied a key part of the Wikipedia database design. This turned out to be a mistake which will need massive and painful database refactoring to fix. The refactorings will be to avoid excessive joins in a lot of key queries.

As a newbie to database design, I thought it would be highly enlightening for somebody to expand on this statement a little, giving, for example, 1 or 2 examples of queries used by StackOverflow that suck when it came to excessive joins.

Also, I'm thought I read somewhere (can't remember exactly where, so maybe I didn't) that the database had recently undergone some refactoring which, I am assuming, was to address the issues regarding the joins. Is the, what seems to be widely published, schema (in the link above) a description of the database before or after refactoring?

I'm not looking for a complete description of all the details about this, but some examples, general insights or links to other material would be a very useful resource for those of us who are new to this.

share|improve this question
whoooooo! I see a tumbleweed rolling by here! 10 days, 91 rep points, 6 people marked this as a favorite and not 1 single answer! I challenge anyone to match this:) – Steg Oct 29 '09 at 22:00
I think that your comment ruined the Tumbleweed :( – Daniel Rodriguez Nov 19 '09 at 5:42

One point here is that it's not just joins that were a problem... it was excessive joins. It sounds like they had queries that needed to hit a lot of different tables, and they de-normalized the schema to avoid the need to touch of few of them.

I can only speculate, but an example might be that Wikipedia doesn't show authorship right away, but StackOverflow does. And so to display a question, stackoverflow has to join to the users table where Wikipedia might not.

Personally, I would have tried to handle the problem with an indexed view for that query instead, but then again maybe they tried that and it made too much difference on the update queries.

share|improve this answer

Another thing I noticed in mediawiki's scheme a few years back is that the contents of the post is separated from the metadata of the post. I remember seeing that it was to save space if two pages had the same content.

Obviously that doesn't happen much on any of these sites, so I'd speculate that could be a painful spot.

share|improve this answer

I also wonder why not considering other approaches. I once read a very interesting article about programming in LISP, entitled "Beating the Averages", by Paul Graham. It not only uses no regular scripting, but it also uses no database for building an online store! I'm not sure if it's still designed like that up to today, but viaweb is still around just it's been called Yahoo Store for over 10 years and it's definitely possible to build stuff with no database nowadays. I just don't know about large scales.

there's no database here

A database always seemed to me like a flawed concept of building something to manage data on top of something that's already build for that (a.k.a. the file system)! Sure you could come up with many different arguments, such as it's made for distributing data across systems, rather than managing how it's physically stored; it's software oriented rather than hardware; and so on. And I agree with it, but it still seems like there's a lot of room for improvement in that aspect, and so I was stunned by knowing that it was actually possible to build such a dynamic web site without a database. And I'd love to also see something like StackOverflow following that same path with success.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .