Regarding your question:
> Is there a quantifiable performance gain with moving more operations to SQL rather than implementing it in my language of choice?
It depends, of course, but in my experience the answer is yes for 90% of the cases.
In my experience, the database is just faster than application code at most stuff and queries can be written in a way that similar logic can’t be optimized at the application level. Additionally, database code is usually (always?) going to be faster than something like Python/Ruby.
Code review is more difficult at the database level, however, and SQL talent is more rare. It may be more challenging to scale and maintain a team of engineers working on a codebase with a lot of application logic implemented in stored procs/SQL versus ORM code (such as active record or Django). Just my opinion, have worked on teams with ORM usage as well as separate DBA teams and all logic in SQL. SQL by hand is more powerful and flexible, surely, but for most use cases I would prefer to keep code in one place (application) and only reach for the tool (raw SQL) when needed for optimizations.
The reasons are:
1) You often need a transactional, consistent view of the data across multiple datasets (tables). Relational databases using SQL provide that, key-value stores may or may not (most likely not).
2) Any time you're doing any kind of aggregation, key-value stores will require that you pull all data in locally and perform the operation there. It's questionable whether the local environment even has the resources to accomplish such a task, and the efficiency of such operations will be dismal compared to a SQL-based relational database server.
Key-value stores are, from an interface perspective, very much like the older ISAM databases that were popular before SQL and relational databases gained a foothold in the industry. They also recycled the exact same problems that SQL-based relational databases solved so elegantly (round and round we go).
Another way to put it is this: leaving aside scaling for now, SQL-based relational databases can typically do everything that a key-value store can do, whereas key-value stores cannot typically do everything that a SQL-based relational database can do.
It's simply more efficient? If I have a table with millions of rows, I'm not going to bring down the entire data set on every request just to sort by latest and grab the 10 most recently updated rows that haven't been soft deleted (WHERE active=1 ORDER BY updated_at DESC LIMIT 10).
Also, his examples are like the tip of the iceberg. There are all kinds of things like windowing functions and common table expressions that have so much power to get exactly as much data as you need right at the RDBMS without having to carry that baggage anywhere else.
Anything that deals with the formatting/display of the columns in the result set rows can be done in SQL, but doesn't have to be done in SQL. It's really a matter of convenience to do it in SQL, especially when dealing with multiple front-end languages, because it means that the formatting is done in one place.
Each method has pros and cons, it depends on the capabilities of your database, your requirements in terms of security, and the degree of cross connection between data stores. For internal business tools I like to do stuff at the database layer, but it would be the wrong choice for a high volume, simple, public API.
The conclusion states:
> Exploit the power of SQL to write less code because “the best code is the code that was never written”. If it is not written there is no need to maintain it.
But they're wrong, since you still need to maintain all of the SQL implementations...
Edit: I previously said "I'm not a DBA", but some folks seem to be getting hung up on that rather than the rest of my comments/questions.