Using a Postgres database with fields that includes nested json data, it is sometimes easier to handle filtering operations in the clojure rather than trying to compose and test the appropriate SQL query. In my case the data in question is going to remain relatively small (less than a few dozen), but I’m curious in the general sense: how much slower is a clj filter command going to be than the SQL where? I assume the choke-point will be the pipeline between db and application, but I’m interested in anything people have observed about this.
The Golden Rule is: if the DB can do it, it should do it. In practice, if the result set is small, the difference will be negligible. On the other hand, a simple SQL query will be way more compact and legible than the corresponding Clojure code that does the same thing.
4 Likes
I’ve usually followed this concept, but found myself questioning
it (especially where db idiosyncrasies are concerned), wondering
if it was a significant consideration. Thanks for your take on
it.
Well, DBs are highly optimized, but your custom in memory code could be as well. I’d just measure it.
The biggest factor i can think of is the DB often leverages indexes for the where clause, which can speed up certain filtering quite a lot. And if you do the filtering, you’re having to retrieve more records which will add quite a bit of overhead to the network hop.
Chances are DB is almost always faster in my opinion, but if your motivation is design of your code base, the relative performance might be acceptable to your use case.
The low-level representation of data (e.g data structures) can play an important role too - better memory layout leading to fewer pointer hops and CPU cache misses, etc.
I think that if you can go for a 20-line query that returns 1000 records with some server side filtering versus a 200-line unreadable query that returns 100, it’s likely worth it. If you have to scan and fetch 10M records to compute 15 results, most likely the DB will be way better.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.