9 ms·

The myth of “joins don't scale”

Sohcahtoa82 6y ago
This page is ridiculous. It downloads 89 separate JS files and then breaks scrolling with my scroll wheel on Firefox. This is why most non-technical users like AMP.
- dfee 6y ago
  This is not reading material for non-technical users. It’s also highly irrelevant to the point that’s made (or discussion about the validity of the claims).
- y2bd 6y ago
  This kind of comment is now against the guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html (third from bottom).
  - twirlock 6y ago
    The guidelines are stupid. This website is another casualty of spooks atroturfing the internet. At least you're so pathetically officious about it that we get a laugh.
  - baddox 6y ago
    That's the first time I've seen that guideline. It seems like the "too common to be interesting" principle would apply to an enormous amount of comments here.
  - wolfgang42 6y ago
    For those of us who haven’t re-read the rules in the last couple weeks: > Please don't complain about website formatting, back-button breakage, and similar annoyances. They're too common to be interesting. Exception: when the author is present. Then friendly feedback might be helpful. (Added 11 days ago: https://news.ycombinator.com/item?id=23664008 https://news.ycombinator.com/item?id=23664008)
m0xte 6y ago
It’s not a myth. In the trite examples provided with tiny datasets they scale. In stark reality when you’ve got 5000+ qps of dog shit queries of several joins each and buggered table statistics (thanks optimiser!) coming in on realistic datasets they are a damage amplifier. Really all they do is repeat work in the most costly way possible that should have been done up front once. But that incurs the cost of knowing what you’re looking for before you do it.
- zozbot234 6y ago
  If you want to avoid repeating work, you can set up a materialized view. But really, quite often a few tailored indexes are enough.
- dman 6y ago
  People do not realize just how far industry practitioners take joins :) So my take is that users will find a way to take joins to the point where they do not scale.
  - m0xte 6y ago
    Usually they scale infinitely well until someone opens a PO for a Cray sized database cluster that burns kilowatts of power.
    - dman 6y ago
      Let me just say that I have seen queries that are megabytes in size with joins all over the place :)
      - m0xte 6y ago
        I’ve seen the same ones that do 3GB of reads to return ten rows. The point I was inelegantly making is that it is terrible doing this processing on every read rather than once at write.
        6y ago
crazygringo 6y ago
Unfortunately, the author/article seems to completely miss the meaning of "joins don't scale". Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS! The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance. Joins scale across rows, they don't scale across servers. Now a lot of people don't realize how insanely powerful single-server DB's can be. A lot of people that assume they need to architect for a multi-server DB don't realize they can get away with a hugely-provisioned SSD single-server indexed database with a backup, with performant queries. But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.
- redisman 6y ago
  > But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue. Even then you probably get a few years of use out of that simple setup while you work on migrating to a horizontal solution. And by then more likely than hitting Twitter-scale, your company has gone bankrupt.
  - csande17 6y ago
    And while we're making pie-in-the-sky long-term plans, the amount of RAM you can shove into a single server chassis is growing faster than the world population. So you might as well use joins -- the people maintaining your code in the year 2999 will thank you!
- yelloweyes 6y ago
  reality is 99% of companies out there will be just fine with a single db server all this nosql fad is simply because SQL is hard and newbies would just rather call it old and slow because they can't be bothered to learn it
