5 ms·

Agree. With patterns like this you are leaning on your db server’s CPU - among your most scarce resources - versus doing this work on the client on a relativel

Agree. With patterns like this you are leaning on your db server’s CPU - among your most scarce resources - versus doing this work on the client on a relatively cheap app server. At query time your app server knows if $2 or $3 or $4 is null and can elide those query args. Feels bad to use a fast language like Rust on your app servers and then your perf still sucks because your single DB server is asked to contemplate all possibilities on queries like this instead of doing such simple work on your plentiful and cheap app servers.

deleted 2y ago
[deleted]
pocketarc 2y ago
This is actually an incredible way of articulating something that's been on my mind for quite a while. Thank you for this, I will use this. The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.
- dinosaurdynasty 2y ago
  There's a different reason to lean on the DB: it's the final arbiter of your data. Much harder to create bad/poisoned data if the DB has a constraint on it (primary, foreign, check, etc) than if you have to remember it in your application (and unless you know what serializable transactions are, you are likely doing it wrong). Also you can't do indexes outside of the DB (well, you can try).
  - bruce511 2y ago
    Replying to this whole sub-thread, not just this post specifically; All SQL advice has to take _context_ into account. In SQL, perhaps more than anywhere else, context matters. There's lots of excellent SQL advice, but most of it is bound to a specific context, and in a different context it's bad advice. Take for example the parent comment above; In their context the CPU of the database server is their constraining resource. I'm guessing the database if "close" to the app servers (ie low network latency, high bandwidth), and I'm also guessing the app developers "own" the database. In this context moving CPU to the app server makes complete sense. Client-side validation of data makes sense because they are the only client. Of course if the context changes, then the advice has to change as well. If the network bandwidth to the server was constrained (cost, distance etc) then transporting the smallest amount of data becomes important. In this case it doesn't matter if the filter is more work for the server, the goal is the smallest result set. And so it goes. Write-heavy systems prefer fewer indexes. Read-heavy systems prefer lots of indexes. Databases where the data client is untrusted need more validation, relation integrity, access control - databases with a trusted client need less of that. In my career I've followed a lot of good SQL advice - advice that was good for my context. I've also broken a lot of SQL "rules" because those rules were not compatible, or were harmful, in my context. So my advice is this - understand your own context. Understand where you are constrained, and where you have plenty. And tailor your patterns around those parameters.
    - ki85squared 2y ago
      The most sensible and pragmatic advice in this thread.
- dagss 2y ago
  I think there are two different concerns here though: The article recommends something that may lead to using the wrong query plans. In the "right" conditions, you will do full table scans of all your data for every query. This is making the DB waste a lot of CPU (and IO). Wasting resources like that is different from just where to do work that has to be done anyway! I am a proponent of shifting logic toward the DB, because likely it ends up there anyway and usually you reduce the resource consumption also for the DB to have as much logic as possible in the DB. The extreme example is you want to sum(numbers) -- it is so much faster to sum it in one roundtrip to the DB, than to do a thousand roundtrips to the DB to fetch the numbers to sum them on the client. The latter is so much more effort also for the DB server's resources. My point is: Usually it is impossible to meaningfully shift CPU work to the client of the DB, because the client needs the data, so it will ask for it, and looking up the data is the most costly operation in the DB.
  - cogman10 2y ago
    The answer is "it depends". Sum is a good thing to do in the Db because it's low cost to the db and reduces io between the db and app. Sort can be (depending on indexes) a bad thing for a db because that's CPU time that needs to be burned. Conditional logic is also (often) terrible for the db because it can break the optimizer in weird ways and is just as easily performed outside the db. The right action to take is whatever optimizes db resources in the long run. That can sometimes mean shifting to the db, and sometimes it means shifting out of the db.
    - crazygringo 2y ago
      It's hard to think of situations where you don't want to do the sorting on the DB. If you're sorting small numbers of rows it's cheap enough that it doesn't matter, and if you're sorting large numbers of rows you should be using an index which makes it vastly more efficient than it could be in your app. And if your conditional logic is breaking the optimizer then the solution is usually to write the query more correctly. I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind?
