4 ms·

Some of the points mentioned are sensible, but others are horrible (shift joins to the application) or simply nonsensical, like this: > If suboptimal methods (

Some of the points mentioned are sensible, but others are horrible (shift joins to the application) or simply nonsensical, like this: > If suboptimal methods (Nested Loop, Hash Join, Merge Join, etc.) are chosen for joins, this can lead to unnecessarily high resource consumption. Pray tell, what join strategies are left to use? Also, none of those are suboptimal, given the right schema and settings. Nested loop is fine, provided one side of the join is small. What isn’t mentioned (perhaps in a future post it will be) is how table and column statistics can affect performance. That’s how you get nested loops when a merge join would actually be more performant.

OJFord 3y ago
Maybe it means (trying to read charitably) that those are the methods, and if a suboptimal one of them (for a given query, schema, etc.) is chosen, then ... Or it's just a continuation of the first point: any non-application join is suboptimal. (Erm..) Edit: in fact, re-reading TFA, I think it is the latter or something like it - because the shift to application logic is mentioned as a 'countermeasure' for the 'suboptimal join methods'. I think it means deal with sometimes getting a bad plan by taking control and doing it yourself in the application. (I don't agree, just trying to understand.)
robertlagrant 3y ago
If you're joining in the application, why use a relational database?
- horse_dung 3y ago
  This is so true. If you just want the data then use a different store (e.g. NoSQL) and everything will be quicker, lighter weight and slower to develop…
  - paulddraper 3y ago
    What does "lighter weight" mean? Smaller install size? Smaller memory footprint? Schemaless?
- toolz 3y ago
  Just because you're joining in the application for one code path doesn't mean all of the code paths are doing the same thing. There are certainly valid use-cases for joining in the application because application layer can often times be horizontally scaled more cheaply/easily than the persistence layer. So in a scenario where you have plenty of network IO being under utilized for your database, it's not unimaginable where joining in the application layer would be preferable.
- pard68 3y ago
  FWIW the article never says to join at the app level. GP read wrong or something. The article says to shift logic to the application.
  - robertlagrant 3y ago
    Ah - fair enough. I knew I should've checked the article first before replying!
- OJFord 3y ago
  This may start to depend on exactly what you're willing to call relational, but because you still want the schema & foreign key constraints for example enforced in the database, you're just going to do the actual mechanics of joining the data in the application? join_users_and_widgets( run_sql('select * from users where account_id = :1', req.account_id), run_sql('select * from widgets'), ) for example, where the db still enforces that `widgets.owner_id` is a fk to `users.id` say. And `join_users_and_widgets` only has to know the current version of the schema, all rows have the same columns as it were.
paulddraper 3y ago
It just means PostgreSQL can choose the wrong one for a specific instance, not that those are universally wrong. "PostgreSQL chosing a suboptimal join methods (a 'join method' is Nested Join, Hash Join, Merge Join, etc., each of which is optimized for a specific situation) leads to unnecessarily high resource consumption. "[And unlike virtually every other DBMS, PostgreSQL doesn't support specifying which method is used for a particular join. So even if it happen relatively infrequently, it's very difficult to correct a situation where it does choose the wrong one.]"
- sgarland 3y ago
  > PostgreSQL doesn't support specifying which method is used for a particular join Not directly, no. You can make some of them possible / impossible depending on your indexing and server settings, as well as the query itself. For example, if the smaller side of the join can't fix into `work_mem` – which defaults to 4 MiB – you won't get a hash join. You can either create smaller tables, more restrictive join conditions, or increase the size of `work_mem` (but be careful not to blow up the DB, since that's per connection).
  - deleted 3y ago
    [deleted]
  - paulddraper 3y ago
    And you can use the enable_* sledgehammers as well.
matthewaveryusa 3y ago
Sometimes it's better to do things in the application instead of caking it in an sql query. An example would be a subquery that yields a large temp table, only to be pruned down to a single row. Your choice is to either have understanding of the query optimizer and formulate a query that forces the subquery with a smaller temp table first, or you can bring it back to the application. Either approach works, but in once case you need to have deep-ish understanding of the optimizer and SQL, and in the other not so much (at the cost of an RTT.)
- sgarland 3y ago
  > but in once case you need to have deep-ish understanding of the optimizer and SQL I will never understand why devs think they need to understand their chosen application well, but not SQL. Your application relies on SQL, so why shouldn't you deeply understand it? Relational algebra isn't that complex compared to other things we take for granted as necessary.
