Are PostgreSQL functions generally faster than code?

Question

We all know that it's more performant to use WHERE's over filtering on the app side. But is it more performant to use PostgreSQL functions over app code? Here's a reference example:

Given an array = [1, 2, NULL]...

array_remove(array, NULL)

vs.

result = SQL.execute(array); result.remove_null_values()

Assuming I have to run code in the app anyways, is it really worthwhile to move every single code into SQL? As in, getting major performance (> 10%).

Answer 1

It is a tradeoff. If doing the work in the database means sending less stuff over the network, it is probably a win. But remember that usually apps scale horizontally and databases scale vertically, so pushing a lot of work into the database might cause problems later.