November 8, 2005

PostgreSQL 8.1 released

Author: Joe Brockmeier

The PostgreSQL Global Development Group (PGDG) is expected to formally announce the availability of PostgreSQL 8.1 today at the Open Source Database Conference in Frankfurt, Germany. The new release has more than 120 enhancements, according to a press release from the PostgreSQL Project, including new and improved features and performance improvements. There are also a number of changes in query syntax and PostgreSQL utility commands, which are detailed in the release notes.

PostgreSQL is licensed under the BSD license, and is available for most Unix-type operating systems, including Linux, and Microsoft Windows. PostgreSQL has been in development, in one form or another, since 1986. The project was released as open source in 1996 as PostgreSQL 6.0, and has been under active development since. PostgreSQL 8.0, the previous version, was released in January.

What's new in 8.1

Bruce Momjian, one of the core developers for the project, said that 8.1 is a major release, despite the minor version bump. Momjian said that the PostgreSQL numbering was "kind of goofy," but that the project "would be up to version 25 or something" if it followed traditional version numbering practices.

One of the major new features in 8.1 is support for roles, a concept that is supposed to make it easier to manage users. The PostgreSQL implementation of roles is based on the SQL standard concept of roles, and removes the concept of groups and users being distinct entities. A role in PostgreSQL can act as a user, group, or both. Roles are able to own database objects, have access permissions for objects, and inherit permissions from other roles.

PostgreSQL 8.1 also has support for two-phase commit, IN/OUT parameters, shared row locking, and bitmap scan. Bitmap scan, according to Momjian, was implemented primarily for data warehousing applications where an organization may have a large number of fields in the database where all fields cannot be indexed. The bitmap scan feature makes it easier to perform ad-hoc searches that may not be anticipated at the time the database is created. Momjian used the example of a company like Wal-Mart with a large number of records, where a user may want to search the database to find out "how many of these shoes were bought on this day" or other queries that will help a user figure out sales patterns.

In addition to adding new features, Momjian said that performance has been improved in 8.1 as well, particularly on "big iron" machines with a large number of CPUs. "Our goal here was to increase the big iron throughput without hurting performance on a single CPU, and I think we've definitely done that." Momjian said that the Open Source Development Labs have been helping the PostgreSQL project in testing on larger machines that the PGDG may not ordinarily have access to.

Corporate support for PostgreSQL 8.1

According to Momjian, "this one was where we started to really take advantage of the companies circling around the project." Indeed, PostgreSQL has attracted a wide variety of companies who are incorporating PostgreSQL into their products, and offering commercial support for PostgreSQL. Despite the corporate involvement, Momjian pointed out that PostgreSQL is "still a volunteer organization," but the project has been able to hire people full time to focus on PostgreSQL.

Companies supporting PostgreSQL development include Greenplum, which is offering Bizgres, a modified distribution of PostgreSQL tailored for use with Business Intelligence (BI) applications. EnterpriseDB is selling the EnterpriseDB Database Server, which is a version of PostgreSQL that's modified to be Oracle-compatible. Momjian also noted that Fujitsu, Pervasive Software, and Afilias have sponsored PostgreSQL development, as have a number of others.

Sun Microsystems has expressed an interest in PostgreSQL recently as well. We contacted Sun to see whether Sun was becoming more involved with the PostgreSQL project, but the company declined to offer a spokesperson or statement regarding its involvement with PostgreSQL. PostgreSQL core member Josh Berkus did note that Sun had been "contributing in several small ways lately, including Solaris platform testing, work on JDBC compliance, Solaris performance ... they've been very friendly for the last several months to us as an OSS project."

According to Momjian, the assorted companies involved in PostgreSQL development are playing well with one another, and the PostgreSQL community in general. Momjian said that companies that get involved with the project "tend to understand" that it's not a good idea to dump in-house patches on the project and expect them to be incorporated into the PostgreSQL release. He also said that companies have been good about contributing to the project, and he could recall only one or two companies using PostgreSQL in their products that had taken the code and not contributed to the project.

Moving beyond OLTP

PostgreSQL's stronghold has been in the area of online transaction processing (OLTP). However, Momjian pointed out that the project has recently been adding features needed for data warehousing and business intelligence applications which will take it to "the next level beyond OLTP areas."

Momjian said that the project, while looking to increase the functionality of PostgreSQL and make it suitable for a wider range of tasks, was also keen to keep its existing users happy. "You don't want to lose your users, once you get them. You don't want to lose them by frustration, or regressions in what it used to be able to do before."

To that end, Momjian said that the PostgreSQL project spends a great deal of time testing prior to each release. PostgreSQL 8.0 was released in January, and work was started on PostgreSQL 8.1 immediately. Feature freeze for PostgreSQL 8.1 was in July, and Momjian said that the work on the release since July has been testing new features and fixes to ensure there were no regressions or performance problems caused by new features.

Migrating to 8.1

Moving to 8.1 shouldn't be too difficult for those using PostgreSQL 8.0, according to Momjian. For most users, Momjian said that moving a database to 8.1 from 8.0 should be as simple as dumping the database and reloading it under 8.1. Applications written for 8.0 should also be mostly compatible. According to Momjian, "95 percent of what we do is specified by ANSI standard. If it has to function a certain way, we're not going to change it."

There's no rush for users to upgrade to 8.1, though. According to Momjian, the 8.0.x release should continue to receive security fixes and bug fixes for some time. The project doesn't guarantee any specific amount of time that PostgreSQL releases will be supported, but Momjian estimated that each release receives about three years of support in terms of security and bug fixes. He said that the project tries to provide security patches "as far back as we can," and noted that the project released a security patch for the 7.2 release about a month ago.

If one of the new features in 8.1 is appealing, however, users will need to upgrade to benefit from them. Momjian said that the project never backports features to older releases.

The competition

The question on many users' minds, of course, is how PostgreSQL stacks up with other databases. When compared to Oracle, Momjian said that PostgreSQL has had "parity" with Oracle for "several years now" when it comes to OLTP applications and "plain vanilla" features.

Momjian said that he didn't see "any compelling value" to choosing MySQL over PostgreSQL for brand new projects, and said that the PostgreSQL development model was superior to MySQL's. "We feel that community development is superior" than products developed by a single company.

Ultimately, Momjian said that the important thing was not "where we are in comparison with MySQL, Oracle, Microsoft, it's our trajectory. It's 'how quickly are we growing our userbase?'

"We don't have to win or make money, or 'beat them,' we just need to keep doing what we're doing as long as it's successful and meets the needs of our community."

Click Here!