CODEWITHSUNDEEP
sqlperformancepostgresql
Geoff Lee
Geoff Lee

Reputation: 152

Are PostgreSQL functions generally faster than code?

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%).

Upvotes: 4

Views: 1462

Answers (2)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24551

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.

Upvotes: 4

Simeon Visser
Simeon Visser

Reputation: 122356

If performance matters then it's worth profiling depending on your specific data processing needs.

In your example: given that removing NULL values needs to be done anyway, it makes sense to let Postgres do this as it means less data will need to be returned to your application.

Other benefits of letting Postgres do the work include: less code to maintain in your own application and reusing well-tested code already available in Postgres.

Upvotes: 3

Related Questions