16 ms·
Reddit’s database has two tables (2012)
- swyx 4y agofrom 10 years ago https://news.ycombinator.com/item?id=4468265 https://news.ycombinator.com/item?id=4468265
- dang 4y agoThanks! Macroexpanded: Reddit's database has only two tables - https://news.ycombinator.com/item?id=4468265 https://news.ycombinator.com/item?id=4468265 - Sept 2012 (79 comments)
- senttoschool 4y agoThey were using it like a NoSQL database back in 2010, just one year after MongoDB started. So there were no options. In 2022, there are so many more mature, reliable, battle-tested NoSQL options that could have solved their problem more elegantly. But even today, if I were to build Reddit as a startup, I'd start with a Postgres database and go as far as I can. Postgres allows me to use relations when I want to enforce data integrity and JSONB when I just want a key/value store.
- lmm 4y ago> But even today, if I were to build Reddit as a startup, I'd start with a Postgres database and go as far as I can. Postgres allows me to use relations when I want to enforce data integrity and JSONB when I just want a key/value store. This is accepted wisdom on HN but I don't see it: - True HA is very difficult. Even just automatic failover takes a lot of manual sysadmin effort - You can't autoscale, and while scaling is a nice problem to have, having to not just fundamentally rearchitect all your code when you need to shard, but also probably migrate all your data, is probably the worst version of it. - Even if you're not using transactions, you still pay the price for them. For example your writes won't commit until all your secondary indices have been updated. - If you're actually using JSONB then driver/mapper/etc. support is very limited. E.g. it's still quite fiddly to have an object where one field is a set of strings / enum values and have a standard ORM store that in a column rather than creating a join table.
- naniwaduni 4y agoDoes a startup in Reddit's niche demand HA?
- lmm 4y agoI would think that if you want to create a habit where people check you frequently, avoiding downtime would be important. But IDK.
- antupis 4y agoyou probably would have some CDN front of it so some database unavailability would not impact to browsing that much.
- sophacles 4y agoReddit has gone through many periods of regular unplanned downtime as they hit various scale issues over the years. It's certainly something to avoid, but not necessarily something that's catastrophic - like most decisions there are tradeoffs.
- rakoo 4y agoFor something like reddit, who cares about consistency ? Under massive load no user cares about knowing the exact number of votes, all the replies to a comment, etc... It's also perfectly fine to refuse some writes if the load is too high to bear.
- senttoschool 4y agoIf I were to start building Reddit from scratch without knowing if it's going to be successful or not, I'd rather iterate quick on features rather trying to spend time scaling something that will most likely fail before reaching that kind of scale. >True HA is very difficult. Even just automatic failover takes a lot of manual sysadmin effort Nearly all Postgres cloud providers such as RDS, CloudSQL, Upcloud, provide multi-region nodes and automatic failover. Also, true HA databases are very expensive and have their own drawbacks. Not really designed for a Reddit-style startup. >You can't autoscale, and while scaling is a nice problem to have, having to not just fundamentally rearchitect all your code when you need to shard, but also probably migrate all your data, is probably the worst version of it. Hence why I said "as far as I can". I can keep upgrading the instance, creating material views, caching responses, etc. Lastly, the most important reason is because I know Postgres and I can get started today. I don't want to learn a highly scalable, new type of database that I might need 5 years down the road.
- deleted 4y ago[deleted]
- senttoschool 4y agoOn a side note, I find it a bit ironic how NoSQL was all the rage back in the last decade or so but in 2022, NoSQL DBs are racing to add SQL querying back to key value stores. It turns out that SQL is crucial to non-app-developers such as business analysts, data scientists. Trying to setup an ETL to pull data from your MongoDB datastore to a Postgres DB so the analysts could generate reports is such a waste of time and resources. Or worse, the analysts have to request devs to write Javascript code to query the data they need. For this reason alone, I will always start with a DB that supports SQL out of the box.
- deleted 4y ago[deleted]
- ralusek 4y agoPeople didn't leave RDBMS for NoSQL because they wanted to get rid of RDBMS features, they did it because they couldn't scale their applications on single instances anymore. So they had to give up transactions and foreign keys and schemas, etc, because those concepts just didn't exist across instances. Not because they wanted to, but because they had to. The entire database world has been working ever since trying to get back those RDBMS features into their horizontally scalable solutions, it's just taken a while because the problems are exponentially harder in a network. It's not like people are using eventual consistency and a loss of referential integrity because they prefer it.
- senttoschool 4y ago>People didn't leave RDBMS for NoSQL because they wanted to get rid of RDBMS features, they did it because they couldn't scale their applications on single instances anymore. I agree that this should have been the reason for choosing something like MongoDB over MySQL/Postgres. In reality, I saw a lot about the companies switching/starting with because so their devs didn't have to learn SQL and they can just stay in Javascript/whatever language they were using.
- Tainnor 4y ago
- Misdicorl 4y ago> So there were no options. Hbase was a pretty solid NoSQL option by 2010, no?
- TekMol 4y agoThese days, for a new Web2 type startup, I would use SQLite. Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM. Another option worth taking a look at is to use no DB at all. Just writing to files is often the faster way to get going. The filesystem offers so much functionality out of the box. And the ecosystem of tools is marvelous. As far as I know, HN uses no DB and just writes all data to plain text files. It would actually be pretty interesting to have a look at how HN stores the data: https://news.ycombinator.com/item?id=32408322 https://news.ycombinator.com/item?id=32408322
- mandeepj 4y agoExactly! no separate machine required just for db. You can also backup SQLite db asynchronously
- senttoschool 4y agoYou can get a managed Postgres instance for $15/month these days. Likely cheaper, more secure, faster, better than an SQLite db hosted manually on a VM. >Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM. Plenty of setup. How would you secure your VM? SSL configuration? SSL expiration management? Security? How would you scale your VM to millions without downtime? Backups? Restoring backups? All these problems are solved with managed database hosting.
- throwaway0x7E6 4y agomy brother in Christ, SQLite is not a RDBMS. it runs on the same machine as your application. the scary, overwhelmingly complex problems you've enumerated should indeed be left to competent professionals to deal with, but they do not affect SQLite.
- geocar 4y ago> the scary, overwhelmingly complex problems you've enumerated should indeed be left to competent professionals to deal with You also aren’t getting that for $15/mo, either.
- 1penny42cents 4y agoHow has this evolved to today? Any growing pains or learning lessons? What stayed good about this as the team and product scaled?
- withinboredom 4y agoI worked at a startup (now fairly popular in the US) where we had tables for each thing (users, companies, etc) and a “relationship” table that described each relationship between things. There were no foreign keys, so making changes were pretty cheap. It was actually pretty ingenious (the two guys who came up with the schema went on to get paid to work on k8s). It was super handy to simply query that table to debug things, since by merely looking for a user, you’d discover everything. If Mongo was more mature and scalable back then (2012ish), I wonder if we would have used it.
- hamandcheese 4y agoSo basically one table for each type of node, plus one table for all edges. Neat! How big was the relationships table? How was performance?
- withinboredom 4y agoIt was pretty massive. I’m certain that it is probably sharded by now. Database performance wasn’t our bottleneck, we had np-hard problems all over the place which was our primary performance issues. For most customers, they never really had issues, but our larger customers def did. Those problems were amazingly fun to solve.
- prox 4y agoHow do you shard a thing like this? (Apart from “with great difficulty.”) :)
- hamandcheese 4y agoI was wondering the same thing. But at the same time, it seems like having it all centralized in one place would make it really easy to write tooling to aid in such an endeavor. For example, suppose it was decided that sharding would be by account ID. It would probably be very achievable to have some monitoring for all newly created relationships to check that they have an account ID specified, and break that down by relationship type (and therefor attributable to a team). Dashboards and metrics would be trivial to create and track progress, which would be essential for any large scale engineering effort.
- zegl 4y agoWe’re doing something similar at Codeball. A single Aurora RDS database (with read replicas), with a single table called “blobs”. The blobs table has three columns: id, type, and json. It’s scaling really really well, to many many millions of rows, and high traffic volumes (many thousands of writes per second).
- wizwit999 4y agoWhy not just use DynamoDB at that point, would likely be cheaper.
- senttoschool 4y agoYea I agree. Seems like it should have been a NoSQL solution from that start. Even today, it should be pretty straightforward to port the data over to a NoSQL db.
- manfre 4y agoDynamodb can be very painful with certain usage patterns or if you need to change/add usage patterns. For startups or MVP apps/features, it's a lot easier and cheaper to adhoc SQL.
- ianberdin 4y agoSorry but I do not believe in this magic. How many queries in realities do you need to fetch all “page” data?
- 4y ago
- ProfXponent 4y ago
- dehrmann 4y agoThe Thing model is almost just Third Normal Form, but where table is part of the key.
- revskill 4y agoYes, all production system could be built using two database: One for metadata, one for dynamic data. Or two tables: one for metadata, one for dynamic data. What's strange here ?
- dmix 4y ago> What's strange here ? strange or completely a-typical for 99% of web apps? Not sure what web apps you've worked on but the rest of the world doesnt default to anything like this. Even if it does make sense for the usecase, even Reddit only evolved to this much later.
- yobbo 4y agoFurthermore, you can have several metadata tables that represent different types of graphs of the data tables, using all the relational features. For example, one can implement role-based ACL on a tree structure in metadata tables, with one data table. Then sql can be used to get permissions, and even join on the data table, if that works for you.
- avereveard 4y ago"Amazon DynamoDB Deep Dive" https://www.youtube.com/watch?app=desktop&v=HaEPXoXVf2k&list=WL&index=110 https://www.youtube.com/watch?app=desktop&v=HaEPXoXVf2k&list... why and how of the single table design approach
- __derek__ 4y agoThat's exactly what I thought of when reading the title.
- saagarjha 4y agoInside of you are two tables. One is for things and the other is for data.
- commandlinefan 4y agoOne is evil and one is good. Which will win? The one you feed.
- BudaDude 4y agoInstructions unclear: I now have a millions things and no data.
- hardware2win 4y agoIndeed, I do wonder why industry still defaults to sql/relational instead of mature nosqls (there aint just mongo)
- kortilla 4y agoBecause schemas let you agree on the shape of the data without having to pull in applications to query things for reporting, analysis, etc. Additionally, forcing things into a correct schema also protects you from a lot of data inconsistencies out of the box. Finally, the dbms is going to be far better at JOINs than your application will. Nearly every web application needs JOINs at some point. Developers just don’t recognize what they are doing so they don’t realize what they are missing out on by doing it at the application layer.
- hardware2win 4y ago"At some point", but how much? I believe it is up to modeling skills Anyway - Schema slows development as hell, you need to maintain mapper layer between domain models and db models unless you model your system with anemic model mixed with tech stuff(db), but it is mess Additionally you deal with either stringly typed sql Or ORMs that require months of practice to be proficient even in c# which has LINQ
- senttoschool 4y agoBesides all the benefits of relational DBs, I'll give you my main reason for not starting with a NoSQL database: Because eventually, you'd want to analyze that data and it's a huge pain to do it with NoSQL. Imagine if you hired a business analyst and she needs to look at the data. Are you going to expect her to learn Javascript to query the data? Are you going to write an ETL pipeline to move data from a NoSQL db to an SQL db? Note: I know many NoSQL DBs now support SQL queries.
- corobo 4y agoPersonally it's a lack of trust. I've set up databases, I've broken databases, I've restored databases, I've scaled databases, and I've migrated them. Never lost so much as a nibble of data. My introduction to NoSQL was an article about Mongo losing someone's production data. Not even all of the data - partial data loss, the worst kind of data loss. I'm just gonna head off that massive headache at day 0 and use an ACID compliant database. From there it's lack of experience. Same reason I wouldn't decide to write my next thing in a new language unless I was specifically learning that language.
- romdev 4y agoI used to work at a company that had dozens of SQL tables for similar data - if there was a selection box in their app there was a table to represent it. I had a very productive meeting with the young (CSLA-trained) programmers on my way out, saying that we could have a single table for all lookup values. The schema went from a giant, whiteboard-covering diagram to a neat 8.5x11 single sheet diagram, resulting in a lot of reused code and data. Maybe there's more than Things and Data in real life, but there is a pretty good medium between "new table for each thing" and "each thing is just another thing row"
- HasanYousef 4y agoThank you so much for sharing this, I was exhausted from trying to make my data schemas be perfect for the long term. I will use this technique for my next projecta for sure. Thanks again.
- gorgoiler 4y agoFacebook was very similar, I believe. The MySQL schema boiled down to two tables: nodes with an id, a type, and key/value properties; and edges (known internally as assocs) between pairs of ids, also with key/value properties and a type. A simple schema helps when you just need to write stuff. You can make simple queries against a simple schema, directly. For the juicier queries what you do is you derive new tables from the data using ETL or map/reduce. The complexity of the underlying schema doesn’t have to reflect the complexity of your more complicated queries. It only needs to be complex enough to let you store data in its base form and make simple queries. Everything else can be derived on top of that, behind the scenes. Example: nodes are people and edges represent friendships, and then from this you could derive a new table of best_friend(id, bf_id, since).
- capitol_ 4y agoThis reminds me of when the /r/counting subreddit broke reddit: https://www.reddit.com/r/counting/comments/ww3vr/i_am_the_bearer_of_bad_news/ https://www.reddit.com/r/counting/comments/ww3vr/i_am_the_be... Their data model was not designed for very deep comment chains.
- asddubs 4y agoI wonder why they need to compute the complete comment tree to begin with
- jiggawatts 4y agoThis design is a fault of traditional RDBMS products that have strictly imperative and synchronous DML commands. The physical schema ought not be a transactional change, but it is in most products, with locks and everything. This isn't necessary, it's just an accident of history that nobody has gotten around to changing. For example: Rearranging the column order (or inserting a column) should just be an instant metadata update. Instead the entire table is rewritten... synchronously. Adding NON NULL columns similarly requires the value to be populated for every row at the point in time when the DML command is run. Instead, the default function should be added to the metadata, and then the change should roll out incrementally over time. Etc, etc... Whatever it is that programmers do with simple "objectid-columnname-value" schemas to make changes "instant" is what RDBMS platforms could also do, but don't.
- rastignack 4y ago> Adding NON NULL columns similarly requires the value to be populated for every row at the point in time when the DML command is run. Instead, the default function should be added to the metadata, and then the change should roll out incrementally over time. You mean just like mysql, oracle, sybase and mssql do ?
- alexpotato 4y agoAfter 15+ years of being an SRE in finance across firms ranging in size from 2 people to 350K people, I've come to the following conclusions: 1. There are no solutions, only tradeoffs. Sure. You win in the OP example of easy to add things but you lose in the relational aspects. You could say the same thing in reverse, using a RDBMS make it much easier to do join based lookups but then it's harder to update 2. At the end of the day, it doesn't matter b/c some other layer will do what your base layer can't I've seen some really atrocious approaches to storing data in systems that were highly regarded by the end users. How did this happen? Someone went in and wrote an abstraction layer that combined everything needed into an easy to query/access interface. I've seen this happen so many times that whenever people start trying to future proof something I mention this point. You will get the design wrong and/or things will change. That means you either change the base layer or you modify/create an abstraction layer.
- Waterluvian 4y agoRe #1: I agree. But with a pedantic semantic distinction: There are a lot of objectively wrong ways to do something. But among all the right ways, there are trade offs.
- Xeoncross 4y agoYou nailed it on the head (and people wonder why there are so many 'bad' laws passed).
- balfebs 4y agoWhen I was a Perl programmer, in our shop we amended the motto "There's more than one way to do it" to "There's more than one way to do it, but most of those ways are wrong." The Perl community themselves eventually extended the motto to "There's more than one way to do it, but sometimes consistency is not a bad thing either"
- 0xbadcafebee 4y agoBut even if you do something objectively wrong, it can still work out anyway. Look at every Perl script written by non-programmers. Horrifying mess of bullshit, and you try to read the code and think this shouldn't even be parseable, but somehow the script works. There is no right or wrong, only working and not-working.
- commandlinefan 4y ago> There are no joins in the database and you must manually enforce consistency In other words, you've manually developed a relational database on top of a relational database that you're using as a file system.
- 0xbadcafebee 4y agoIt's a relational database management system on a database management system. An RDBMSDBMS.
- deleted 4y ago[deleted]
- mabbo 4y agoIn 2004, I started community college program and had a professor teaching intro to databases. He was retired from industry and had lived through the rise of RDBMS's and made his money in that. Teaching was just his hobby. He liked to have fun with the class (120+ of us) by having us do call-response things as a group. Prof: "Why do we normalize?" Class, in unison: "To make better relations." Prof: "Why do we denormalize?" Class, in unison: "Performance." It took a lot of years of work before the simplicity of the lesson stuck with me. Normalization is good. Performance is good. You trade them off as needed. Reddit wants performance, so they have no strong relations. Your application may not need that, so don't prematurely optimize.
- blahyawnblah 4y ago> don't prematurely optimize Premature optimization is the root of all evil
- zinclozenge 4y agoSo is premature pessimization.
- isaacremuant 4y agoCareful with repeating mantras without reflection. You may end up using them as arguments when they're just catchphrases that require context. See people who used "goto considered harmful" as an absolute to never use goto (C).
- hnuaer 4y ago
- branko_d 4y ago“We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.”
- kcartlidge 4y ago
- KronisLV 4y agoI've often seen a pattern where the database schema devolves into a "classifier" system, although perhaps a combination of EAV (entity attribute value) and OTLT (one true lookup table) would be a more apt descriptor. Basically, you have tables a bit like these: - classifier_groups (e.g. product_classifiers, basket_classifiers) - classifiers (e.g. product_types, discount_types, delivery_methods, payment_methods) - classifier_values (e.g. regular_product, bid_product, timed_discount, loyalty_discount, postal_delivery, courier_delivery, credit_card, paypal) - classifier_value_attributes (e.g. product_name, product_weight, product_size, bid_start_date, bid_start_value, discount_start_date, discount_percentage, ...) - classifier_value_attribute_values (e.g. "Some product name", "0.25 kg", "2022.08.10 09:00", 5 EUR) Though sometimes the approach folds in on itself and the attributes are classifiers/classifier_values themselves, or JSON is used, or more tables inbetween are used when you need to track where a particular usage is needed (e.g. when you define a template and then have an item that is based on the template but with changed data). So the end result is that instead of 200 tables you have just a few instead, but the problem is that you can't really figure out what is happening with just foreign keys (especially when you are trying to do the reverse - refer from these classifiers to another table, since clearly you can't have a foreign key there and instead you do the EAV thing, like "link_type"/"link_table_name" and "referenced_id") and oftentimes you can't really use the DB because the enums and other data that you need to be able to write queries is actually stored in the application code. Regardless of what you call it, the thoughts on the approach, or at least its components seem split, even back in the day: 1) https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-bi... 2) (PDF) https://novicksoftware.com/wp-content/uploads/2016/09/Entity-Attribute-Value-EAV-The-Antipattern-Too-Great-to-Give-Up-Andy-Novick-2016-03-19.pdf https://novicksoftware.com/wp-content/uploads/2016/09/Entity... 3) https://softwareengineering.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios https://softwareengineering.stackexchange.com/questions/9312... I've seen some people swear by it, though personally I really dislike it, since any DB schema visualization tool just turns up a black box and a dozen JOINs across these classifier tables will always be less clear than just referencing tables like products, bid_products, timed_discounts, loyalty_discounts etc. So personally, I'd much prefer the 200 tables with foreign keys between them wherever necessary, with really dumb model bindings in the app instead of a bunch of enums, complex service logic and inevitable orphaned data. And yet, somehow people fear the idea of hundreds of table, but would prefer to shove this functionality into bunches of data instead. (disclaimer: domain is made up so perhaps not the best example, also a proper term for such a pattern escapes my mind at the moment)
- itwy 4y agoHow does this really work if a developer wants to pull most recent discussions posted in in X category, having most number of replies when I only have to tables?
- grandpoobah 4y agosomething like this? SELECT TOP 50 * FROM [things] [post] JOIN [things] [category] ON [category].[id] = [post].[parent_id] WHERE [post].[type] = 'post' AND [category].[type] = 'category' AND [category].[slug] = 'animal-pictures' ORDER BY ([post].[child_count] / DATEDIFF('hh', [post].[created_date], GETUTCDATE()) DESC;
- kgbcia 4y agomakes sense. rather than have an ever expanding user table with multiple columns, save attributes as rows. example. thing table, user id 1, 2, 3: data table user id 1, attribute name, value mike user id 1, attribute address, value Chicago.