7 ms·

I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend). The “backend” scales much easier than

I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend). The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance. If you think, your joins must happen in db, because data too big to be loaded to memory on backend, restructure it, so it’s possible. Bonus points for moving joins to the frontend. This makes data highly cacheable - fast to load, as you need to load less data and frees up resources on server side.

AdrianB1 1y ago
My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.
- mdavid626 1y ago
  In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.
  - AdrianB1 1y ago
    The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
    - mdavid626 1y ago
      This is and “old-school” design. Nowadays I wouldn’t let apps meet in the database. Simple service oriented architecture is much preferred. Each app with its own data. Then such problems can be easily avoided.
      - dakiol 1y ago
        It’s not old school, it’s actually solid design. I have worked too with people that think the frontend or even services should guide the design/architecture of the whole thing. Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. Having Data structures (and mainly this means database structures) stable is key to long term maintenance.
        radlad 1y ago
        > Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. This appears as an opinion rather than an argument. Could you explain what you find bad about the design? In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements.
        dakiol 1y ago
        It's an opinion based on countless of references and books out there. I cannot cite them, but it's like "code should be designed to depend on abstract interfaces instead of a concrete implementation", "everything is a byte stream", "adding more people to a late project makes it later", "Bad programmers worry about the code. Good programmers worry about data structures and their relationships", "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious.", etc... they are usually true.
        RaftPeople 1y ago
        > In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements. I think the idea of breaking up a shared enterprise DB into many distinct but communicating and dependent DB's was driven by a desire to reduce team+system dependencies to increase ability to change. While the pro is valid and we make use of the idea sometimes when we design things, the cons are significant. Splitting up a DB that has data that is naturally shared by many departments in the business and by many modules/functional areas of the system increases complexity substantially. In the shared model, when some critical attribute of an item (sku) is updated, then all of the different modules+functional areas of enterprise are immediately using that current and correct master value. In the distributed model, there is significant complexity and effort to share this state across all areas. I've worked on systems designed this way and this issue frequently causes problems related to timing. As with everything, no single solution is best for all situations. We only split this kind of shared state when the pros outweigh the cons, which is sometimes but not that often.
- mdavid626 1y ago
  Good, simple solution could be data duplication, eg. store some props from the joined tables directly in the main table. I know, for many, this is one of the deadly sins, but I think it can work out very well.
riv991 1y ago
High Scale is so subjective here, I'd hazard a guess that 99% of businesses are not at the scale where they need to worry about scaling larger than a single Postgres or MySQL instance can handle.
- dondraper36 1y ago
  Vertical scaling is criminally underrated, unfortunately. Maybe, it's because horizontal scaling looks so much better on Linkedin.
  - mdavid626 1y ago
    Sooner or later even small apps reach hardware limits. My proposed design doesn’t bring many hard disadvantages. But it allows you to avoid vertical hardware scaling. Saves money and development time.
    - dondraper36 1y ago
      Not really disagreeing with you here, but that "later" never comes for most companies.
- Tade0 1y ago
  In the case of one project I've been in, the issue was the ORM creating queries, which Postgres deemed too large to do in-memory, so it fell back to performing them on-disk. Interestingly it didn't even use JOIN everywhere it could because, according to the documentation, not all databases had the necessary features. A hard lesson in the caveats of outsourcing work to ORMs.
  - richardlblair 1y ago
    I've worked both with ORMs and without. As a general rule, if the ORM is telling you there is something wrong with your query / tables it is probably right. The only time I've seen this is my career was a project that was an absolute pile of waste. The "CTO" was self taught, all the tables were far too wide with a ton of null values. The company did very well financially, but the tech was so damn terrible. It was such a liability.
sgarland 1y ago
Unless all your tables have the same width - or you’re doing weird things with constants in your SELECTs - you can’t UNION the various queries, so they’re sequential. You could parallelize those I suppose, but now you’re adding more complexity. If you want a KV store, use a KV store. If you want an RDBMS, then use its features. They haven’t changed much in the last 50 years for a reason.

