Reddit’s database has two tables

Steve Huffman talks about Reddit’s approach to data storage in a High Scalability post from 2010. I was surprised to learn that they only have two tables in their database.

Lesson: Don’t worry about the schema.

[Reddit] used to spend a lot of time worrying about the database, keeping everthing nice and normalized. You shouldn’t have to worry about the database. Schema updates are very slow when you get bigger. Adding a column to 10 million rows takes locks and doesn’t work. They used replication for backup and for scaling. Schema updates and maintaining replication is a pain. They would have to restart replication and could go a day without backups. Deployments are a pain because you have to orchestrate how new software and new database upgrades happen together.

Instead, they keep a Thing Table and a Data Table. Everything in Reddit is a Thing: users, links, comments, subreddits, awards, etc. Things keep common attribute like up/down votes, a type, and creation date. The Data table has three columns: thing id, key, value. There’s a row for every attribute. There’s a row for title, url, author, spam votes, etc. When they add new features they didn’t have to worry about the database anymore. They didn’t have to add new tables for new things or worry about upgrades. Easier for development, deployment, maintenance.

The price is you can’t use cool relational features. There are no joins in the database and you must manually enforce consistency. No joins means it’s really easy to distribute data to different machines. You don’t have to worry about foreign keys are doing joins or how to split the data up. Worked out really well. Worries of using a relational database are a thing of the past.

This fits with a piece I read the other day about how MongoDB has high adoption for small projects because it lets you just start storing things, without worrying about what the schema or indexes need to be. Reddit’s approach lets them easily add more data to existing objects, without the pain of schema updates or database pivots. Of course, your mileage is going to vary, and you should think closely about your data model and what relationships you need.

Update, 10:05AM PDT: It’s worth reading the comments from a current Reddit engineer on this post. Particularly this one:

I’m personally not a fan of using an RDBMS as a key-value store – but take a look at, say, line 60 of the accounts code. Each item in that _defaults dictionary corresponds to an attribute on an account. For pretty much all of those (1) we don’t need to join on it and (2) we don’t want to do database maintenance just to add a new preference toggle. Those points are particularly more important when you’ve got a staff of 2-3 engineers. Sure, reddit has more now – but we’ve also now got a lot of data to migrate if we wanted to change, a lot of code to rewrite, and a lot of more important problems.

The data architecture made sense for Reddit as a small company that had to optimize for engineering man hours. Now they are much bigger and can afford a saner structure. He/she mentions that they are in the process of migrating their Postgres data over to Cassandra, but slowly.

Update, 11:31PM PDT: A former engineer at reddit adds this comment.

There isn’t a “table” for a subreddit. There is a thing/data pair that stores metadata about a subreddit, and there is a thing/data pair for storing links. One of the properties of a link is the subreddit that it is in. Same with the comments. There is one thing/data pair for comments and the subreddit it is in is a property.

Still today I tell people that even if you want to do key/value, postgres is faster than any NoSQL product currently available for doing key/value.

Update, 7:11PM PDT: From Hacker News, it looks like they use two tables for each “thing”, so a thing/data pair for accounts, a thing/data pair for links, etc.

