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.
Liked what you read? I am available for hire.
That article is from 2010.
Thanks, I’ve updated the post to make that point clear.
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….
Very similar to the schema FriendFeed used back before they were bought by Facebook (and probably still to this day since it seems to be exactly the same).
http://backchannel.org/blog/friendfeed-schemaless-mysql
There was a Ruby library inspired by that post called Friendly ORM that was being used to power fetlife.com for a while there, too. I don’t know if that’s being actively maintained anymore, though.
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?
@Toby: Neither. Nobody remembers IBM’s contributions, so they don’t mention it.
@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 :)
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.
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.
Zero seconds? Except if you have default value. Then it takes ages.
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.
Umm. Not in Oracle. Default values are stored in the data dictionary. Still 0 seconds.
That’s great, but when you’re two guys in a garage, you can’t afford Oracle.
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.
— 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.
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.
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.
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.
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).
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.
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.
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.
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?
PostgreSQL has an extension called hstore. This is what they should use.
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).
Pingback: Thought this was cool: Reddit’s database has two tables | Kevin Burke « CWYAlpha
So it’s a MapReduce solution, done in SQL.
Why not go directly to a noSQL solution then?
Which one?
CouchDB had only been released 2 months before Reddit launched, so waiting for that would have delayed their launch. Cassandra was still 3 years away from their first release, and MongoDB, Riak, and Redis were still 4 years away. Hypertable and HBase have still (in 2015) not had a stable 1.0 release. BerkeleyDB existed, but it’s not a serious choice for a shared scalable multi-user database.
Google’s now-famous “BigTable” USENIX paper was still a year in the future, too, which is what kicked off most of today’s NoSQL solutions.
I’m having trouble thinking of a better “NoSQL solution” that was at all usable in 2005. You could use raw files, but you’d have to implement your own indexing and concurrency and such.
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.
Pingback: Today in bookmarks for August 31st. | ngerakines.me
Pingback: What’s wrong with universities database class and how to prepare for the future? | Raw thoughts from Alex Dong
There also was an article on the architecture of friendfeed.com, or some other similar social site. They were employing similar but slightly different technique: http://backchannel.org/blog/friendfeed-schemaless-mysql
Looks very similar to Entity-Attribute-Value (EAV) concept, but it completely fails if you need to do selections based on attributes.
Pingback: 205: TZ Discussion – Check Your Egometer
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?
Pingback: Rounded Corners 343 — Worked fine in dev | Labnotes
Pingback: State of Data #116 « Dr Data's Blog
Pingback: Facebook Multifeed « Missional Code
“without the pain of schema updates”
I hear this supposed benefit a lot from NoSQL advocates, but my experience is exactly the opposite.
Having schema updates mean when I come up with a better way to structure something in the database, I write one UPDATE statement to describe how I want it to change, and then I can work with the new and improved structure.
NoSQL systems without schema updates mean I have to maintain every version of the schema in my application code, for all time.
What am I missing here? Why is that supposed to be better? Is it only for people who will have 10 million users?