- scarface_74 2y ago
  > The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. This is not received wisdom at all and the one edict I have when leading a project is no stored procedures for any OLTP functionality. Stored Procs make everything about your standard DevOps and SDLC process harder - branching, blue green deployments and rolling back deployments.
  - oftenwrong 2y ago
    >Stored Procs make everything about your standard DevOps and SDLC process harder - branching, blue green deployments and rolling back deployments. There is a naming/namespacing strategy incorporating a immutable version identifier that makes this easier, which I have described here: https://news.ycombinator.com/item?id=35648974 https://news.ycombinator.com/item?id=35648974 Note that this requires a strategy for cleaning up old procedures. It also is possible to individually hash each procedure, which is more sophisticated, and would allow for incremental creation of new procedures.
    - scarface_74 2y ago
      That’s actually an ingenious solution. I can’t find any flaws in it.
- ramchip 2y ago
  I think the conventional wisdom is to lean on the DB to maintain data integrity, using constraints, foreign keys, etc.; not to use it to run actual business logic.
- nycdotnet 2y ago
  This is Brent Ozar's old theme.
BeefWellington 2y ago
I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. This isn't what I think you're saying -- just piggybacking to try and explain further. The key thing here is to understand that you want the minimal correct query for what you need, not to avoid "making the database work". The given example is silly because there's additional parameters that must be either NULL or have a value before the query is sent to the DB. You shouldn't send queries like: SELECT \* FROM users WHERE id = 1234 AND (NULL IS NULL OR username = NULL) AND (NULL IS NULL OR age > NULL) AND (NULL IS NULL OR age < NULL) But you should absolutely send: SELECT \* FROM users WHERE id = 1234 AND age > 18 AND age < 35
- deredede 2y ago
  While sub-optimal, your first example is probably fine to send and I'd expect to be simplified early during query planning at a negligible cost to the database server. What you shouldn't send is queries like: SELECT \* FROM users WHERE ($1 IS NULL OR id = $1) AND ($2 IS NULL OR username = $2) AND ($3 IS NULL OR age > $3) AND ($4 IS NULL OR age < $4) because now the database (probably) doesn't know the value of the parameters during planning and needs to consider all possibilities.
  - oever 2y ago
    Interesting. I try to use prepared statements to avoid redoing the planning. But since the database schema is small compared to the data, the cost of query planning is quickly negligible compared to running an generic query that is inefficient.
- tsarchitect 2y ago
  One of my rules: don't send NULL over the wire, but of course always check for NULL on the server if you're using db functions.
kamma4434 2y ago
While this is no excuse for sending sloppy queries to the database server, my rule of thumb with databases - as I was told by my elders - is ”if it can be reasonably done in the database, it should be done by the database”. Data base engines are meant to be quite performant at what they do, possibly more than your own code.
- cogman10 2y ago
  Databases aren't magic. They have limited CPU and IO resources. They can only optimize within the bounds of the current table/index structure. And sometimes they make a bad optimization decision and need to be pushed to do the right thing. Databases can, for example, sort things. However, if that thing being sorted isn't covered by an index then you are better off doing it in the application where you have a CPU that can do the n log n sort. Short quips lead to lazy thinking. Learn what your database can and can't do fast and work with it. If something will be just as fast in the application as it would be in the database you should do it in the application. I've seen the end result of the "do everything in the database" thinking and it has created some of the worst performance bottlenecks in my company. You can do almost everything in the database. That doesn't mean you should.
  - blazing234 2y ago
    Better off doing it in the application? Bro im not pulling that much data to do a top N query
    - cogman10 2y ago
      > Short quips lead to lazy thinking. Learn what your database can and can't do fast and work with it.
  - kamma4434 2y ago
    That’s an optimization, and does not mean that the general rule is not valid. If that happens to be a bottleneck and you can do better, you should definitely do it in code locally. But these are two ifs that need to evaluate to true