36 thoughts on “Reddit’s database has two tables

  1. Darren Douglas

    That’s a good approach, and one that’s similar (although more extreme) to the wordpress approach. A posts table and a post_meta table.

    Every plugin I’ve used that tries to add its own tables causes me issues when I want to use it with other plugins….

    Reply
  2. Toby DiPasquale

    I also find it very strange that people keep re-inventing ISAM in these large web services but no one ever seems to give that concept credit. FriendFeed, Reddit, Google App Engine’s Datastore… does IBM have some kind of lockdown on that term or do they all just think they were the first to think of it?

    Reply
  3. Jonathan deWerd

    @Toby You could “go deeper” and say that ISAM re-invents the concept of a memory address, which goes back to the dawn of computing. In this form, the database is essentially a blob of binary data with some convenience functions on top (replication / backup / serialization / virtual-memory like aliasing). If computing had a proverbial wheel to re-invent, this would be it. I think it’s ok to not use IBM’s term for this, especially if they’ve patented it or their lawyers think they were the first to think of it :)

    Reply
  4. SD

    That is stupid, Use a key value object store, there are hundreds pick any.

    Lets have all the management and development overhead of a RDBMS and use none of the benefits.

    Reply
  5. Noah Yetter

    What a load of crap from total amateurs.

    Adding a column to a 10 million row table takes ZERO SECONDS in Oracle or PostgreSQL. Friends don’t let friends use MySQL!

    Data in this idiotic format has absolutely no structure, no integrity. Maybe that’s fine if you run a glorified forum but if you actually transact business the relational model gives you a lot and asks little in return. What’s that phrase about re-inventing wheels? Don’t build joins and transactions in your application when an RDBMS can do them for you better, faster, correctly. Don’t build an unstructured mess that can’t be reported on or analyzed, and requires custom code to do even the tiniest data migration.

    Reply
      1. Adam

        Indeed. Or take a minute to add it with no default, then run an update to put the default value in all rows, then save the table again with the default value in. It won’t bother locking as there’s nothing to update now.

        Reply
    1. Greg Jorgensen

      My thoughts exactly, thank you. This article describes both MySQL-induced ignorance of RDBMSs and ignorance of the benefits of ACID. The programmers have moved all of the problems of data integrity and management into the application layer, throwing away all of the benefits of an RDBMS without even knowing why that’s a terrible idea. If your car doesn’t run you don’t conclude that cars suck and ride a Big Wheel to work — you get a car that works or learn to fix the one you have.

      Reply
      1. Robert Young

        – The programmers have moved all of the problems of data integrity and management into the application layer, throwing away all of the benefits of an RDBMS without even knowing why that’s a terrible idea

        Having spent many years with such coders, never pleasantly, they know it’s *not* a terrible idea. From their point of view. CS graduates still leave school with a language centric mind. Not a data centric mind. As such, they view app dev just the way their COBOL wielding grandpappies did: I gots me a bunch o dumb bytes, so I gots to write some smart code to wrangle them bytes. Ergo: they gets to write lots more code. More employment for them. Never mind the collateral damage; they never do. They aren’t being stupid, only smart in their limited view sort of way. It’s intentional.

        Reply
    2. michel

      I agree Noah. This concept of two tables sounds so logical when explained, but when implemented it is a real nightmare as a developer. Only collections of attributes to work with, and getting 600 rows for 30 objects with 20 properties, no integrity check, and reporting made people jump out of the window.

      Reply
    3. Chris Rueber

      Righteous fury, much? Also, you should look up the definition of the word ‘amateur’.

      It’s not entirely a load of total crap, either. Fact is, there are many cases RDBMS systems don’t shine. As a document store, for instance. Postgres is pretty good at storing arbitrary files, but why would you muddy the waters? It can store JSON data, but you’ve lost the purpose of an RDB at that point. Just because you can do something with an RDB does not mean you should.

      Not sure I like the thing/data store concept, with stores like Riak, Mongo, and Cassandra hanging around, but I can see the value in keeping data this way. Particularly if you don’t have a bunch of DBAs hanging around to help in discovery of whether or not your database supports certain features.

      Reply
      1. Dennis Decker Jensen

        Actually PostgreSQL is a fine document-store or key-value-store. You should look into the hdata-type. The relational model doesn’t put any constraints on the types you can use. Any RDBMS is fine for any information requiring structure. You might also want to check out presentations from Instagram to see how they were able to scale massively with PostgreSQL. Relational databases do shine for just about all cases, it’s just that many people are not educated to use them properly, or even allowed to do so otherwise.

        Reply
    4. Mike Blaszczak

      Indeed, Noah — it seems like this structure was chosen to work around an RDBMs that was flawed in taking a long time to do metadata updates. Adding a column with no valu should take no time at all, needing only a schema lock and not any kind of data locks. The code accessing the data can remember that the NULLs in the new columns are not set and enact its own default, or write back a default as the records are accessed anyay.

      Mixing types of entities in the same table ends up causing the table to be hot for contention and necessitates extra indexing to find the subset rows of each logical entity that’s been lumped into the same table. Inefficient for storage and caching, this also becomes na issue for locking because the sequential nature of th scans over the localized entities ends up being likely to promote small locks (rows, pages) to larger locks (pages, extents, the whole table).

      Reply
  6. Roy

    Well, sure anyone can only own 2 tables. You’ve just pushed all your database work back on the programming staff. Six of one half-dozen of another. You’ve eliminated time consuming database functions at the expense of programming. Either is OK. Just depends on where you want your expenses.

    Reply
    1. rdp

      First, it’s worth noting that six 20-something-year-old programmers are WAY cheaper than a half-dozen DBA experts.

      This has got me thinking about what some people would call a “fad” in noSQL: while full ACID compliance and 3NF has its place, to completely dismiss noSQL is akin Bethlehem Steel dismissing mini-mills in the 1980s (cue Christiansen’s “Innovator’s Dilemma”): the cost structure of noSQL is much lower, the technology will improve and will eventually take over many applications currently served by full SQL databases.

      Reply
  7. lazyload

    We are also using this design in our office. We have about 10 billion rows of data. Imagine adding an index to each column used in a traditional way.

    Here you only have to add index on key and value column. Eases the maintenance part and results are extremely fast.

    Reply
    1. Chris

      You have a two column table, with a two column index? So, the index is essentially a clone of the table? How is this useful?

      Reply
  8. David Mytton

    Schemaless design is one of the advantages of MongoDB which makes it great for development. You just download the binary then run it, and you have a database ready to go. That doesn’t mean you don’t have to thing about the structure though because it’s not really “schemaless” – every document has fields and you need to be aware of them for creating the right indexes. It’s also easy for a typo to be a major bug.

    In production the advantages are that you don’t need to alter the table structure – you just do it in code. That avoids long running ALTER queries…but you still have to create indexes on new fields (even though they can be run in the background).

    Reply
  9. Pingback: Thought this was cool: Reddit’s database has two tables | Kevin Burke « CWYAlpha

  10. Federico Ramírez

    That’s quite interesting… You DO have a lot of manual work to do, but also the advantages are huge.
    And I’m surprised about Postgres beeing faster for key / value than NoSQL. I guess I’ll have some fun this weekend.

    Reply
  11. Pingback: Today in bookmarks for August 31st. | ngerakines.me

  12. Pingback: What’s wrong with universities database class and how to prepare for the future? | Raw thoughts from Alex Dong

  13. Alex

    Looks very similar to Entity-Attribute-Value (EAV) concept, but it completely fails if you need to do selections based on attributes.

    Reply
  14. Pingback: 205: TZ Discussion – Check Your Egometer

  15. Konstantin isaev

    Hey, why 2 tables? why not 1?
    a single ocean of key-value pairs, where keys are have a kind of convention like this:
    o_{objectid}_{attributeid} – key [with two guids] for value
    o_{objectid}_type – key for id of type the {objectid} belongs to
    t_{typeid} – name of type {typeid}
    a_{typeid}_{attributeid} – name of attribute that contains name of attribute {attributeid} of {typeid}
    a_{typeid}_{attributeid}_type – attribute with values type of the {attributeid}
    and more blah blah blah.
    There is only one problem with this. If you need key-value pairs storage, you may be don’t need RDBMS at all for a task? Preparing coffee in a microwave oven is not a good idea, is it?

    Reply
  16. Pingback: Rounded Corners 343 — Worked fine in dev | Labnotes

  17. Pingback: State of Data #116 « Dr Data's Blog

  18. Pingback: Facebook Multifeed « Missional Code

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>