19 ms·

Reddit's database has only two tables

296 points by kevinburke 14y ago

pud 14y ago
I wish I read this article a year ago before I started developing my latest app. I have that exact problem, where adding a column to a 10M+ row table takes over an hour. So instead I end up with "things" tables all over the place. Sigh.
- MBCook 14y ago
  The last company I worked at made an application that used the Entity-Attribute-Value pattern in the database. The stated reason was to be dynamic, so we didn't have to worry about adding new columns and the associated downtime (assuming the DB got huge, which of course it would because this app would surely be a huge success). We had that problem on our main app (with 10s of millions of rows) where adding a column was always tricky, so I think management over-corrected. The other supposed win was that since the model didn't change, the code didn't need to be updated. The data that was being stored fit into the relational model pretty well. But thanks to E.A.V. it was very difficult to query. The kinds of questions we often looked at (how many records from this zip code) would have been trivial without the E.A.V. Today you might use a NoSQL database (which were just starting to get noticed at the time), but in reality it fit into MySQL just fine. The real sad part is, we never used that functionality in the 2-3 years after it was developed while I was there. The app wasn't big enough for adding columns to take much time at all. All that "flexibility" we needed? We didn't use it, because it would have taken additional time to implement the additional front-ends and update the other backend systems. Even if we wanted to keep things smaller, we could have gone with a table per type of record (record_type_one, record_type_two, etc) instead of one big records table. That would have made schema changes easier. And of course, the code did need to be updated. Always. Sure there were no new columns that might cause problems if they didn't have default values, but you can never make changes to an app without code changes. We still had to implement the new interface. We had to implement the code to post that new kind of record to the systems it got processed by. Making the code handle the DB changes would have taken less time than the day or two a designer might work on the front end. It never would have been a bottleneck.
  - amitdugar 14y ago
    Looks like you guys did a bit of a premature optimization there. I know how painful it can get :(
    - MBCook 14y ago
      That's exactly what it was. It was designed to replace a growing set of systems that were all tiny forks of the same basic code base over a couple of years. Managing all that had become a mess, and it did need to be replaced. But there wan an opportunity and we ended up trying to reach for the stars when we should have aimed a bit lower. The irony is that after the system had been in production for a few months, we noticed it had terrible performance that was getting worse with load. It turned out the programmer who had written some parts of the system had it recalculating way too much data, things that didn't apply to what was going on and that couldn't have changed. It was probably an artifact from initial development (I'll do it this way to get it going, then cleanup later...). Once we caught and fixed that, it was much much faster. That was the only optimization I remember it needing while I was there.
zzzeek 14y ago
Pretty sure Reddit has thousands of tables - last time I looked, it was really hard to see this but this is how it seemed like it was working. It has "thing"/"data" tables for every subreddit - created on the fly (a crime for which any DBA would have you put to death, normally). While I'm honored they use my library (SQLAlchemy) for relational database access, their actual usage of the relational DB couldn't be more...well... let's just say please don't imitate their style. If you want to build a Reddit, use Mongo/Cassandra or something like that. They'd very likely have done so themselves if NoSQL products were mature when they first developed their platform (I am vaguely recalling/guessing here on that one). Edit: if any reddit devs want to correct me here, feel free, as I found the reddit source extremely difficult to follow back when I looked.
- Hovertruck 14y ago
  Reddit actually does use Cassandra, and you can read about their adventures and such with it here: http://blog.reddit.com/search/label/Cassandra http://blog.reddit.com/search/label/Cassandra
  - deleted 14y ago
    [deleted]
- kevinburke 14y ago
  As the OP, would also appreciate Reddit dev input into what they're using now... I am summarizing a summary of a Reddit talk from 2010.
  - jedberg 14y ago
    See my comment here: http://news.ycombinator.com/item?id=4468905 http://news.ycombinator.com/item?id=4468905 There isn't one table per subreddit. I'm not a current dev, FYI, I left a year ago. But as far I know, it still works the same way as a year ago. Which is the same as it worked 3 years ago.
- mvc 14y ago
  > created on the fly (a crime for which any DBA would have you put to death, normally) Is the crime in creating tables on the fly? Or creating tables of identical structure on the fly?