deleted 2y ago

[deleted]

pocketarc 2y ago

This is actually an incredible way of articulating something that's been on my mind for quite a while. Thank you for this, I will use this. The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.

dinosaurdynasty 2y ago
There's a different reason to lean on the DB: it's the final arbiter of your data. Much harder to create bad/poisoned data if the DB has a constraint on it (primary, foreign, check, etc) than if you have to remember it in your application (and unless you know what serializable transactions are, you are likely doing it wrong). Also you can't do indexes outside of the DB (well, you can try).
- bruce511 2y ago
  Replying to this whole sub-thread, not just this post specifically; All SQL advice has to take _context_ into account. In SQL, perhaps more than anywhere else, context matters. There's lots of excellent SQL advice, but most of it is bound to a specific context, and in a different context it's bad advice. Take for example the parent comment above; In their context the CPU of the database server is their constraining resource. I'm guessing the database if "close" to the app servers (ie low network latency, high bandwidth), and I'm also guessing the app developers "own" the database. In this context moving CPU to the app server makes complete sense. Client-side validation of data makes sense because they are the only client. Of course if the context changes, then the advice has to change as well. If the network bandwidth to the server was constrained (cost, distance etc) then transporting the smallest amount of data becomes important. In this case it doesn't matter if the filter is more work for the server, the goal is the smallest result set. And so it goes. Write-heavy systems prefer fewer indexes. Read-heavy systems prefer lots of indexes. Databases where the data client is untrusted need more validation, relation integrity, access control - databases with a trusted client need less of that. In my career I've followed a lot of good SQL advice - advice that was good for my context. I've also broken a lot of SQL "rules" because those rules were not compatible, or were harmful, in my context. So my advice is this - understand your own context. Understand where you are constrained, and where you have plenty. And tailor your patterns around those parameters.
  - ki85squared 2y ago
    The most sensible and pragmatic advice in this thread.
dagss 2y ago
I think there are two different concerns here though: The article recommends something that may lead to using the wrong query plans. In the "right" conditions, you will do full table scans of all your data for every query. This is making the DB waste a lot of CPU (and IO). Wasting resources like that is different from just where to do work that has to be done anyway! I am a proponent of shifting logic toward the DB, because likely it ends up there anyway and usually you reduce the resource consumption also for the DB to have as much logic as possible in the DB. The extreme example is you want to sum(numbers) -- it is so much faster to sum it in one roundtrip to the DB, than to do a thousand roundtrips to the DB to fetch the numbers to sum them on the client. The latter is so much more effort also for the DB server's resources. My point is: Usually it is impossible to meaningfully shift CPU work to the client of the DB, because the client needs the data, so it will ask for it, and looking up the data is the most costly operation in the DB.
- cogman10 2y ago
  The answer is "it depends". Sum is a good thing to do in the Db because it's low cost to the db and reduces io between the db and app. Sort can be (depending on indexes) a bad thing for a db because that's CPU time that needs to be burned. Conditional logic is also (often) terrible for the db because it can break the optimizer in weird ways and is just as easily performed outside the db. The right action to take is whatever optimizes db resources in the long run. That can sometimes mean shifting to the db, and sometimes it means shifting out of the db.
  - crazygringo 2y ago
    It's hard to think of situations where you don't want to do the sorting on the DB. If you're sorting small numbers of rows it's cheap enough that it doesn't matter, and if you're sorting large numbers of rows you should be using an index which makes it vastly more efficient than it could be in your app. And if your conditional logic is breaking the optimizer then the solution is usually to write the query more correctly. I can't think of a single instance where I've ever found moving conditional logic out of a query to be meaningfully more performant. But maybe there's a specific example you have in mind?
