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 functions in PostgreSQL (9.0), specifically regarding performance pitfalls.

  • do you mean you don't want answers to mention anything not performance related? Commented Nov 20, 2011 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.
    – user1822
    Commented Apr 10, 2013 at 18:05

4 Answers 4


Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. See:

There are also functions, doing almost but not quite the same, and those have been there from the beginning.

Functions with LANGUAGE sql basically just wrap SQL commands (DML and/or DDL) into a function, accepting parameters. Execution is always atomic (all of it runs inside a single transaction). All statements in an SQL function are planned at once, before executing anything. That's subtly different from executing one statement after the other (especially when involving DDL commands) and may affect the order in which locks are taken.

For anything more, the most mature language is PL/pgSQL (LANGUAGE plpgsql). You have all the usual procedural elements at your disposal, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).

PL/pgSQL functions execute queries like prepared statements. Re-using cached query plans cuts off some planning overhead and makes them a bit faster than equivalent SQL statements, which may be a noticeable effect depending on circumstances. It may also have side effects like in this related question:

This carries the advantages and disadvantages of prepared statements - as discussed in manual. For queries on tables with irregular data distribution and varying parameters dynamic SQL with EXECUTE may perform better when the gain from an optimized execution plan for the given parameter(s) outweighs the cost of re-planning.

Generic execution plans can be cached for the session but, quoting the manual:

This occurs immediately for prepared statements with no parameters; otherwise it occurs only after five or more executions produce plans whose estimated cost average (including planning overhead) is more expensive than the generic plan cost estimate.

We get best of both worlds most of the time (less some added overhead) without (ab)using EXECUTE. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.

Postgres 12 added the server variable plan_cache_mode to force generic or custom plans. For special cases, use with care. Also see:

You can win big with server side functions that prevent additional round-trips 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 (...)). Functions are black boxes posing as optimization barriers to the query planner. They are optimized separately, not in the context of the outer query, which makes planning simpler, but may result in less than perfect plans. Also, cost and result size of functions cannot be predicted reliably.

The exception to this rule are simple SQL functions (LANGUAGE sql), which can be "inlined" - if some preconditions are met. Read more about how the query planner works in this presentation by Neil Conway (advanced stuff).

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

That's also why functions are not exactly "stored procedures" (even though that term is used sometimes, misleadingly). Some commands like VACUUM, CREATE INDEX CONCURRENTLY or CREATE DATABASE cannot run inside a transaction block, so they are not allowed in functions. (Neither in SQL procedures, yet, as of Postgres 11. That might be added later.)

I have written thousands of PL/pgSQL functions over the years.

  • 2
    @nhahtdh: "automatic transaction" is not a technical term. It was just a hardly elegant way of saying .. what it's saying now after my clarification. Not an autonomous transaction at all. "autonomous" just happens to be a similar word. Commented Mar 10, 2015 at 3:01
  • 7
    Your answers compiled from here and SO could be an epic PostGreSQL best practice handbook.
    – Davos
    Commented Nov 3, 2017 at 13:07

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 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 keyset pagination over OFFSET pagjnation
  • Make sure you unit-test your functions
  • 2
    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?
    – user1822
    Commented Dec 19, 2013 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. Commented Dec 19, 2013 at 22:25

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)


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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