Beginner's question to the student: Does the stop file stackoverflow-esque create as simple as 2 db tables?

Question

I have long been puzzled by the speed of stackoverflow and how quickly the questions / comments load on the page. It seems that the db backend that stores all this information will be humongus ... How can I ask a question and all the related answers to download so fast?

I have never worked in a large-scale db environment before (my background is a small business business such as Access, some MySQL) ... but I would suggest that the backend db for stackoverflow (simplified) is something like two tables, bound by an indexed key, right? Something similar to:

Question table: Question_PrimaryKey | QuestionText

Answer table: Answer_PrimaryKey | Question_ForeignKey | AnswerText

(linked in Question_PrimaryKey and Question_ForeignKey).

As I understand it, a site like stackoverflow is so structured? If so, how did the answers to these questions actually get so quickly and go to the browser? (this drives me crazy because when I create small intranet sites that use Access as a backend, performance really starts to deteriorate when db grows).

Any input is appreciated. Thank you for your time!

Answer 1

In an extremely simplified form, yes, the backend will be so, although the scheme will be more complex with more tables and relationships.

StackOverflow uses SQL Server 2008, which in many ways resembles access, at a whole new level in terms of complexity and performance. As far as databases are concerned, they don't get much worse than access (some of them are likely to correct me for this).

Performance is very good, but it will be the result of a large performance tuning, carefully optimized queries, indexes, schemas, partitions, etc. along with lots of caching.

Answer 2

A good website performance obviously depends on an orderly and well-tuned database, but this is largely due to caching - mainly storing frequently used data in memory, rather than retrieving from the database for each request.

This blog post talks about SO architecture.

Answer 3

Optimize Your Website With Jeff Atwood And Stackoverflow

This is the return of Jeff Atwood. Recently, he and the team have been doing a lot of quick optimizations for Stackoverflow. What tools do they use? What speed improvements do they see, and what can you do to use their experience?

And from the perspective of a hardware architect: High scalability - Archiving

Answer 4

This site actually provides a data dump. You can look to see how it folds.