DaiPlusPlus 6y ago
I get the impression that (crucially: outside of Facebook/Google-scale applications) the strongest advocates for No-SQL databases are great software devs who just had a poor experience with SQL databases as juniors or students - or didn’t develop have the necessary experience and skills to make the most of a SQL RDBMS because it was out of their field (e.g. game devs); they’d be missing things like not understanding relational-algebra or relational-calculus - or not even knowing SQL at all - or having the DBA skills to know how to define the best indexes - or even what a cardinality-estimate in an execution plan is. If you throw all of those topics at someone and also add that they only have a week to build the thing - it’s no-wonder that people turn to No-SQL. I’m not putting those people down either - I would have opted for No-SQL myself if I wasn’t already familiar with RDBS theory and management. What I am criticising is the large amount of manual-management needed when you do run an RDBMS because this is putting people off. Instead, things like automatic index generation and pruning, nightly index rebuilds and statistics computing, etc arguably SHOULD be part of the RDBS out-of-the-box and enabled by default. To my knowledge only IaaS databases have features like that (it’s what justifies their subscription pricing). I believe some on-prem database server products will generate recommendations but they won’t automatically implement them on their own accord. Another “approachability” issue I have is with the design of the SQL language: it’s unnecessarily verbose and very confusing for beginners as its basis in relational-calculus instead of relational-algebra throws people off (“SELECT is evaluated after the WHERE clause?!”). If SQL is like SGML and XML, which was replaced in many applications by the faster and lighter JSON format, can we do the same for SQL? Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway. In summary: 1. RDBMS servers should have an “automatic DBA”-mode enabled by default (because if it’s not the default then the exact people who need it (unsophisticated DB devs) won’t be using it, and the people who don’t want it will just turn it off anyway. 2. SQL needs to be replaced with something better: less verbose but just as expressive, and with all of SQL’s “gotchas” removed (like adding multiplicity assertions to joins, implicit joins when dereferencing a foreign-key, implicit locks, allowing comparisons to NULL, and leaning more towards relational-algebra instead of relational-calculus, and support for column macros: this is desperately needed for CRUD applications!) 3. Thank you for coming to my TED Talk.
htfy96 6y ago
This article only tested two pages, each with millions of data. In corporate developments (and probably the original meaning of "joins don't scale" in the era of Oracle/MSSQL), it's usually the opposite - usually joining half dozen of tables, each with a few data, but their mappings and underlying relationship are usually not explicitly-defined, making joins across multiple tables super slow (RDBMS can only plan the query based on fundamental stats). In other words, joins now scale with table size, but still a challenge in terms of #tables
- chadcmulligan 6y ago
  > but their mappings and underlying relationship are usually not explicitly-defined wouldn't this be an issue no matter what you're using? I'm not sure what you mean by joins scale with table size? There are few times you'd join more than say 5 or 6 tables.
  - htfy96 6y ago
    > There are few times you'd join more than say 5 or 6 tables. This is only true in new tech where data relationship isn't complicated. Any stored procedure in bank would contain joins with no fewer than that number of tables.
    - chadcmulligan 6y ago
      Really? Twenty years as a database consultant and 5 or 6 tables would be a typical upper limit, you could perhaps get up to 10 or so if you include a lot of lookup tables for codes. I've never worked in banks though, so what would you be looking at there typically?
      - Akronymus 6y ago
        I work at a leasing company, and we somewhat frequently have around 5-7 joins when getting data such as where to send how much money for work done at another company. The most I saw was "only" 10 though.
- 6y ago
sradman 6y ago
It is 2020. If you comparing the performance of PostgreSQL and DynamoDB in terms of GROUP BY analytics, you have probably missed the Column Store revolution. Now if these two authors actually focused on the trade-offs between these two architectures in terms of their sweet-spot, operational row stores, we might gain some insight.

This page is ridiculous. It downloads 89 separate JS files and then breaks scrolling with my scroll wheel on Firefox. This is why most non-technical users like AMP.

dfee 6y ago
This is not reading material for non-technical users. It’s also highly irrelevant to the point that’s made (or discussion about the validity of the claims).
y2bd 6y ago
This kind of comment is now against the guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html (third from bottom).
- twirlock 6y ago
  The guidelines are stupid. This website is another casualty of spooks atroturfing the internet. At least you're so pathetically officious about it that we get a laugh.
- baddox 6y ago
  That's the first time I've seen that guideline. It seems like the "too common to be interesting" principle would apply to an enormous amount of comments here.
- wolfgang42 6y ago
  For those of us who haven’t re-read the rules in the last couple weeks: > Please don't complain about website formatting, back-button breakage, and similar annoyances. They're too common to be interesting. Exception: when the author is present. Then friendly feedback might be helpful. (Added 11 days ago: https://news.ycombinator.com/item?id=23664008 https://news.ycombinator.com/item?id=23664008)

m0xte 6y ago

It’s not a myth. In the trite examples provided with tiny datasets they scale. In stark reality when you’ve got 5000+ qps of dog shit queries of several joins each and buggered table statistics (thanks optimiser!) coming in on realistic datasets they are a damage amplifier. Really all they do is repeat work in the most costly way possible that should have been done up front once. But that incurs the cost of knowing what you’re looking for before you do it.

zozbot234 6y ago
If you want to avoid repeating work, you can set up a materialized view. But really, quite often a few tailored indexes are enough.
dman 6y ago
People do not realize just how far industry practitioners take joins :) So my take is that users will find a way to take joins to the point where they do not scale.
- m0xte 6y ago
  Usually they scale infinitely well until someone opens a PO for a Cray sized database cluster that burns kilowatts of power.
  - dman 6y ago
    Let me just say that I have seen queries that are megabytes in size with joins all over the place :)
    - m0xte 6y ago
      I’ve seen the same ones that do 3GB of reads to return ten rows. The point I was inelegantly making is that it is terrible doing this processing on every read rather than once at write.
      - 6y ago

crazygringo 6y ago

