5 ms·

People hate joins because at some point they get in the way of scaling, and getting past that is a huge pain. Or at least, that's where the original join-hate

People hate joins because at some point they get in the way of scaling, and getting past that is a huge pain. Or at least, that's where the original join-hate comes from. In reality of course, most of us don't have that problem, never had and never will, and it's just being parroted as an excuse for not bothering to understand RDMS's. Relational database design is a highly undervalued skill outside the enterprise IT world. Many of the best programmers I've worked with couldn't design a proper database if their lives depended on it.

rosser 13y ago
People hate joins because at some point they get in the way of scaling... No, in fact, they don't. Poor relational modeling gets in the way of scaling, and that can be geometrically exacerbated by JOINs. A JOIN, in and of itself, is neither good nor bad. It's just a tool, and like all tools, how you use it is what makes it "good" or "bad" — just like you can build a house or bash in a skull with a hammer.
- parasubvert 13y ago
  In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site. A time series data warehouse could go into the billions of rows with scalable joins with partitioning and bitmap indices ... but is also only applicable in the unlikely case you could afford oracle at $60-90k/CPU list price Also, most databases that aren't Oracle don't have high performance materialized views to "preprocess" joins at upsert time, therefore people resort to demoralized tables and their own custom approach to materializing those views. Then even denormalized tables begin to stop scaling at around 250 million to 500 million rows. So people resort to sharding managed in a custom way. I haven't even begun to express the scalability impacts of millions of users on a LRU buffer cache used in most RDBMS - that usually is resolved through an in-memory cache (Memcached, Redis) whose coherency is also managed in a custom manner. Or you could spend $$$ for Coherence, Gigaspaces, Gemfire, etc. but that's also unlikely in most web companies. At the end of all this, even if you bought a cache, you wonder why you're using an RDBMS at all since you're so constrained in your administrative approaches. Cue NoSQL. of course in practice many devs ignore all of this history and "design by resume" assuming their new social-mobile-chat-photo-conbobulator will be at Facebook scale tomorrow.
  - olavgg 13y ago
    This is not true at all. I've worked on several databases with billions of rows in several tables. A good solution for improving your query performance is to use a multi column index http://www.postgresql.org/docs/9.3/static/indexes-multicolumn.html http://www.postgresql.org/docs/9.3/static/indexes-multicolum...
    - parasubvert 13y ago
      What part isn't true? I'll restate my narrative: Single instance, normalized, unpartitioned databases run into scaling problems the several-hundred million row range especially when under heavy concurrent load. But once you start moving to multi-instance, partitioned databases, you start to lose the benefits of the relational model as most databases have to restrict how you accomplish things -- e.g. joins are severely restricted.
  - batbomb 13y ago
    I routinely deal with joins on a 100 million row table and they work just fine. Other than that, I also use a 10 billion row table for searches. This is in Oracle.
  - frederickf 13y ago
    "demoralized tables" :)
  - darkmoth 13y ago
    Oracle will handle anything you throw at it, assuming you have the $$$. Ebay uses it for 2 Petabytes of data: http://www.dba-oracle.com/oracle_news/news_ebay_massive_oracle.htm http://www.dba-oracle.com/oracle_news/news_ebay_massive_orac...
    - parasubvert 13y ago
      That's a link discussing an analytic database from SEVEN YEARS ago. eBay has moved on. Please understand what I am saying: - Traditional database architectures have limitations on what you can express in SQL for highly available and scalable online transaction processing once you introduce partitioning and clustering. - Oracle has probably the best support for partitioning and clustering out of all RDBMS, but even that has limits in the billions of rows - Many companies do not use Oracle for business reasons (licensing/sales/pricing practices) What I am not saying: - Oracle sucks (it's the most feature complete and robust RDBMS out there and is ); - Oracle is not used (Amazon, Yahoo, eBay, etc. all use Oracle in various contexts); - Oracle does not scale (it does, though it requires you, the SQL developer, to intimately know the database physical design at a certain point of scale, which defeats much of why SQL exists to begin with)
drsintoma 13y ago
> In reality of course, most of us don't have that problem, never had and never will Maybe you never had any problems, but I don't believe "most of us" can say the same. At least me, I'd encountered problems derived from join-abuse in almost every job I've had.
- rodgerd 13y ago
  That's funny, because I've mostly encountered problems with people who prefer to nest SQL queries inside a sucession of loops in their code, rather than learn how to use SQL properly.
- emn13 13y ago
  Yeah, me too. But having said that, I've also seen problems with mongodb and they're much, much, much, much harder to solve.
tracker1 13y ago
For me, it was a database schema with 38 joins (and 2 additional queries) to effectively get the data to display a single page. For that use case, mirroring the data on save to MongoDB was a no-brainer... with geospacial queries out of the box, and a few other indexing features it made a lot of sense. I wouldn't even think to use MongoDB for certain use cases... but for others, it's a great fit. I think that Cassandra, Riak, Couch, Redis and RethinkDB all have serious advantages and disadvantages to eachother and SQL. I do find that MongoDB is a very natural fit for node.js development, but am not shy about using the right tool for a job. Another thing that tends to irk me, is when people use SQL in place of an MQ server.
- davidp 13y ago
  > For that use case, mirroring the data on save to MongoDB was a no-brainer I think you just confirmed the OP's point -- MongoDB makes a good cache, not a good primary store. I'm guessing you didn't do updates into that MongoDB store, and always treated the SQL source as "authoritative" when it became necessary. Am I right?
  - tracker1 13y ago
    I no longer work at the company in question, but the plan was to displace SQL for the records that were being used in MongoDB, for mongo to become the authority. NOTE: this was for a classified ads site for cars. Financial and account transactions and data would remain in the dbms, but vehicle/listing records would have become mongo authoratative. The transition was difficult because of the sheer number of systems that imported/updated listing records in the database... there wasn't yet a 100% certainty that all records were tagged on update properly, so that they could be re-exported to mongo... each day, all records were tagged ... took about 24 minutes to replicated the active listings (about 50K records), and we're not talking "Big Data" here, but performance was much better doing search/display queries against MongoDB.

rosser 13y ago

People hate joins because at some point they get in the way of scaling... No, in fact, they don't. Poor relational modeling gets in the way of scaling, and that can be geometrically exacerbated by JOINs. A JOIN, in and of itself, is neither good nor bad. It's just a tool, and like all tools, how you use it is what makes it "good" or "bad" — just like you can build a house or bash in a skull with a hammer.

parasubvert 13y ago
In most relational database implementations, joins stop scaling after 10-50 million rows or so assuming an online transactional site. A time series data warehouse could go into the billions of rows with scalable joins with partitioning and bitmap indices ... but is also only applicable in the unlikely case you could afford oracle at $60-90k/CPU list price Also, most databases that aren't Oracle don't have high performance materialized views to "preprocess" joins at upsert time, therefore people resort to demoralized tables and their own custom approach to materializing those views. Then even denormalized tables begin to stop scaling at around 250 million to 500 million rows. So people resort to sharding managed in a custom way. I haven't even begun to express the scalability impacts of millions of users on a LRU buffer cache used in most RDBMS - that usually is resolved through an in-memory cache (Memcached, Redis) whose coherency is also managed in a custom manner. Or you could spend $$$ for Coherence, Gigaspaces, Gemfire, etc. but that's also unlikely in most web companies. At the end of all this, even if you bought a cache, you wonder why you're using an RDBMS at all since you're so constrained in your administrative approaches. Cue NoSQL. of course in practice many devs ignore all of this history and "design by resume" assuming their new social-mobile-chat-photo-conbobulator will be at Facebook scale tomorrow.
- olavgg 13y ago
  This is not true at all. I've worked on several databases with billions of rows in several tables. A good solution for improving your query performance is to use a multi column index http://www.postgresql.org/docs/9.3/static/indexes-multicolumn.html http://www.postgresql.org/docs/9.3/static/indexes-multicolum...
  - parasubvert 13y ago
    What part isn't true? I'll restate my narrative: Single instance, normalized, unpartitioned databases run into scaling problems the several-hundred million row range especially when under heavy concurrent load. But once you start moving to multi-instance, partitioned databases, you start to lose the benefits of the relational model as most databases have to restrict how you accomplish things -- e.g. joins are severely restricted.
- batbomb 13y ago
  I routinely deal with joins on a 100 million row table and they work just fine. Other than that, I also use a 10 billion row table for searches. This is in Oracle.
- frederickf 13y ago
  "demoralized tables" :)
- darkmoth 13y ago
  Oracle will handle anything you throw at it, assuming you have the $$$. Ebay uses it for 2 Petabytes of data: http://www.dba-oracle.com/oracle_news/news_ebay_massive_oracle.htm http://www.dba-oracle.com/oracle_news/news_ebay_massive_orac...
  - parasubvert 13y ago
    That's a link discussing an analytic database from SEVEN YEARS ago. eBay has moved on. Please understand what I am saying: - Traditional database architectures have limitations on what you can express in SQL for highly available and scalable online transaction processing once you introduce partitioning and clustering. - Oracle has probably the best support for partitioning and clustering out of all RDBMS, but even that has limits in the billions of rows - Many companies do not use Oracle for business reasons (licensing/sales/pricing practices) What I am not saying: - Oracle sucks (it's the most feature complete and robust RDBMS out there and is ); - Oracle is not used (Amazon, Yahoo, eBay, etc. all use Oracle in various contexts); - Oracle does not scale (it does, though it requires you, the SQL developer, to intimately know the database physical design at a certain point of scale, which defeats much of why SQL exists to begin with)

drsintoma 13y ago

> In reality of course, most of us don't have that problem, never had and never will Maybe you never had any problems, but I don't believe "most of us" can say the same. At least me, I'd encountered problems derived from join-abuse in almost every job I've had.

rodgerd 13y ago
That's funny, because I've mostly encountered problems with people who prefer to nest SQL queries inside a sucession of loops in their code, rather than learn how to use SQL properly.
emn13 13y ago
Yeah, me too. But having said that, I've also seen problems with mongodb and they're much, much, much, much harder to solve.

tracker1 13y ago

For me, it was a database schema with 38 joins (and 2 additional queries) to effectively get the data to display a single page. For that use case, mirroring the data on save to MongoDB was a no-brainer... with geospacial queries out of the box, and a few other indexing features it made a lot of sense. I wouldn't even think to use MongoDB for certain use cases... but for others, it's a great fit. I think that Cassandra, Riak, Couch, Redis and RethinkDB all have serious advantages and disadvantages to eachother and SQL. I do find that MongoDB is a very natural fit for node.js development, but am not shy about using the right tool for a job. Another thing that tends to irk me, is when people use SQL in place of an MQ server.

davidp 13y ago
> For that use case, mirroring the data on save to MongoDB was a no-brainer I think you just confirmed the OP's point -- MongoDB makes a good cache, not a good primary store. I'm guessing you didn't do updates into that MongoDB store, and always treated the SQL source as "authoritative" when it became necessary. Am I right?
- tracker1 13y ago
  I no longer work at the company in question, but the plan was to displace SQL for the records that were being used in MongoDB, for mongo to become the authority. NOTE: this was for a classified ads site for cars. Financial and account transactions and data would remain in the dbms, but vehicle/listing records would have become mongo authoratative. The transition was difficult because of the sheer number of systems that imported/updated listing records in the database... there wasn't yet a 100% certainty that all records were tagged on update properly, so that they could be re-exported to mongo... each day, all records were tagged ... took about 24 minutes to replicated the active listings (about 50K records), and we're not talking "Big Data" here, but performance was much better doing search/display queries against MongoDB.