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.
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.
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
Your bad query can not be a bottleneck but can negatively impact the performance of everyone else.
Databases are highly susceptible to the noisy neighbor problem.
Databases aren't magic. If you can do something better or the same outside the database, you should. 1000 cpu cycles are better spent on the application than the database. You can easily add more application servers.
Your general rule is invalid because of this. It doesn't have to be a bottleneck before it can be a problem. It's a "general rule" I particularly hate because I do performance tuning at my company and have OFTEN seen this be a root cause to negative outcomes. Devs using these sorts of shorthands without understanding what their database is and isn't good at. It's right up there with "premature optimization" which gets parroted at me by jr devs that want to write an n^3 algorithm when an n algorithm exists if they'd just use a data structure besides `List`.
Don't shut your brain off when coding. Sometimes it is better to make the database do something, sometimes it isn't. When that is true is context and situation dependent.
> 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
If the OP said what you are saying, I'd probably agree. However, the above statement makes it clear that the OP is saying "put it in the database unless you can prove it doesn't belong there".
That is what I disagree with. There's a lot of reasonable things you can do with a database which aren't the best thing to do from both a system and performance perspective. It is, for example, reasonable to use the sort method on a database. It's also not something you should do without proper covering indexes. Especially if the application can reasonably do the same sort.
