Pervasive Software's PostgreSQL 8

Once the installation is finished, the installer starts a server configuration tool. This allows you to tweak settings such as the resource usage and query tuning as well as error reporting, logging and server statistics. This is one utility I wish MySQL had by default. Another excellent utility is the pgAdmin tool, which gives you a graphical management interface to the databases and users. If you can create or modify an object with the command-line interface, you can do the same operation with pgAdmin even faster. The GUI is well laid out and very simple to follow.

Beyond basic usage, the latest version has some features that make database management much easier. The first is updated ALTER TABLE support. It is now possible to change the data type of columns without having to re-create the entire table. I created a test table with a column called 'column_name' as type text and was able to change it to an integer with the command 'ALTER TABLE table_name ALTER COLUMN column_name TYPE integer.'

Another revamped feature is the Perl server-side language. Essentially, you can write SQL functions using Perl and then use them later inside queries. For example, let's define the function 'perl_max.' First, we need to tell the server that we want to use Perl on our database by running 'createlang plperl ' from a command prompt. Then, in psql, we enter the following:

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ return $_[0] if($_[0] > $_[1]); return $_[1];$$ LANGUAGE plperl;

We can now use the function perl_max as though it was a built-in function of the server. Obviously, this is a very simple function. But much more complex functions can be easily written to reduce work in the applications that use the database. Other languages such as pgSQL, Tcl, Python and even C are also available for server-side use. This feature is not available for MySQL, which puts PostgreSQL closer to an enterprise product than simply an open-source project.

Tablespaces are a big feature in enterprise-class databases, but until recently there was no support for them in PostgreSQL. Tablespaces are superior to the old setup of manual symlink management of disks and file systems because they improve performance and control over disk usage.

A key new feature in PostgreSQL 8 is Point-in-Time recovery. Instead of backing up the database every night, or every few hours, it is continuously backed up. In this setup, if a crash should occur, it is possible to restore to some known good point in the past or to a specific transaction. This is a wonderful feature that can save many headaches in the event of a disk crash.

Although some people may look at the latest release as just another SQL server, some major improvements make PostgreSQL stand out from both performance and reliability standpoints. If you are looking for a robust SQL server, PostgreSQL is the way to go.