4

I read the Database schema documentation for the public data dump and I'm curious to know how you at StackExchange keep the Posts.Score column synced with the Votes table (as well as some other columns e.g. Users.UpVotes)

I remember reading long time ago that you're using Redis for saving the votes counts, but I can't find the answer now and it didn't have all the information anyway.

Improve this question
4
  • 1
    The data dump is a "point in time" dump of data - there is no synchronization needed there. It is also not an identical schema to the one that Stack Overflow runs with.
    – Oded
    Feb 23, 2016 at 13:44
  • @Oded, ohh, so what is the reason you added the column in the data dump? You don't have something similar in the real scheme? I ask because I build something with up and down votes, and when I checked how you pros are doing it, I saw it wasn't normalized...
    – gdoron
    Feb 23, 2016 at 13:47
  • Ease of use - that's the kind of information that users of the public data need, so it is there. In the actual schema we do have a "Score" column in the Posts table as well as a Votes table (we don't have Users.UpVotes, for example). We update both at the same time and have a daily job to synchronize the two when they go out of sync (say due to race conditions). As a piece of data that is always seen with a post, denormalizing like this is a perf gain, at the cost of possibly having conflicting data.
    – Oded
    Feb 23, 2016 at 13:51
  • 1
    Related: meta.stackexchange.com/questions/275875/…
    – Oded
    Feb 23, 2016 at 13:53

1 Answer 1

Reset to default
4

The "schema" that is in the data dump is derived from the SEDE database schema (the data in SEDE is imported once a week from the live DB - it is cleaned up to remove non-public identifying information and doesn't change till the next import). This schema is not identical to the schema used in the live production databases.

Having said that, the live database schema does have a few denormalized fields - this is done for performance where needed (we start normalized and when we identify a bottleneck that denormalization would help with, we consider that option and sometimes implement it - Score on the Posts table is one example).

The normalized data is the source of truth and we have daily jobs that fix up discrepancies that can occur on the denormalized data (Tags on the Posts table are one other example).

We do use redis as (mostly) a caching layer - but that isn't our single source of truth, just a cache.

Improve this answer

You must log in to answer this question.

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