PostgreSQL Upgrades Open Source Database Performance Tools

The PostgreSQL open source database project has released version 8.4 with improved administration, monitoring, and SQL features.

With the new release, a database administrator can directly grant or deny permission to users to view a specific column in the database. The need to shield a salary or Social Security number column from most viewers could be done in earlier releases but only through more roundabout methods, said Bruce Momjian, lead integrator of the project.

"It's a much cleaner implementation than forcing the [database administrator] to use Views (a control on viewing database tables) or set rules that limit who can see what," he said in an interview. The addition of column-level permissions brings PostgreSQL more into line with established commercial databases, such as Oracle and IBM's DB2, although Momjian concedes, "We're still playing catch-up" to some of their features.

Nevertheless, Momjian said the recession has spurred download and adoption of open source PostgreSQL and its commercial implementation from EnterpriseDB, Postgres Plus Advanced Server, by firms seeking to stem growth in their database expenses. Momjian is employed as senior database architect by EnterpriseDB.

In the 8.4 release, administrators may invoke new query monitoring tools that summarize statistics on each query, such as which ones execute most frequently and use the most system time. The information is critical for troubleshooting resource hogs among queries. Another tool tells a database administrator how many times a stored procedure is accessed, how many function calls it contains and how long it takes to run, a means of identifying slow procedures.

"I personally consider this very important. I've done a lot of work in this area," said Momjian. Among other things, the 8.4 release includes the ability to terminate a session of database operation as well as cancel an individual query. That power can allow a database administrator to free up a system from a major drag on performance, when more than a single query is slowing down operations.

The 8.4 release allows a database administrator to aggregate windowing functions so that an SQL query can display multiple operations. One query could produce a view of what the management hierarchy is from the CEO down to a particular employee. In the past, that type of query didn't result in one view of the results and had to be assembled separately to produce an organizational chart of the results, Momjian said.

The aggregate function can be used in other ways, such as establishing an average for all members of the sales force in a given period, then reporting how each member varies from that average. It can also be used to establish salary means in a department and report on those above and below the average. Such aggregate reporting in the past required a series of joins in the database. The windowing aggregate function "allows you do to it in one shot," Momjian said.

The 8.4 release allows secure sockets layer certificates to be used to authenticate users, instead of needing to go to a separate identity management system. The database system can validate the certificate against a certificate authority on the Internet, and allow a user to proceed with the correct privilege levels, Momjian said.

The 8.4 release picks up the Dtrace capabilities first launched in Solaris 10 to embed probes in the database system that can report on internal operations. Forty-three probes have been added to the 11 that were previously part of PostgreSQL, allowing administrators to see much more information on how the database is performing and interacting with its operating system. The Dtrace feature works with PostgreSQL running under Solaris 10, OpenSolaris, and FreeBSD Unix. The feature will work under Linux and Apple's OS X operating system later this year, Momjian added.

InformationWeek Analytics has published an independent analysis of the current state of open source adoption. Download the report here (registration required).