lazyjones 14y ago
Seriously? Sounds like a half-baked reimplementation of a https://en.wikipedia.org/wiki/Triplestore https://en.wikipedia.org/wiki/Triplestore to me ... The post he refers to is more than 2 years old, things may have changed.
prodigal_erik 14y ago
This is called a http://en.wikipedia.org/wiki/Entity-attribute-value_model http://en.wikipedia.org/wiki/Entity-attribute-value_model or http://en.wikipedia.org/wiki/Triplestore http://en.wikipedia.org/wiki/Triplestore. I think the author is understating the price, though. There's a lot of existing software you could reuse if your data was stored in more conventional relations, and "manually enforce consistency" is a pipe dream. Your code has expectations about your data, so in the abstract you do still have a schema, and not writing it down merely prevents any tools from helping you keep your data sane over time. I've seen Notes databases decay to the point that not even the dev team could explain how a document got into its contradictory state nor how the apps should (much less currently would) handle it. The few people diligent enough to do completely correct work without a checkable schema, aren't the people who would be tempted to try.
- aed 14y ago
  I recently read The Pragmatic Programmers book SQL Antipatterns: Avoiding the Pitfalls of Database Programming (http://pragprog.com/book/bksqla/sql-antipatterns http://pragprog.com/book/bksqla/sql-antipatterns) and chapter 6 talks specifically about this kind of DB design (entity-attribute-value) and the benefits/pitfalls. It's a great read and I highly recommend it.
  - mikegirouard 14y ago
    I bought that book after my first time developing on top of Magento. Pure curiosity: Do you know of any other OSS projects that went down this route?
    - bigiain 14y ago
      Yeah, I'd been thinking "Magento" from about halfway into that article. Wordpress uses the EAV db pattern too (have a look at the wp_options and wp_meta tables). Of course, Magento and Wordpress use it in fundamentally different ways - so this project I've got to build a combined search across a site with both Magento (EAV for products) and Wordpress (non-EAV for posts/pages) in use, is in that "it'll sit on the backburner until it becomes critical or somebody else solves it for me first" state…
kjhughes 14y ago
This is a thin wrapper around a good, but two year old, High Scalability post: http://highscalability.com/blog/2010/5/17/7-lessons-learned-while-building-reddit-to-270-million-page.html http://highscalability.com/blog/2010/5/17/7-lessons-learned-... which was discussed on HN recently: http://news.ycombinator.com/item?id=4467077 http://news.ycombinator.com/item?id=4467077 and on HN long ago: http://news.ycombinator.com/item?id=1354542 http://news.ycombinator.com/item?id=1354542 EDIT: Fixed "long ago" vs "recently". Thanks, sync.
- deleted 14y ago
  [deleted]
rfurmani 14y ago
Umm, assuming the github repo is what they actually use (i assume so given how often it is committed to) there are two tables per object. Reddit_thing_link, reddit_data_link, reddit_rel_thing_savehide, etc etc
tzs 14y ago
> Adding a column to 10 million rows takes locks and doesn’t work. It does not take locks, other than for very briefly. 1. Make a new empty table that has the same structure as the table you wish to add a column to. Add your new column to the empty table. 2. Put triggers on the old table that, whenever a row is added or updated, makes a copy of the row in the new table or updates the copy already there. 3. Run a background process that goes through the old table doing dummy updates: UPDATE table SET some_col = some_col WHERE ... where the WHERE clause picks a small number of rows (e.g., just go through the primary key sequentially). Since you aren't actually modifying the table, all this does is trigger the trigger on the specified rows. 4. When you've hit everything with a dummy update, rename the current table to a temp name, and rename the new table to the current table. This is the only step that needs a lock. There are tools for MySQL to automate much of this. There was a post either here or on Reddit a while back about this which linked to them. I'm sorry but I didn't save a link to it so you'll have to search if you want it.
- leif 14y ago
  Or (shameless plug) use tokudb. We have many online schema change features that work within mysql. You can add and remove columns while the database is running, without resorting to complicated tricks with triggers. You just add the column, it completes immediately, and you start using it right away. The work of changing existing rows happens in the background, in a transactionally consistent manner, so you never see the old schema again. It's pretty sweet.
  - saurik 14y ago
    FWIW, I often look at tokudb with envy, but there is no way I will switch to MySQL from PostgreSQL for it: there are so many other things I'd lose that I would rather put up with my other issues. That said, and I totally admit it was now over a year ago that I was looking into various database implementation companies (and thereby might have you confused with a differen company), it seems to me like your technology would be amazing if used as a standalone index (as opposed to also storing the heap), and adding indexes to PostgreSQL is both simple and "part of the point of using it in the first place". (I also would have expected the PostgreSQL market to be better for this kind of thing, although I'm not in this business, so I'd love to understand where I'm wrong: the general argument being that relying on a third-party commercial index is a much lower commitment--and thereby a simpler sell--than relying on a third-party commercial storage engine, in addition to how most companies using MySQL seem to either know so little about database technology that their criteria was simply "popularity" or, alternatively, so much about database technology that to them MySQL is perfectly fine and they don't really need your solution.)
ocharles 14y ago
That quote is just painful to read, littered with FUD and not a single bit of evidence to back it up. You should worry about the database because it's probably your canonical storage of data, which for most of us is the most important part of our product/service/whatever. A good schema enforces consist data, invariants, and all sorts of other stuff that you don't want to be dealing with a manual (and buggy) basis. Schema updates do not need to be slow. They might not always be as elegant as you hope but the big databases are improving on that front, and as tzs mentions - there are tricks that can be employed. With the latest and greatest PG, I believe we're even starting to get event triggers, so it may well be possible to do schema updates with replication. I also have a feeling the binary replication in PG 9 and up can even do it out of the box, with hot standby to still allow responses. I'm not entirely convinced replication is a backup solution, so maybe that was an operations antipattern. That's some baseless assertion from me though :) If deployments are a pain, work to alleviate pain. They are pretty mechanical, even if involved, which lead very nicely to being automated. Seriously, we're smart people, let's not throw at least 30 years of research out the window in favour of glorified entity-attribute-value schemas.
SoftwareMaven 14y ago
This is roughly the path I'm taking on my current project. There are some things that have a consistent, "traditional" schema. However, many of the feature-important things are being stored as JSON blobs. This means that I don't have to worry about schema migrations when I add a new feature; instead, I just define its JSON schema. This works particularly well because I don't need to join on these items. The downside is that I do have to do occasional manual processing on them (sum up all values of type FOO) that would be trivial if everything were perfectly normalized.
- SideburnsOfDoom 14y ago
  > many of the feature-important things are being stored as JSON blobs. ... when I add a new feature I just define its JSON schema. Some no-sql stores work like that. You may be better off using one of them.
  - SoftwareMaven 14y ago
    I looked at that (and have done that in the past), but I really missed some of the ORM features I got with relational. I would up writing more glue code than I liked for things that would otherwise be free. This is my compromise world (and Postgres is getting better at handling its JSON).
gioele 14y ago
Basically they rediscovered RDF. They should switch to an RDF triplestore, at least they would be able to exploit some of the RDF-only optimizations and their validation tools.
TylerBrock 14y ago
Obvious article is obvious, this is exactly how facebook works and why MongoDB is important.
cageface 14y ago
I used to work at a company with a huge LDAP database that had been growing in uncontrolled and organic ways for years. If you've never worked with LDAP before, it's basically a big key-value store, with some optional schema enforcement. We spent an inordinate amount of time manually cleaning it up and trying to enforce some kind of data integrity and we were always bemoaning the lack of a real structural schema. I think the kind of ad-hoc generic data typing described in this article is sometimes the right solution but it comes at a cost.
ionforce 14y ago
My fear with headlines like this is that people with no business working at the scale that Reddit does will suddenly eschew years of best practice SQL and by like "my dad's pizza shop CRM only needs two tables, JUST LIKE REDDIT". Everyone please, use your brain before repeating such specific configurations. Reddit is quite exceptional. Your burgeoning to-do list app is not.
sjs 14y ago
No, they have <thing> and <thing data> tables for each entity. This is a grave misunderstanding of what was actually said.
raldi 14y ago
Note: Steve was exaggerating a bit, or this post misrepresents what he said. I can think of 12 just off the top of my head. That said, many of them fit the same pattern: reddit_thing_link reddit_thing_comment reddit_thing_user ...and each had a complement with "data" in place of "thing". Though there were also ones like: reddit_xref_subscription_user_subreddit reddit_xref_vote_user_link reddit_xref_vote_user_comment ...for the many-to-many relations. That accounts for most of the big ones (though there are also a handful of smaller specialty tables, too.) Also, the above is as of March 2011 -- a lot has moved to Cassandra since then.
- yaix 14y ago
  Thanks for the comment, that makes more sense. I was trying to figure out in my head how to do the lookups to reander comments with votes and what comments the user voted on all from one data table. Also I guess the table schemas are slightly different for the different "thing" tables.
  - raldi 14y ago
    Nope, all "thing" tables have exactly the same schema as each other. Ditto "data" and "xref" tables.
t_hozumi 14y ago
Datomic takes similar approach. A unit of Entity, Attribute, Value and Time is called datom. Although in this approach we don't have to have schema, datomic forces us to define schema for some benefits.
Morg 14y ago
Surely you jest. Another web2.0 company with absolutely zero understanding of databases ? IMPOSSIBRU ! EAV is the half-assed approach to data management. When in fact it would be just as easy to store the information in a newly created table, some people consider it's way too hard (lol) to do dynamically and thus prefer EAV. And then some of those fools go as far as to say that it's better for keeping track of row versions (which is retarded, because ... just write the query to return the state of a group of objects at a set date and you'll understand) and whatever. The correct approach : read data to array, create_table(array), just run a select to get a row. The EAV approach : read data to array, break it up in cell-sized chunks, put it all in a table, use retarded long complex queries to do something simple. Apart from bad OO design or lack of understanding of databases, I don't see what could justify that kind of approach.
SideburnsOfDoom 14y ago
They've built a database in their database. Also known as the inner platform antipattern: http://en.wikipedia.org/wiki/Inner-platform_effect http://en.wikipedia.org/wiki/Inner-platform_effect http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx http://thedailywtf.com/Articles/The_Inner-Platform_Effect.as...

296 points by kevinburke 14y ago

pud 14y ago

I wish I read this article a year ago before I started developing my latest app. I have that exact problem, where adding a column to a 10M+ row table takes over an hour. So instead I end up with "things" tables all over the place. Sigh.

MBCook 14y ago
The last company I worked at made an application that used the Entity-Attribute-Value pattern in the database. The stated reason was to be dynamic, so we didn't have to worry about adding new columns and the associated downtime (assuming the DB got huge, which of course it would because this app would surely be a huge success). We had that problem on our main app (with 10s of millions of rows) where adding a column was always tricky, so I think management over-corrected. The other supposed win was that since the model didn't change, the code didn't need to be updated. The data that was being stored fit into the relational model pretty well. But thanks to E.A.V. it was very difficult to query. The kinds of questions we often looked at (how many records from this zip code) would have been trivial without the E.A.V. Today you might use a NoSQL database (which were just starting to get noticed at the time), but in reality it fit into MySQL just fine. The real sad part is, we never used that functionality in the 2-3 years after it was developed while I was there. The app wasn't big enough for adding columns to take much time at all. All that "flexibility" we needed? We didn't use it, because it would have taken additional time to implement the additional front-ends and update the other backend systems. Even if we wanted to keep things smaller, we could have gone with a table per type of record (record_type_one, record_type_two, etc) instead of one big records table. That would have made schema changes easier. And of course, the code did need to be updated. Always. Sure there were no new columns that might cause problems if they didn't have default values, but you can never make changes to an app without code changes. We still had to implement the new interface. We had to implement the code to post that new kind of record to the systems it got processed by. Making the code handle the DB changes would have taken less time than the day or two a designer might work on the front end. It never would have been a bottleneck.
- amitdugar 14y ago
  Looks like you guys did a bit of a premature optimization there. I know how painful it can get :(
  - MBCook 14y ago
    That's exactly what it was. It was designed to replace a growing set of systems that were all tiny forks of the same basic code base over a couple of years. Managing all that had become a mess, and it did need to be replaced. But there wan an opportunity and we ended up trying to reach for the stars when we should have aimed a bit lower. The irony is that after the system had been in production for a few months, we noticed it had terrible performance that was getting worse with load. It turned out the programmer who had written some parts of the system had it recalculating way too much data, things that didn't apply to what was going on and that couldn't have changed. It was probably an artifact from initial development (I'll do it this way to get it going, then cleanup later...). Once we caught and fixed that, it was much much faster. That was the only optimization I remember it needing while I was there.

zzzeek 14y ago

Pretty sure Reddit has thousands of tables - last time I looked, it was really hard to see this but this is how it seemed like it was working. It has "thing"/"data" tables for every subreddit - created on the fly (a crime for which any DBA would have you put to death, normally). While I'm honored they use my library (SQLAlchemy) for relational database access, their actual usage of the relational DB couldn't be more...well... let's just say please don't imitate their style. If you want to build a Reddit, use Mongo/Cassandra or something like that. They'd very likely have done so themselves if NoSQL products were mature when they first developed their platform (I am vaguely recalling/guessing here on that one). Edit: if any reddit devs want to correct me here, feel free, as I found the reddit source extremely difficult to follow back when I looked.

Hovertruck 14y ago
Reddit actually does use Cassandra, and you can read about their adventures and such with it here: http://blog.reddit.com/search/label/Cassandra http://blog.reddit.com/search/label/Cassandra
- deleted 14y ago
  [deleted]
kevinburke 14y ago
As the OP, would also appreciate Reddit dev input into what they're using now... I am summarizing a summary of a Reddit talk from 2010.
- jedberg 14y ago
  See my comment here: http://news.ycombinator.com/item?id=4468905 http://news.ycombinator.com/item?id=4468905 There isn't one table per subreddit. I'm not a current dev, FYI, I left a year ago. But as far I know, it still works the same way as a year ago. Which is the same as it worked 3 years ago.
mvc 14y ago
> created on the fly (a crime for which any DBA would have you put to death, normally) Is the crime in creating tables on the fly? Or creating tables of identical structure on the fly?

lazyjones 14y ago

Seriously? Sounds like a half-baked reimplementation of a https://en.wikipedia.org/wiki/Triplestore https://en.wikipedia.org/wiki/Triplestore to me ... The post he refers to is more than 2 years old, things may have changed.

prodigal_erik 14y ago

This is called a http://en.wikipedia.org/wiki/Entity-attribute-value_model http://en.wikipedia.org/wiki/Entity-attribute-value_model or http://en.wikipedia.org/wiki/Triplestore http://en.wikipedia.org/wiki/Triplestore. I think the author is understating the price, though. There's a lot of existing software you could reuse if your data was stored in more conventional relations, and "manually enforce consistency" is a pipe dream. Your code has expectations about your data, so in the abstract you do still have a schema, and not writing it down merely prevents any tools from helping you keep your data sane over time. I've seen Notes databases decay to the point that not even the dev team could explain how a document got into its contradictory state nor how the apps should (much less currently would) handle it. The few people diligent enough to do completely correct work without a checkable schema, aren't the people who would be tempted to try.

aed 14y ago
I recently read The Pragmatic Programmers book SQL Antipatterns: Avoiding the Pitfalls of Database Programming (http://pragprog.com/book/bksqla/sql-antipatterns http://pragprog.com/book/bksqla/sql-antipatterns) and chapter 6 talks specifically about this kind of DB design (entity-attribute-value) and the benefits/pitfalls. It's a great read and I highly recommend it.
- mikegirouard 14y ago
  I bought that book after my first time developing on top of Magento. Pure curiosity: Do you know of any other OSS projects that went down this route?
  - bigiain 14y ago
    Yeah, I'd been thinking "Magento" from about halfway into that article. Wordpress uses the EAV db pattern too (have a look at the wp_options and wp_meta tables). Of course, Magento and Wordpress use it in fundamentally different ways - so this project I've got to build a combined search across a site with both Magento (EAV for products) and Wordpress (non-EAV for posts/pages) in use, is in that "it'll sit on the backburner until it becomes critical or somebody else solves it for me first" state…

kjhughes 14y ago

This is a thin wrapper around a good, but two year old, High Scalability post: http://highscalability.com/blog/2010/5/17/7-lessons-learned-while-building-reddit-to-270-million-page.html http://highscalability.com/blog/2010/5/17/7-lessons-learned-... which was discussed on HN recently: http://news.ycombinator.com/item?id=4467077 http://news.ycombinator.com/item?id=4467077 and on HN long ago: http://news.ycombinator.com/item?id=1354542 http://news.ycombinator.com/item?id=1354542 EDIT: Fixed "long ago" vs "recently". Thanks, sync.

deleted 14y ago
[deleted]

rfurmani 14y ago

Umm, assuming the github repo is what they actually use (i assume so given how often it is committed to) there are two tables per object. Reddit_thing_link, reddit_data_link, reddit_rel_thing_savehide, etc etc

tzs 14y ago

> Adding a column to 10 million rows takes locks and doesn’t work. It does not take locks, other than for very briefly. 1. Make a new empty table that has the same structure as the table you wish to add a column to. Add your new column to the empty table. 2. Put triggers on the old table that, whenever a row is added or updated, makes a copy of the row in the new table or updates the copy already there. 3. Run a background process that goes through the old table doing dummy updates: UPDATE table SET some_col = some_col WHERE ... where the WHERE clause picks a small number of rows (e.g., just go through the primary key sequentially). Since you aren't actually modifying the table, all this does is trigger the trigger on the specified rows. 4. When you've hit everything with a dummy update, rename the current table to a temp name, and rename the new table to the current table. This is the only step that needs a lock. There are tools for MySQL to automate much of this. There was a post either here or on Reddit a while back about this which linked to them. I'm sorry but I didn't save a link to it so you'll have to search if you want it.

leif 14y ago
Or (shameless plug) use tokudb. We have many online schema change features that work within mysql. You can add and remove columns while the database is running, without resorting to complicated tricks with triggers. You just add the column, it completes immediately, and you start using it right away. The work of changing existing rows happens in the background, in a transactionally consistent manner, so you never see the old schema again. It's pretty sweet.
- saurik 14y ago
  FWIW, I often look at tokudb with envy, but there is no way I will switch to MySQL from PostgreSQL for it: there are so many other things I'd lose that I would rather put up with my other issues. That said, and I totally admit it was now over a year ago that I was looking into various database implementation companies (and thereby might have you confused with a differen company), it seems to me like your technology would be amazing if used as a standalone index (as opposed to also storing the heap), and adding indexes to PostgreSQL is both simple and "part of the point of using it in the first place". (I also would have expected the PostgreSQL market to be better for this kind of thing, although I'm not in this business, so I'd love to understand where I'm wrong: the general argument being that relying on a third-party commercial index is a much lower commitment--and thereby a simpler sell--than relying on a third-party commercial storage engine, in addition to how most companies using MySQL seem to either know so little about database technology that their criteria was simply "popularity" or, alternatively, so much about database technology that to them MySQL is perfectly fine and they don't really need your solution.)

ocharles 14y ago

That quote is just painful to read, littered with FUD and not a single bit of evidence to back it up. You should worry about the database because it's probably your canonical storage of data, which for most of us is the most important part of our product/service/whatever. A good schema enforces consist data, invariants, and all sorts of other stuff that you don't want to be dealing with a manual (and buggy) basis. Schema updates do not need to be slow. They might not always be as elegant as you hope but the big databases are improving on that front, and as tzs mentions - there are tricks that can be employed. With the latest and greatest PG, I believe we're even starting to get event triggers, so it may well be possible to do schema updates with replication. I also have a feeling the binary replication in PG 9 and up can even do it out of the box, with hot standby to still allow responses. I'm not entirely convinced replication is a backup solution, so maybe that was an operations antipattern. That's some baseless assertion from me though :) If deployments are a pain, work to alleviate pain. They are pretty mechanical, even if involved, which lead very nicely to being automated. Seriously, we're smart people, let's not throw at least 30 years of research out the window in favour of glorified entity-attribute-value schemas.

SoftwareMaven 14y ago

This is roughly the path I'm taking on my current project. There are some things that have a consistent, "traditional" schema. However, many of the feature-important things are being stored as JSON blobs. This means that I don't have to worry about schema migrations when I add a new feature; instead, I just define its JSON schema. This works particularly well because I don't need to join on these items. The downside is that I do have to do occasional manual processing on them (sum up all values of type FOO) that would be trivial if everything were perfectly normalized.

SideburnsOfDoom 14y ago
> many of the feature-important things are being stored as JSON blobs. ... when I add a new feature I just define its JSON schema. Some no-sql stores work like that. You may be better off using one of them.
- SoftwareMaven 14y ago
  I looked at that (and have done that in the past), but I really missed some of the ORM features I got with relational. I would up writing more glue code than I liked for things that would otherwise be free. This is my compromise world (and Postgres is getting better at handling its JSON).

gioele 14y ago

Basically they rediscovered RDF. They should switch to an RDF triplestore, at least they would be able to exploit some of the RDF-only optimizations and their validation tools.

TylerBrock 14y ago

Obvious article is obvious, this is exactly how facebook works and why MongoDB is important.

cageface 14y ago

I used to work at a company with a huge LDAP database that had been growing in uncontrolled and organic ways for years. If you've never worked with LDAP before, it's basically a big key-value store, with some optional schema enforcement. We spent an inordinate amount of time manually cleaning it up and trying to enforce some kind of data integrity and we were always bemoaning the lack of a real structural schema. I think the kind of ad-hoc generic data typing described in this article is sometimes the right solution but it comes at a cost.

ionforce 14y ago

My fear with headlines like this is that people with no business working at the scale that Reddit does will suddenly eschew years of best practice SQL and by like "my dad's pizza shop CRM only needs two tables, JUST LIKE REDDIT". Everyone please, use your brain before repeating such specific configurations. Reddit is quite exceptional. Your burgeoning to-do list app is not.

sjs 14y ago

No, they have <thing> and <thing data> tables for each entity. This is a grave misunderstanding of what was actually said.

raldi 14y ago

Note: Steve was exaggerating a bit, or this post misrepresents what he said. I can think of 12 just off the top of my head. That said, many of them fit the same pattern: reddit_thing_link reddit_thing_comment reddit_thing_user ...and each had a complement with "data" in place of "thing". Though there were also ones like: reddit_xref_subscription_user_subreddit reddit_xref_vote_user_link reddit_xref_vote_user_comment ...for the many-to-many relations. That accounts for most of the big ones (though there are also a handful of smaller specialty tables, too.) Also, the above is as of March 2011 -- a lot has moved to Cassandra since then.

yaix 14y ago
Thanks for the comment, that makes more sense. I was trying to figure out in my head how to do the lookups to reander comments with votes and what comments the user voted on all from one data table. Also I guess the table schemas are slightly different for the different "thing" tables.
- raldi 14y ago
  Nope, all "thing" tables have exactly the same schema as each other. Ditto "data" and "xref" tables.

t_hozumi 14y ago

Datomic takes similar approach. A unit of Entity, Attribute, Value and Time is called datom. Although in this approach we don't have to have schema, datomic forces us to define schema for some benefits.

Morg 14y ago

Surely you jest. Another web2.0 company with absolutely zero understanding of databases ? IMPOSSIBRU ! EAV is the half-assed approach to data management. When in fact it would be just as easy to store the information in a newly created table, some people consider it's way too hard (lol) to do dynamically and thus prefer EAV. And then some of those fools go as far as to say that it's better for keeping track of row versions (which is retarded, because ... just write the query to return the state of a group of objects at a set date and you'll understand) and whatever. The correct approach : read data to array, create_table(array), just run a select to get a row. The EAV approach : read data to array, break it up in cell-sized chunks, put it all in a table, use retarded long complex queries to do something simple. Apart from bad OO design or lack of understanding of databases, I don't see what could justify that kind of approach.

SideburnsOfDoom 14y ago

They've built a database in their database. Also known as the inner platform antipattern: http://en.wikipedia.org/wiki/Inner-platform_effect http://en.wikipedia.org/wiki/Inner-platform_effect http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx http://thedailywtf.com/Articles/The_Inner-Platform_Effect.as...