Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.

One of the things I would like to do is move some of the application logic into stored procedures, so I'm here asking for DOs and DON'Ts (best practices) on using stored procedures in PostgreSQL (9.0), specifically regarding performance pitfalls.

share|improve this question
    
do you mean you don't want answers to mention anything not performance related? –  Jack Douglas Nov 20 '11 at 20:51
    
Chris Travers blogs a lot about the advantages of using stored procedures, e.g. here: ledgersmbdev.blogspot.de/2012/07/… and here: ledgersmbdev.blogspot.de/2012/07/… just skim through his blog, there are a lot of interesting articles on this topic. –  a_horse_with_no_name Apr 10 '13 at 18:05
add comment

4 Answers

up vote 10 down vote accepted

Functions with LANGUAGE SQL are basically just batch files with plain SQL commands in a function wrapper accepting parameters. For anything more, as Jack wrote, the most mature language is PL/pgSQL (LANGUAGE plpgsql). It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).

Functions in PostgreSQL execute queries like prepared statements, which cuts off some of the overhead and make them generally faster that the equivalent SQL statements. This may be a noticeable effect depending on circumstances.

This carries the advantages and disadvantages of prepared statement - as described in the linked manual page. In the case of queries with very uneven data distribution and varying parameters and results it may be of advantage to use dynamic SQL with EXECUTE, because the gain from an optimized execution plan is bigger than the loss due to re-planning every time.

PostgreSQL 9.2 brought a major improvement in this area: The planner now plans the query at execution and decides whether it could be worth to replan with the current parameter values. So you get the best of both worlds, performance-wise, and you don't have to (ab)use EXECUTE for this purpose any more. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.

You can win big with server side functions when you prevent additional journeys to the database server from your application. Have the server execute as much as possible at once and only return a well defined result.

Avoid nesting of complex functions, especially table functions (RETURNING SETOF record or TABLE (..)), where the query planner might otherwise optimize away redundant operations. Functions are black boxes for the query planner. They cannot be further optimized. And cost and result size of functions can not effectively be predicted.

The exception to this rule are simple SQL functions (language SQL), which can be "inlined". Read more about how the query planner works in this presentation by Neil Conway. (Advanced stuff.)

In PostgreSQL a function is always an automatic transaction. All of it succeeds or nothing. If an exception occurs, everything is rolled back. But there is error handling ...

Here is a somewhat conservative but informative review of the capabilities of PL/pgSQL by the Czech PostgreSQL community. Note that it is a bit outdated by now (written for 8.1) and PL/pgSQL has seen a number of major improvements since.

I have written thousands of plpgsql functions over the years.

share|improve this answer
add comment

Some DO's:

  • Use SQL as the function language when possible, as PG can inline the statements
  • Use IMMUTABLE / STABLE / VOLATILE correctly, as PG can cache results if it's immutable or stable
  • Use STRICT correctly, as PG can just return null if any input is null instead of running the function
  • Consider PL/V8 when you can't use SQL as the function language. It is about 100x faster than PL/pgSQL in some unscientific tests that I ran
  • Use LISTEN / NOTIFY for longer-running processes that can happen out-of-transaction
  • Consider using functions to implement pagination as key-based pagination can be faster than LIMIT based pagination
  • Make sure you unit-test your functions
share|improve this answer
    
It's the first time I see the claim that PL/V8 is faster than PL/pgSQL. Do you have any (published) figures to support that? –  a_horse_with_no_name Dec 19 '13 at 22:21
    
@a_horse_with_no_name no, I don't. Like I said, I did a few unscientific tests. They were mostly logic, not data access. I'll try to do some repeatable tests over xmas and re-post here. –  Neil McGuigan Dec 19 '13 at 22:25
add comment

You can do some very interesting stuff using user defined functions (UDF) in postgresql. For instance, there's dozens of possible languages you can use. The built in pl/sql and pl/pgsql are both capable and reliable and use a sandbox method to keep users from doing anything too terribly dangerous. UDFs written in C give you the ultimate in power and performance, since they run in the same context as the database itself. However, it's like playing with fire, because even small mistakes can cause huge problems, with backends crashing or data getting corrupted. The custome pl languages, like pl/R, pl/ruby, pl/perl, and so on provide you with the ability to write both database and app layers in the same languages. This can be handy, since it means that you don't have to teach a perl programmer java or pl/pgsql etc to write a UDF.

Lastly, there is the pl/proxy language. This UDF language allows you to run your application across dozens or more backend postgresql servers for scaling purposes. It was developed by the good folks at Skype and basically allows for a poor man's horizontal scaling solution. It's surprisingly easy to write in as well.

Now, as to the performance issue. This is a gray area. Are you writing an app for one person? Or for 1,000? or for 10,000,000? The way you build your app and use UDFs will depend a LOT on how you're trying to scale. If you're writing for thousands and thousands of users, then the main thing you want to do is reduce the load on the db as much as possible. UDFs that reduce the amount of data being moved out and back into the database will help reduce IO load. However, if they start to increase CPU load, they may be an issue then. Generally speaking reducing IO load is the first priority, and making sure the UDFs are efficient so as not to overload your CPUs is next.

share|improve this answer
add comment

Generally speaking moving application logic into the database will mean it is faster - after all it will be running closer to the data.

I believe (but am not 100% sure) that SQL language functions are faster than those using any other languages because they do not require context switching. The downside is that no procedural logic is allowed.

PL/pgSQL is the most mature and feature-complete of the built in languages - but for performance, C can be used (though it will only benefit computationally intensive functions)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.