Unfortunately, the author/article seems to completely miss the meaning of "joins don't scale". Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS! The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance. Joins scale across rows, they don't scale across servers. Now a lot of people don't realize how insanely powerful single-server DB's can be. A lot of people that assume they need to architect for a multi-server DB don't realize they can get away with a hugely-provisioned SSD single-server indexed database with a backup, with performant queries. But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

redisman 6y ago
> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue. Even then you probably get a few years of use out of that simple setup while you work on migrating to a horizontal solution. And by then more likely than hitting Twitter-scale, your company has gone bankrupt.
- csande17 6y ago
  And while we're making pie-in-the-sky long-term plans, the amount of RAM you can shove into a single server chassis is growing faster than the world population. So you might as well use joins -- the people maintaining your code in the year 2999 will thank you!
yelloweyes 6y ago
reality is 99% of companies out there will be just fine with a single db server all this nosql fad is simply because SQL is hard and newbies would just rather call it old and slow because they can't be bothered to learn it

DaiPlusPlus 6y ago

I get the impression that (crucially: outside of Facebook/Google-scale applications) the strongest advocates for No-SQL databases are great software devs who just had a poor experience with SQL databases as juniors or students - or didn’t develop have the necessary experience and skills to make the most of a SQL RDBMS because it was out of their field (e.g. game devs); they’d be missing things like not understanding relational-algebra or relational-calculus - or not even knowing SQL at all - or having the DBA skills to know how to define the best indexes - or even what a cardinality-estimate in an execution plan is. If you throw all of those topics at someone and also add that they only have a week to build the thing - it’s no-wonder that people turn to No-SQL. I’m not putting those people down either - I would have opted for No-SQL myself if I wasn’t already familiar with RDBS theory and management. What I am criticising is the large amount of manual-management needed when you do run an RDBMS because this is putting people off. Instead, things like automatic index generation and pruning, nightly index rebuilds and statistics computing, etc arguably SHOULD be part of the RDBS out-of-the-box and enabled by default. To my knowledge only IaaS databases have features like that (it’s what justifies their subscription pricing). I believe some on-prem database server products will generate recommendations but they won’t automatically implement them on their own accord. Another “approachability” issue I have is with the design of the SQL language: it’s unnecessarily verbose and very confusing for beginners as its basis in relational-calculus instead of relational-algebra throws people off (“SELECT is evaluated after the WHERE clause?!”). If SQL is like SGML and XML, which was replaced in many applications by the faster and lighter JSON format, can we do the same for SQL? Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway. In summary: 1. RDBMS servers should have an “automatic DBA”-mode enabled by default (because if it’s not the default then the exact people who need it (unsophisticated DB devs) won’t be using it, and the people who don’t want it will just turn it off anyway. 2. SQL needs to be replaced with something better: less verbose but just as expressive, and with all of SQL’s “gotchas” removed (like adding multiplicity assertions to joins, implicit joins when dereferencing a foreign-key, implicit locks, allowing comparisons to NULL, and leaning more towards relational-algebra instead of relational-calculus, and support for column macros: this is desperately needed for CRUD applications!) 3. Thank you for coming to my TED Talk.

htfy96 6y ago

This article only tested two pages, each with millions of data. In corporate developments (and probably the original meaning of "joins don't scale" in the era of Oracle/MSSQL), it's usually the opposite - usually joining half dozen of tables, each with a few data, but their mappings and underlying relationship are usually not explicitly-defined, making joins across multiple tables super slow (RDBMS can only plan the query based on fundamental stats). In other words, joins now scale with table size, but still a challenge in terms of #tables

chadcmulligan 6y ago
> but their mappings and underlying relationship are usually not explicitly-defined wouldn't this be an issue no matter what you're using? I'm not sure what you mean by joins scale with table size? There are few times you'd join more than say 5 or 6 tables.
- htfy96 6y ago
  > There are few times you'd join more than say 5 or 6 tables. This is only true in new tech where data relationship isn't complicated. Any stored procedure in bank would contain joins with no fewer than that number of tables.
  - chadcmulligan 6y ago
    Really? Twenty years as a database consultant and 5 or 6 tables would be a typical upper limit, you could perhaps get up to 10 or so if you include a lot of lookup tables for codes. I've never worked in banks though, so what would you be looking at there typically?
    - Akronymus 6y ago
      I work at a leasing company, and we somewhat frequently have around 5-7 joins when getting data such as where to send how much money for work done at another company. The most I saw was "only" 10 though.
6y ago

sradman 6y ago

It is 2020. If you comparing the performance of PostgreSQL and DynamoDB in terms of GROUP BY analytics, you have probably missed the Column Store revolution. Now if these two authors actually focused on the trade-offs between these two architectures in terms of their sweet-spot, operational row stores, we might gain some insight.