RealTime IT News

PostgreSQL 8.2 Closes The Gap

After more than a year of active development, the open source PostgreSQL 8.2 database is now available.

The release is an effort to close the gap on a performance and functionality basis between PostgreSQL and its competitive proprietary counterparts such as Oracle, IBM's DB2 and Microsoft's SQL Server.

There are over 200 feature improvements or additions in PostgreSQL 8.2, including Warm Standby Databases that will enable database administrators to have a failover copy of their database within a database cluster.

Generalized Inverted Indexes is another new feature that PostgreSQL Project Core Team Member Josh Berkus said will help to lay the foundation for the large database "semi-structured" database search tools of tomorrow.

PostgreSQL 8.2 also includes some SQL syntax enhancements that are commonly requested by those planning on migrating from Oracle.

"This release further 'closes' the gap between PostgreSQL and the leading proprietary databases for high-end OLTP and DW applications, and thus increases the number of Oracle/DB2/SQL Server customers who can migrate to PostgreSQL without sacrificing performance," Berkus told internetnews.com.

PostgreSQL developers expect that the new version will drive such migrations because of the improved performance, features and administration capabilities. They anticipate an overall performance gain of between 10 percent and 20 percent for most applications.

PostgreSQL 8.2 is also taking aim at the open source MySQL database.

"Our enhanced multi-processor scalability means that we can now outperform MySQL for Web performance on some high-end systems with heavy concurrency, which will encourage MySQL users with those kinds of applications to migrate," Berkus said.

Berkus said the fact that PostgreSQL 8.2 performance is at the same level, or better than Oracle, IBM DB2 or Microsoft SQL Server means that more companies will now be open to using PostgreSQL.

PostgreSQL 8.2 is not, however, on a path to be Oracle; for that there is EnterpriseDB, which builds its database on top of PostgreSQL and is an active member of the PostgreSQL community. Oracle compatibility add-ons are baked into EnterpriseDB but are not all in PostgreSQL 8.2

"There are features which are an emulation of Oracle features based on our own design or published standards but they were contributed because they are generally useful, not because Oracle has them," Berkus said.

"For specific Oracle syntax compatibility, that's something that will remain specific to EnterpriseDB by community consensus. PostgreSQL is not Oracle and we don't want to be Oracle."

One such example of Oracle compatibility that EnterpriseDB includes that PostgreSQL 8.2 does not is the way in which the databases handle recursive queries.

"EnterpriseDB supports Oracle's Connect By, but the community rejected that," Berkus said. "We're working on the ANSI SQL99 standard version with recursive, which also has the benefit of more functionality than Oracle's version. EnterpriseDB staff are contributing to that development."

Though PostgreSQL 8.2 is expected to help increase migrations, Berkus admitted that unfortunately the PostgreSQL community doesn't have complete figures on people migrating from other DBMSs. The fit and finish will help make the move easier for those migrating, whatever their numbers may be.

"We've done a lot of dotting I's and crossing T's with this release on syntax and administrative functionality," Berkus explained. "This adds up to a better out of the box experience for DBAs just testing moving from another system to PostgreSQL."

The previous major point release for PostgreSQL was version 8.1 which introduced new Windows compatibility features. The next PostgreSQL release is expected to come out sooner than the 13 months than it took for 8.2 to come out, which happened in November 2005.

Berkus said that PostgreSQL 8.3 is expected to be a shorter release, expected as it is around July 2007.

"The reason for this is to get out quickly some features that didn't make 8.2," Berkus said. "It's also to change the timing of our release cycle, which currently has its busiest period at midsummer when the European developers are on vacation and the U.S. developers are at conferences."

Among the improvements expected in PostgreSQL 8.3 are further performance gains.

"The most exciting of these is an optimization that would improve performance on OLTP systems between 50 percent and 200 percent depending on the application," Berkus noted. "However, that feature involves some difficult engineering that may not be ready in time."