- whynotmaybe 3y ago
  Bringing more data to the app so that the app filters it instead of the DB server is always wrong. An app with such solution is a sign of mutliple issues that either the DB schema is wrong, or that the devs don't know enough Sql, or both. Sql has been there for decades, much less longer than many of the actual dev framework. PostgreSQL (and other DBMS) have also been there for decades, they have perfected the art of fetching data.
  - jon-wood 3y ago
    Nothing is ever "always wrong". It may be a sign that a little more thought can lead to a more maintainable/quick/efficient solution, but it may also be that under the circumstances an approach that would be the wrong one if applied widely actually is the correct tradeoff between those factors in this case.
jmull 3y ago
The article says "Shift logic to the application level to reduce database load." (Emphasis mine.) It may have been edited since you commented (perhaps because of your comment), but it seems like they aren't actually advocating to do joins at the application level.
- 7thaccount 3y ago
  I worked at a company with an extremely complex optimization application with more business rules than any human being could ever comprehend. The logic has to go somewhere. Either the database or the interface or the application itself I guess. There are pro/cons to all. Some of the logic is hard to follow in SQL compared to a general purpose language. The vendor for us of course did some kind of blend where a lot of SQL packages do certain validations and so on. The optimization problem reads a bunch of .csv files from the database and then has its own rules it uses to build the math problem for the mathematical solver.
- sgarland 3y ago
  I could’ve sworn it said joins, but I’m not positive. Tbf this is what Prisma does, albeit it joins at its own layer, then passes results back to the caller. Still horrifying.
dboreham 3y ago
Just to push against the crowd here pooh poohing client-side joins: this is a standard technique for scaling when you are stuck (for the time being) with a RDB and is definitely not in general "horrible". It does not mean pulling entire tables to the client then joining there. It means splitting a query that in effect walks down a chain of pointers (FKs) between tables into separate client-initiated queries. There are several benefits to this: 1. makes the design "nosql-ready" for when you design out the RDB. 2. Makes all DB queries inherently indexed and quick, allowing easy perf analysis (any non-quick query must be a bug) and alleviating connection pool starvation issues. 3. Prevents pesky devs from adding expensive queries under the radar. 4. allows more ready deployment of caching solutions. 5. recognizes that client resources scale with the number of clients, and are paid for by the client while RDB resources generally scale with difficulty and $$$, and are paid for by you.

OJFord 3y ago

Maybe it means (trying to read charitably) that those are the methods, and if a suboptimal one of them (for a given query, schema, etc.) is chosen, then ... Or it's just a continuation of the first point: any non-application join is suboptimal. (Erm..) Edit: in fact, re-reading TFA, I think it is the latter or something like it - because the shift to application logic is mentioned as a 'countermeasure' for the 'suboptimal join methods'. I think it means deal with sometimes getting a bad plan by taking control and doing it yourself in the application. (I don't agree, just trying to understand.)

robertlagrant 3y ago

If you're joining in the application, why use a relational database?

horse_dung 3y ago
This is so true. If you just want the data then use a different store (e.g. NoSQL) and everything will be quicker, lighter weight and slower to develop…
- paulddraper 3y ago
  What does "lighter weight" mean? Smaller install size? Smaller memory footprint? Schemaless?
toolz 3y ago
Just because you're joining in the application for one code path doesn't mean all of the code paths are doing the same thing. There are certainly valid use-cases for joining in the application because application layer can often times be horizontally scaled more cheaply/easily than the persistence layer. So in a scenario where you have plenty of network IO being under utilized for your database, it's not unimaginable where joining in the application layer would be preferable.
pard68 3y ago
FWIW the article never says to join at the app level. GP read wrong or something. The article says to shift logic to the application.
- robertlagrant 3y ago
  Ah - fair enough. I knew I should've checked the article first before replying!
OJFord 3y ago
This may start to depend on exactly what you're willing to call relational, but because you still want the schema & foreign key constraints for example enforced in the database, you're just going to do the actual mechanics of joining the data in the application? join_users_and_widgets( run_sql('select * from users where account_id = :1', req.account_id), run_sql('select * from widgets'), ) for example, where the db still enforces that `widgets.owner_id` is a fk to `users.id` say. And `join_users_and_widgets` only has to know the current version of the schema, all rows have the same columns as it were.

