PostgreSQL 8.1 includes performance improvements and advanced SQL features that will support bigger data warehouses, higher-volume transaction processing and more complex distributed enterprise software.
Bruce Momjian, a member of the core PostgreSQL development team, said the focus of the update is to move PostgreSQL beyond the OLTP (online transaction processing) market where its been at home up until now.
"Weve always done well in that market," he said. "Weve moving more into the data warehousing area with table partitioning and the bit map index."
The update features big improvements for multi-CPU systems, he said. Whereas PostgreSQL has in the past been held back from scalability, it now will scale almost linearly, depending on how many CPUs are available.
"With every release weve been able to chip away at what we need to do to be completely linear," he said.
The last sticking point as far as linear scalability goes was to make some buffer improvements in 8.1, Momjian said.
The important thing was to fix it so it didnt hamper performance on single-CPU machines, he said. "The trick was to get something that worked, that didnt require any configuration on users part, and that didnt require special binary."
The improved multiprocessor performance will make for performance gains on 8-way, 16-way, dual-core and multicore CPU systems.
With 8.1, getting a lock on a single-CPU machine has become more efficient, with the machine relying on heuristics that tell it to take a little nap and to attempt to get the lock again in a little while, instead of beating itself against the kernel, Momjian said.
"Thats given us very good multiprocessor performance while working very well on a single-CPU machine and not needing anyone to set CPU parameters saying whether theyre on a single-CPU machine or a multi-CPU machine," he said.
Other new, advanced database features in PostgreSQL 8.1 include IN/OUT parameters, substantially improving support of complex business logic for J2EE (Java 2 Platform, Enterprise Edition) and .Net applications.
Another feature in 8.1 thats long been in demand for WAN applications and heterogeneous data centers using PostgreSQL is two-phase commit. The feature allows ACID-compliant transactions across widely separated servers.
Other new features include shared row locking and bit map scan, which means that indexes will be dynamically converted to bit maps in memory when appropriate, giving up to 20 times faster index performance on complex queries against very large tables.
For its part, Sleepycat is boasting that its update can increase performance by 500 percent in many cases. Berkeley DB XML 2.2, the latest version of the companys native XML database, now comes with node-level indexes, which dramatically improve query performance, especially for large XML documents; optimization of query plan generation and indexing; streamlined path expression evaluation; improved resource use for node storage containers; new index lookup functions; and support for the April drafts of XQuery 1.0 and XPath 2.0.
Berkeley DB XML 2.2 is available immediately under a dual license. A no-cost open-source license permits redistribution if the application using Berkeley DB XML is open source. A commercial license is available for redistribution of proprietary applications. It can be downloaded here.