What do you expect from a software upgrade? Bugfixes? Dozens of new, useful features, all clearly documented? Double or triple performance increases?
The latest version of PostgreSQL provides all of those things, plus an active, growing community of developers, administrators and end-users. The growth of the community is reflected in the breadth and depth of changes in version 8.3, likely to be released January 2008.
This article describes 10 new features system administrators and application developers can immediately use. Each feature includes examples for implementation, and descriptions are meant to be introductions to both PostgreSQL and the database-specific technology implemented. Examples use data from the recent Perl Survey (www.perlsurvey.org).
Because of many improvements in performance of the collector process, statistics collection is now enabled by default. Database statistics are all tracked in the pg_stat_* tables. The pg_stat_* tables are described in section 26 of the PostgreSQL documentation. (http://tinyurl.com/yrxbhr)
One way to identify performance problems is to look for long-running queries. A new column, xact_start, was added to pg_stat_activity to help determine how long a transaction associated with a particular query has been running.
Here is an example query that looks at xact_start, query_start and backend_start:
SELECT datname, procpid, usename, current_query, xact_start, query_start, backend_start FROM pg_stat_activity;
The return values are below, separated by ‘|’, with the headers stripped away for clarity:
test | 5552 | selena | SELECT datname,procpid,usename, \...
Please log in to view this content.
Not Yet a Member?
Register with LinuxMagazine.com and get free access to the entire archive, including:
