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

did you try using index on fields in condition?

Adam Silenko
– Adam Silenko

2016-03-21 22:01:41 +00:00
Commented Mar 21, 2016 at 22:01 — Adam Silenko
– Adam Silenko, Commented Mar 21, 2016 at 22:01

Paul A Jungwirth · Accepted Answer · 2016-03-21 21:53:20Z

4

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.

answered Mar 21, 2016 at 21:53

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jim Nasby Over a year ago

While true, I've never seen a database that was CPU bound unless it was just hammered with work and all the data fit in memory. So generally speaking, the closer you can do computation to the data, the better off you'll be. If it's critical, benchmark it. The other case where doing stuff in the database makes a ton of sense is when it's a data problem. IE: if you need to dynamically pull a bunch of data based on configuration, a stored proc will almost certainly outperform individual app queries. (A single large query may or may not be faster still.)

Paul A Jungwirth Over a year ago

I pretty much agree 100%. :-) For most of us the scalability thing is just a technicality.

Geoff Lee Over a year ago

@jim-nasby Currently (and this will be fixed later) we have a huge query with lots of joins so yes, it is reaching its limits

Simeon Visser · Accepted Answer · 2016-03-21 21:51:49Z

3

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.

answered Mar 21, 2016 at 21:51

Simeon Visser

123k19 gold badges192 silver badges185 bronze badges

Collectives™ on Stack Overflow

Are PostgreSQL functions generally faster than code?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related