5

Understanding the StackOverflow Database Schema

I would like to know more about how tags are stored in the database, and how each tag is retrieved in a search query.
I would also like to know if I can store many URLs in a one cell of a table as a string, in the same way you have stored tags in the above link.

3
  • Wow... there's a sqlserverpedia article for everything. Unfortunately, that one is really out of date. Try here: meta.stackexchange.com/questions/2677/… -- it's more up to date. – Jon Seigel Jul 27 '11 at 16:21
  • 1
    @Jon The data dump is not the same schema that stack exchange uses internally. – Pollyanna Jul 27 '11 at 17:09
  • @Adam I know that (first hand, actually). Part of my point was that the PostTags table is totally missing from that article, for example. Reviewing the question I linked to would help explain things a bit more than that article does. While the two schemas are different, the data dump mostly mirrors the real schema in this area. – Jon Seigel Jul 28 '11 at 4:03
3

As we don't have access to the actual database schema we can assume what is offered in the Stack Exchange Data Explorer is close enough to reason about.

This is the Entity Relationship Model for tags with the relevant attributes:

                  Data Examples                      
+-------------+
| Posts       |
| - Id        |   100017
| - Tags      |   <discussion><tags><database>
+-----+-------+
      |
      |*
+-----+-------+
| PostTags    |
| - PostId    |   10017       10017       10017
| - TagId     |   4           51          322 
+-----+-------+
      |*
      |
+-----+-------+
| Tags        |
| - Id        |   4            51          322
| - TagName   |   discussion   tags        database  
+-------------+

Between the Posts table and Tags table there is a relation over the join table PostTags. Do notice that the field tags in the Posts table also holds the tags that exist on the question. This is a de-normalized field where the tagnames that are linked to the question are enclosed in < > so they can be accessed without extra reads on joined tables.

In a search query on SEDE you could either do a posts.tags like '%<database>%' or a join over posts, posttags and tags with a where clause on tags.tagname = 'database'. The former leads to a full table scan on the posts table, which for larger databases like Stack Overflow might cause problems, the latter involves more reads over more tables.

If you're interested in the historical use of tags you can query the PostHistory table. Its records with posthistorytypeid in (3,6) hold the events when tags being added/edited, where the actual tags are stored denormalized in the text field, stored similar as is done in posts.tags.

Do know that in actual production there is something called the tag engine which is a proprietary "search" engine that is fed with info from above tables and then does the heavy lifting of searching in combination with Elastic Search.

You must log in to answer this question.

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