Monday, August 16, 2010

Why We're Conservative With PostgreSQL Minor Releases

Last week, a PostgreSQL user filed bug #5611, complaining about a performance regression in PostgreSQL 8.4 as compared with PostgreSQL 8.2. The regression occurred because PostgreSQL 8.4 is capable of inlining SQL functions, while PostgreSQL 8.2 is not. The bug report was also surprising to me, because in my experience, inlining SQL queries has always improved performance, often dramatically. But this user managed unluckily hit a case where the opposite is true: inlining caused a function which had previously been evaluated just once to be evaluated multiple times. Fortunately, there is an easy workaround: writing the function using the "plpgsql" language rather than the "sql" language defeats inlining.

Although the bug itself is interesting (let's face it, I'm a geek), what I found even more interesting was that I totally failed to appreciate the possibility that inlining an SQL function could ever fail to be a performance win. Prior to last week, if someone had asked me whether that was possible, I would have said that I didn't think so, but you never know...

And that is why the PostgreSQL project maintains stable branches for each of our major releases for about five years. Stable branches don't get new features; they don't get performance enhancements; they don't even get tweaks for things we wish we'd done differently or corrections to behavior of doubtful utility. What they do get is fixes for bugs (like: without this fix, your data might get corrupted; or, without this fix, the database might crash), security issues, and a smattering of documentation and translation updates. When we release a new major release (or actually about six months prior to when we actually release), development on that major release is over. Any further changes go into the next release.

On the other hand, we don't abandon our releases once they're out the door, either. We are just now in the process of ceasing to support PostgreSQL 7.4, which was released in November 2003. For nearly seven years, any serious bugs or security vulnerabilities which we have discovered either in that version or any newer version have been addressed by releasing a new version of PostgreSQL 7.4; the current release is 7.4.29. Absent a change in project policy, 7.4.30 will be the last 7.4.x release.

If you're running PostgreSQL 8.3 or older, and particularly if you're running PostgreSQL 8.2 or older, you should consider an upgrade, especially once PostgreSQL 9.0 comes out. Each release of PostgreSQL includes many exciting new features: new SQL constructions, sometimes new data types or built-in functions, and performance and manageability enhancements. Of course, before you upgrade to PostgreSQL 8.4 (or 9.0), you should carefully test your application to make sure that everything still works as you expect. For the most part, things tend to go pretty smoothly, but as bug #5611 demonstrates, not always.

Of course, this upgrade path is not for everyone. Application retesting can be difficult and time-consuming, especially for large installations. There is nothing wrong with staying on the major release of PostgreSQL that you are currently using. But it is very wise to upgrade regularly to the latest minor version available for that release. The upgrade process is generally as simple as installing the new binaries and restarting the server (but see the release notes for your version for details), and the PostgreSQL community is firmly committed to making sure that each of these releases represents an improvement to performance and stability rather than a step backwards.

4 comments:

  1. Robert,

    I'm pretty sure inlining sql functions existed in PostgreSQL 8.2 and I think even before that.

    We in PostGIS group started taking advantage of it in our 1.3 (late 1.2 versions) for our index based functions.

    See matrix
    http://www.postgresql.org/about/featurematrix.html

    Inlining of SQL-functions

    Or are we talking about a different kind of SQL inline functions.

    Also marking an SQL function as strict (though not always desireable to do so) seems to also make it not inlineable as we discovered and noted here

    http://www.postgresonline.com/journal/archives/163-STRICT-on-SQL-Function-Breaks-In-lining-Gotcha.html

    ReplyDelete
  2. I believe in this case the relevant patch was this one.

    http://archives.postgresql.org/pgsql-committers/2008-03/msg00357.php

    ReplyDelete
  3. Robert,

    Thanks. That makes sense now.
    I'll have to experiment with that sometime as I didn't realize PostgreSQL made a distinction when to inline (if in SELECT, FROM, WHERE).

    I take it though that SELECT and WHERE are treated the same or does it have more to do with if its a set returning function.

    ReplyDelete
  4. You may need to experiment a bit, but I would NOT assume that SELECT and WHERE are treated the same. We have some pretty grotty hacks in place right now to handle the SRF-in-targetlist case. In general, rewriting such constructs requires LATERAL support, which we won't have until at least (hopefully exactly) 9.1.

    ReplyDelete