paulddraper 3y ago

It just means PostgreSQL can choose the wrong one for a specific instance, not that those are universally wrong. "PostgreSQL chosing a suboptimal join methods (a 'join method' is Nested Join, Hash Join, Merge Join, etc., each of which is optimized for a specific situation) leads to unnecessarily high resource consumption. "[And unlike virtually every other DBMS, PostgreSQL doesn't support specifying which method is used for a particular join. So even if it happen relatively infrequently, it's very difficult to correct a situation where it does choose the wrong one.]"

sgarland 3y ago
> PostgreSQL doesn't support specifying which method is used for a particular join Not directly, no. You can make some of them possible / impossible depending on your indexing and server settings, as well as the query itself. For example, if the smaller side of the join can't fix into `work_mem` – which defaults to 4 MiB – you won't get a hash join. You can either create smaller tables, more restrictive join conditions, or increase the size of `work_mem` (but be careful not to blow up the DB, since that's per connection).
- deleted 3y ago
  [deleted]
- paulddraper 3y ago
  And you can use the enable_* sledgehammers as well.

matthewaveryusa 3y ago

Sometimes it's better to do things in the application instead of caking it in an sql query. An example would be a subquery that yields a large temp table, only to be pruned down to a single row. Your choice is to either have understanding of the query optimizer and formulate a query that forces the subquery with a smaller temp table first, or you can bring it back to the application. Either approach works, but in once case you need to have deep-ish understanding of the optimizer and SQL, and in the other not so much (at the cost of an RTT.)

sgarland 3y ago
> but in once case you need to have deep-ish understanding of the optimizer and SQL I will never understand why devs think they need to understand their chosen application well, but not SQL. Your application relies on SQL, so why shouldn't you deeply understand it? Relational algebra isn't that complex compared to other things we take for granted as necessary.
whynotmaybe 3y ago
Bringing more data to the app so that the app filters it instead of the DB server is always wrong. An app with such solution is a sign of mutliple issues that either the DB schema is wrong, or that the devs don't know enough Sql, or both. Sql has been there for decades, much less longer than many of the actual dev framework. PostgreSQL (and other DBMS) have also been there for decades, they have perfected the art of fetching data.
- jon-wood 3y ago
  Nothing is ever "always wrong". It may be a sign that a little more thought can lead to a more maintainable/quick/efficient solution, but it may also be that under the circumstances an approach that would be the wrong one if applied widely actually is the correct tradeoff between those factors in this case.

jmull 3y ago

The article says "Shift logic to the application level to reduce database load." (Emphasis mine.) It may have been edited since you commented (perhaps because of your comment), but it seems like they aren't actually advocating to do joins at the application level.

7thaccount 3y ago
I worked at a company with an extremely complex optimization application with more business rules than any human being could ever comprehend. The logic has to go somewhere. Either the database or the interface or the application itself I guess. There are pro/cons to all. Some of the logic is hard to follow in SQL compared to a general purpose language. The vendor for us of course did some kind of blend where a lot of SQL packages do certain validations and so on. The optimization problem reads a bunch of .csv files from the database and then has its own rules it uses to build the math problem for the mathematical solver.
sgarland 3y ago
I could’ve sworn it said joins, but I’m not positive. Tbf this is what Prisma does, albeit it joins at its own layer, then passes results back to the caller. Still horrifying.

dboreham 3y ago

Just to push against the crowd here pooh poohing client-side joins: this is a standard technique for scaling when you are stuck (for the time being) with a RDB and is definitely not in general "horrible". It does not mean pulling entire tables to the client then joining there. It means splitting a query that in effect walks down a chain of pointers (FKs) between tables into separate client-initiated queries. There are several benefits to this: 1. makes the design "nosql-ready" for when you design out the RDB. 2. Makes all DB queries inherently indexed and quick, allowing easy perf analysis (any non-quick query must be a bug) and alleviating connection pool starvation issues. 3. Prevents pesky devs from adding expensive queries under the radar. 4. allows more ready deployment of caching solutions. 5. recognizes that client resources scale with the number of clients, and are paid for by the client while RDB resources generally scale with difficulty and $$$, and are paid for by you.