5 ms·

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 e

by 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.
      - dman 6y ago
        Correct!
yelloweyes 6y ago
you either need a relational model or you don't. if you try to build a complex enterprise application with nosql all that's gonna happen is you end up reinventing an RDBMS there are not many companies in the world that can afford to reinvent shit
- m0xte 6y ago
  I entirely disagree with the first point on the basis that most enterprise applications are CRUD with a poorly contrived search interface. Both of which are somewhat better serviced by anything other than SQL. The second one however is on the mark.
petergeoghegan 6y ago
> It’s not a myth. In the trite examples provided with tiny datasets they scale. The point of the article is that in general nested loop joins based on index scans/iteration scale in the sense that the amount of I/O incurred grows at logarithmic intervals, at least once you assume a fixed amount of rows are returned. The amount of I/O incurred is practically fixed past a certain point. It doesn't matter how much data you have! It may be that there are practical difficulties with ensuring that everything runs smoothly in the real world (e.g., availability of good statistics), but that doesn't make the author's point any less valid.
matwood 6y ago
> It’s not a myth. It depends really. My approach for the last 20+ years is first build a proper database and data model and go from there. It's akin to only when you know the rules, should you break the rules. In the late 90s the db I was using didn't have 'materialized views' so we built a system that ran nightly and generated them for highly used sets of data (effectively removing joins b/c they hadn't scaled). Some systems had a nice 3rd normal tx database for the main program, but was dumped into a star/snowflake schema for reporting. Others worked fine coming right out the tx system. As computers have gotten faster, I find the original database can often go much farther. But, when it finally breaks down we now have so many more tools to help handle the situation. Admittedly, I've never dealt with data at FB or Google scale, but honestly that's a completely different set of constraints than what most of the world deals with.

by m0xte 6y ago

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.
    - dman 6y ago
      Correct!

yelloweyes 6y ago

you either need a relational model or you don't. if you try to build a complex enterprise application with nosql all that's gonna happen is you end up reinventing an RDBMS there are not many companies in the world that can afford to reinvent shit

m0xte 6y ago
I entirely disagree with the first point on the basis that most enterprise applications are CRUD with a poorly contrived search interface. Both of which are somewhat better serviced by anything other than SQL. The second one however is on the mark.

petergeoghegan 6y ago

> It’s not a myth. In the trite examples provided with tiny datasets they scale. The point of the article is that in general nested loop joins based on index scans/iteration scale in the sense that the amount of I/O incurred grows at logarithmic intervals, at least once you assume a fixed amount of rows are returned. The amount of I/O incurred is practically fixed past a certain point. It doesn't matter how much data you have! It may be that there are practical difficulties with ensuring that everything runs smoothly in the real world (e.g., availability of good statistics), but that doesn't make the author's point any less valid.

matwood 6y ago

> It’s not a myth. It depends really. My approach for the last 20+ years is first build a proper database and data model and go from there. It's akin to only when you know the rules, should you break the rules. In the late 90s the db I was using didn't have 'materialized views' so we built a system that ran nightly and generated them for highly used sets of data (effectively removing joins b/c they hadn't scaled). Some systems had a nice 3rd normal tx database for the main program, but was dumped into a star/snowflake schema for reporting. Others worked fine coming right out the tx system. As computers have gotten faster, I find the original database can often go much farther. But, when it finally breaks down we now have so many more tools to help handle the situation. Admittedly, I've never dealt with data at FB or Google scale, but honestly that's a completely different set of constraints than what most of the world deals with.