The open source PostgreSQL database is out with a new release today that introduces new administration and database features for users.
In total, PostgreSQL 8.4 includes 293 enhancements from the previous 8.3 release, an update that was primarily centered around performance.
“There is no performance change [that] affects all users equally across the board,” PostgreSQL project core team member Josh Berkus told InternetNews.com. “However, there are several changes with dramatically improve performance for several specific common use cases.”
The new PostgreSQL release comes at a critical time in the open source database market, as rival MySQL is about to be absorbed into Oracle as part of its acquisition of Sun.
While it’s unclear how MySQL may change from its new association with the proprietary database giant, the powers behind PostgreSQL aren’t sitting still.
Among the new changes in its new release are improvements to some complex reporting queries, which could improve performance by as much as 400 percent, according to Berkus. Database restoration is also accelerated in the new release, with a Parallel Restore feature that Berkus said could speed up loading a new database, or migrating an existing one, by as much as eight times — depending on the number of processors a user has.
Berkus added that there are also new SQL
PostgreSQL 8.4 users will also gain new “windowing aggregates,” functions that enable users to do aggregate operations — like count, sum, and rank — over a subset of data.
Berkus explained that conventional aggregates only deliver one summarized row; that is, you get one total for sum(). In contrast, windowing aggregates can deliver that summary and also per-row information on the data set, which means that you can get that total, but you can also get the subtotals.
“This gets even more useful when you want to do things like, for example, rank staff based on their number of hours worked within each department,” Berkus said. “You could do that with earlier versions of PostgreSQL, but only using procedural code or multiple sub-queries. Now, it’s much simpler.”
Security
Security also gets a boost in PostgreSQL 8.4 with a new per-column permissions feature, which is intended to provide granular control for confidential data. The column privileges also have the benefit of limiting data that can be exposed through SQL injection.
Berkus said that the per-column permissions is a feature that will require PostgreSQL to develop more sophisticated privilege management tools to deal with the potential complexity of secure systems — which is something the team is currently working on.
Still, Berkus added that per-column permission as it stands in PostgreSQL 8.4 already reduces complexity for the users who need it.
“Say you have a table [that] has one or two columns — such as credit card numbers or passwords, which need special access control … a very common design issue,” Berkus said. “Previously, you’d have had to do that either by moving those columns to a separate table with different permissions, or creating a view with different permissions than the base table.”
“Being able to simply say, ‘only administrators can see these two columns’ in the table itself is actually a lot simpler,” he added.
PostgreSQL 9?
It’s not yet clear whether the next version of PostgreSQL will be version 8.5 or version 9.
“We don’t decide on a first-digit bump until we’re sure what’s going to be in that version,” Berkus said. “As usual, we can’t tell you for certain what’s going to be in the next version, just what we’re working on.”
For now, that list includes hot standby, synchronous replication, covering indexes, permissions management and auto-partitioning, he said.
“Which of these will actually be in the next version is hard to say because we simply won’t ship a feature unless it works correctly and reliably,” he added.