scarface_74 2y ago
> The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. This is not received wisdom at all and the one edict I have when leading a project is no stored procedures for any OLTP functionality. Stored Procs make everything about your standard DevOps and SDLC process harder - branching, blue green deployments and rolling back deployments.
- oftenwrong 2y ago
  >Stored Procs make everything about your standard DevOps and SDLC process harder - branching, blue green deployments and rolling back deployments. There is a naming/namespacing strategy incorporating a immutable version identifier that makes this easier, which I have described here: https://news.ycombinator.com/item?id=35648974 https://news.ycombinator.com/item?id=35648974 Note that this requires a strategy for cleaning up old procedures. It also is possible to individually hash each procedure, which is more sophisticated, and would allow for incremental creation of new procedures.
  - scarface_74 2y ago
    That’s actually an ingenious solution. I can’t find any flaws in it.
ramchip 2y ago
I think the conventional wisdom is to lean on the DB to maintain data integrity, using constraints, foreign keys, etc.; not to use it to run actual business logic.
nycdotnet 2y ago
This is Brent Ozar's old theme.

BeefWellington 2y ago

I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. This isn't what I think you're saying -- just piggybacking to try and explain further. The key thing here is to understand that you want the minimal correct query for what you need, not to avoid "making the database work". The given example is silly because there's additional parameters that must be either NULL or have a value before the query is sent to the DB. You shouldn't send queries like: SELECT \* FROM users WHERE id = 1234 AND (NULL IS NULL OR username = NULL) AND (NULL IS NULL OR age > NULL) AND (NULL IS NULL OR age < NULL) But you should absolutely send: SELECT \* FROM users WHERE id = 1234 AND age > 18 AND age < 35

deredede 2y ago
While sub-optimal, your first example is probably fine to send and I'd expect to be simplified early during query planning at a negligible cost to the database server. What you shouldn't send is queries like: SELECT \* FROM users WHERE ($1 IS NULL OR id = $1) AND ($2 IS NULL OR username = $2) AND ($3 IS NULL OR age > $3) AND ($4 IS NULL OR age < $4) because now the database (probably) doesn't know the value of the parameters during planning and needs to consider all possibilities.
- oever 2y ago
  Interesting. I try to use prepared statements to avoid redoing the planning. But since the database schema is small compared to the data, the cost of query planning is quickly negligible compared to running an generic query that is inefficient.
tsarchitect 2y ago
One of my rules: don't send NULL over the wire, but of course always check for NULL on the server if you're using db functions.

kamma4434 2y ago

While this is no excuse for sending sloppy queries to the database server, my rule of thumb with databases - as I was told by my elders - is ”if it can be reasonably done in the database, it should be done by the database”. Data base engines are meant to be quite performant at what they do, possibly more than your own code.

cogman10 2y ago
Databases aren't magic. They have limited CPU and IO resources. They can only optimize within the bounds of the current table/index structure. And sometimes they make a bad optimization decision and need to be pushed to do the right thing. Databases can, for example, sort things. However, if that thing being sorted isn't covered by an index then you are better off doing it in the application where you have a CPU that can do the n log n sort. Short quips lead to lazy thinking. Learn what your database can and can't do fast and work with it. If something will be just as fast in the application as it would be in the database you should do it in the application. I've seen the end result of the "do everything in the database" thinking and it has created some of the worst performance bottlenecks in my company. You can do almost everything in the database. That doesn't mean you should.
- blazing234 2y ago
  Better off doing it in the application? Bro im not pulling that much data to do a top N query
  - cogman10 2y ago
    > Short quips lead to lazy thinking. Learn what your database can and can't do fast and work with it.
- kamma4434 2y ago
  That’s an optimization, and does not mean that the general rule is not valid. If that happens to be a bottleneck and you can do better, you should definitely do it in code locally. But these are two ifs that need to evaluate to true