AdrianB1 1y ago

My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.

mdavid626 1y ago
In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.
- AdrianB1 1y ago
  The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding more data and more apps. Think SAP as another such example.
  - mdavid626 1y ago
    This is and “old-school” design. Nowadays I wouldn’t let apps meet in the database. Simple service oriented architecture is much preferred. Each app with its own data. Then such problems can be easily avoided.
    - dakiol 1y ago
      It’s not old school, it’s actually solid design. I have worked too with people that think the frontend or even services should guide the design/architecture of the whole thing. Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. Having Data structures (and mainly this means database structures) stable is key to long term maintenance.
      - radlad 1y ago
        > Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. This appears as an opinion rather than an argument. Could you explain what you find bad about the design? In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements.
        dakiol 1y ago
        It's an opinion based on countless of references and books out there. I cannot cite them, but it's like "code should be designed to depend on abstract interfaces instead of a concrete implementation", "everything is a byte stream", "adding more people to a late project makes it later", "Bad programmers worry about the code. Good programmers worry about data structures and their relationships", "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious.", etc... they are usually true.
        RaftPeople 1y ago
        > In any case, I believe a DB per backend service isn't a decision driven by the frontend - rather, it's driven by data migration and data access requirements. I think the idea of breaking up a shared enterprise DB into many distinct but communicating and dependent DB's was driven by a desire to reduce team+system dependencies to increase ability to change. While the pro is valid and we make use of the idea sometimes when we design things, the cons are significant. Splitting up a DB that has data that is naturally shared by many departments in the business and by many modules/functional areas of the system increases complexity substantially. In the shared model, when some critical attribute of an item (sku) is updated, then all of the different modules+functional areas of enterprise are immediately using that current and correct master value. In the distributed model, there is significant complexity and effort to share this state across all areas. I've worked on systems designed this way and this issue frequently causes problems related to timing. As with everything, no single solution is best for all situations. We only split this kind of shared state when the pros outweigh the cons, which is sometimes but not that often.
mdavid626 1y ago
Good, simple solution could be data duplication, eg. store some props from the joined tables directly in the main table. I know, for many, this is one of the deadly sins, but I think it can work out very well.

riv991 1y ago

High Scale is so subjective here, I'd hazard a guess that 99% of businesses are not at the scale where they need to worry about scaling larger than a single Postgres or MySQL instance can handle.

dondraper36 1y ago
Vertical scaling is criminally underrated, unfortunately. Maybe, it's because horizontal scaling looks so much better on Linkedin.
- mdavid626 1y ago
  Sooner or later even small apps reach hardware limits. My proposed design doesn’t bring many hard disadvantages. But it allows you to avoid vertical hardware scaling. Saves money and development time.
  - dondraper36 1y ago
    Not really disagreeing with you here, but that "later" never comes for most companies.
Tade0 1y ago
In the case of one project I've been in, the issue was the ORM creating queries, which Postgres deemed too large to do in-memory, so it fell back to performing them on-disk. Interestingly it didn't even use JOIN everywhere it could because, according to the documentation, not all databases had the necessary features. A hard lesson in the caveats of outsourcing work to ORMs.
- richardlblair 1y ago
  I've worked both with ORMs and without. As a general rule, if the ORM is telling you there is something wrong with your query / tables it is probably right. The only time I've seen this is my career was a project that was an absolute pile of waste. The "CTO" was self taught, all the tables were far too wide with a ton of null values. The company did very well financially, but the tech was so damn terrible. It was such a liability.

sgarland 1y ago

Unless all your tables have the same width - or you’re doing weird things with constants in your SELECTs - you can’t UNION the various queries, so they’re sequential. You could parallelize those I suppose, but now you’re adding more complexity. If you want a KV store, use a KV store. If you want an RDBMS, then use its features. They haven’t changed much in the last 50 years for a reason.