Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently designing a project and I need professional advice from DBA.

My project will feature voting system somewhat similar to the one used in stack exchage websites. I have users and content pieces and users can vote for content pieces they like or don't. Note, that I will have vote up/down option on the feed list, so if I load 30 content pieces I will need to also load the data of user's votes on every piece, since the up/down button should be highlighed if user already has voted for a specific piece. In other words, I expect big load on the votes table. I was thinking of the basic structure like this:

Table users (user_id, ...), table content (content_id, ...), table votes (vote_id, user_id, content_id, datetime, vote). However, I have doubts about this design.

Let's say I have 10k users and 1k content pieces. That's up to 10 million records in table votes. If I start thinking about scaling it up, I can imagine a big problem. Content doesn't go anywhere, old votes as well, so the longer website runs, the more records will be in the table and the slower it will work.

Let's say in some years I will have 100k users and 20k content pieces. That's up to 2 billion records. I understand that not every user will vote on every content piece, but the problem however is clear - there is a limit to that design (by limit I mean that select query will be slow when the amount of rows will reach some point).

So my questions are:

  1. Is there really a limit to that design and if yes, how can it be dealt with? 1.1. If the select query on votes table will be getting slower, what can I do to speed it up?
  2. Is there a better way to design this kind of relations?
  3. How do I cache that data? Or is that even needed with proper indexing?
  4. What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id, content_id)?
  5. Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

I really would like to do things right from the start so in a few years I won't end up with a slow website. Thank you for your time.

share|improve this question
    
"by limit I mean that select query will be slow when the amount of rows will reach some point" - Not true in the slightest. A properly indexed database can support billions of rows. –  Phil Feb 15 '13 at 17:39
    
even reddit disables voting after a while. after x months you could close voting and aggregate the votes. –  Neil McGuigan Feb 17 '13 at 22:45

4 Answers 4

up vote 2 down vote accepted
+50

Is there really a limit to that design and if yes, how can it be dealt with? 1.1. If the select query on votes table will be getting slower, what can I do to speed it up?

I don't think the number of votes is likely to be the problem. The questions will have to do in part with questions of how well you can index, how your db does caching, etc. Standard performance tuning applies and that isn't really your design per se. I will answer more below on what to consider if you run into the wall of being unable to get your design to work fast enough.

Is there a better way to design this kind of relations?

Not really.

How do I cache that data? Or is that even needed with proper indexing?

My preference in this case would be to start out without caching, and then to implement a caching layer when you need one. A caching layer might include something like memcached, or you could build one on a NoSQL solution like Mongo. At that point you can look at optimizing the areas which are the largest problems.

What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id, content_id)?

I know that MySQL and PostgreSQL are different enough to make cross-db somewhat dangerous here but I am thinking you'd want two indexes, one on content_id and one on user_id. I am thinking this because aggregating by user_id and content_id are likely to be different queries and these are different join conditions.

Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

Keep in mind that db's frequently do a good job of caching recent content pieces. I would expect that MySQL can do this too. If it can't go with PostgreSQL instead. Don't cache it yourself in the db.

what to do if you hit the wall will depend on your DB choice. If you are using MySQL, your traditional answer is to look at something like memcached or create a caching layer in a NoSQL db. If you are using PostgreSQL, you get those choices plus something like Postgres-XC which gives you an ability to do teradata-style scaling out and clustering in OLTP environments.

share|improve this answer
  1. Using a table with a ton of rows is not a problem (if properly indexed, as Phil pointed out). Managing it (backup, restore, altering) a single table can be rough.
  2. You could shard the data into multiple tables. You should cache the total number of up/down votes in the 'content' table itself.
  3. A specialized RAM cache will typically beat a db query. As you note, you don't have to cache the counts for every piece of content, just the ones that will be displayed (recent, popular, etc). For users, load the list of their X most recent votes into the cache when they login - one query will be much better than X small ones.
  4. You'll need (user_id, content_id) and (content_id, user_id). If you put the 'vote' column in the index, then the database won't have to read the underlying record from the database to get that value. To do the 'most recent' queries (from #3 and #5), you'll want the date. Use a column of type 'date' (not 'datetime') in your index - it's less that half the size and will be faster.
  5. See #3.
share|improve this answer

I would consider logging your votes to flat files, and using a script to summarize them and store them in application-ready tables. This way, you are storing summarized data that is quickly accessible and you have access to your raw logs if you ever need to summarize your data in a new way that you didn't think about before.

For caching, storing the number of votes for each piece of content would be cheap and would limit the number of connections you need to the database, so I would highly recommend that. Even if you expire the cache every couple minutes, you would only need to make one call to the database every couple minutes instead of potentially thousands.

share|improve this answer

When in doubt about scalability, it's usually useful to break data down so that there is as little contention as possible. So, I'd start by asking questions like:

  • How many users will be active at the same time? How many items (content pieces) will be votable? I'm talking about orders of magnitude. 100,000? 10,000,000? If it's less than 1,000,000 you almost certainly don't need any complex schema or caching, a default sane configuration of MySQL and reasonable indexes will do just fine and you don't need to worry about scalability (on modern hardware), so better focus on other problems, like usability. Even if you approach the performance limits, buying a SSD, more RAM or a faster CPU is generally cheaper than paying someone to design a more complex architecture.
  • How important is it to record which user voted on which item? If "not at all", then you could get away with having a single counter per item, possibly with storing "votes" on the client side (html5 storage, etc).
  • Will there be multiple web/app servers accessing the same database? How important is to always have an exact count of votes in this scenario? If "not much", each web/app server could have a local memcached instance holding votes, and a periodic cron job which synchronizes them into the database.
  • Similarly, you could make the votes table in your database memory-backed ("memory" storage engine), and a cron job which will periodically transfer the data to a persistent table.
  • Similarly, you could have the "hot data" i.e. the current votes table be memory-backed and then only transfer the summary data (i.e. the total vote counts for your data items) to a persistent table (e.g. the main items table).
  • Similarly, you could move all of your "hot data", e.g. the most active items and their votes to memory tables, keep them there a few days and then move them back to persistent storage (or just periodically sync them).
  • If you're really, really concerned that the votes table will be a major performance bottleneck (it's probably not, don't do this unless you know what you're doing) and you need exact counts, always, you could create a specialized server (daemon) in a fast language like C++ which have specialized data structures (trees, hash tables) to track votes in.

There are a lot of different strategies you could use (these are not the only ones). I'd say that if you get pass the first bullet on my list, bullet #4 would give you the most benefits with the least effort.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.