Keeping Too Much Logic in SQL Queries Does Not Scale

Replacing hundreds of lines of code by a single clever SQL snippet is fun and intellectually satisfying. And, after all, there are compelling reasons.

It's good for performance. Processing data in situ before returning saves bandwidth and reduces latency. This is especially true for mega-queries that embed complex business logic and avoid back-and-forth between the application code and the database.
I've seen tremendous performance improvement by replacing complicated code with pompous SQL. A request that took a few seconds to complete now takes a few milliseconds—really.

Besides, it's pretty elegant. SQL is an expressive language particularly suited to data manipulation, certainly more than the typical languages we use for application code.

Praise To Boring SQL

Despite the benefits, I try to write more "boring" SQL these days and avoid offloading too much heavy work onto the database, trying to keep transactions short and lightweight.

Scaling out and distributing the workload is easier with application code. It's just a matter of throwing more containers or servers to keep up with demand. However, with databases, things are not as simple (even with managed databases running in the Cloud) due to their inherent stateful and long-lasting nature. Better keep the heavy work in code that runs on more elastic resources.

A few years ago, the typical database setup was a bare metal server with a decent CPU, a fair amount of RAM, and fast enterprise SAS disks or SSDs.
Nowadays, it's common to have databases managed by the cloud service provider that run on ridiculously anemic VMs and even production setups. Better keep the heavy work in code that runs on cheaper computation resources.

Should We Treat the Database as Dumb Storage?

Databases are very capable; it'd certainly be a shame to treat them as "dumb storage." They are still the more effective tools for filtering, pre-processing, and accessing data efficiently, and SQL is the language to do that.

Sure, if I find myself writing data-retrieving logic in application code, I'm probably doing something I should offload to the database by writing SQL.

Other Views