It seems that just recently, every DBMS of any importance has undergone a major update. The latest one to add to the list is PostgreSQL – an open source database that many purists regard as the best of the free DBMS offerings out there at present.

8.1 is in fact the second big release of the year, as 8.0 came out in January 2005. The latter was the first release to provide "proper" support for Windows servers – that is, with a proper installer and running as a Windows service. As with prior releases, pretty well every half-decent Unix-like OS and architecture is supported – notable inclusions being AIX, Solaris, HP-UX and a raft of Linux flavours including Debian, Fedora, Red Hat, and SuSE.

Release 8.0 added some important new features in addition to proper Windows support. Tablespaces can now be organised to fit your requirements (so you can put different bits of your world on different partitions, for example), and point-in-time recovery provides the ability to do near-real-time backups. The main change in 8.1 is the switch from users and groups to "roles" for defining access permissions. As with 8.0, there are plenty of performance tweaks (it now uses indexes for MIN() and MAX() calculations, for instance – something you used to have to bodge around) and changes to the underlying operational code.

Installing on a supported Unix platform is a simple case of running a few commands on a shell command line in order to create users and directories and build the software from its source code. You can generally find pre-built packages of Postgres around the web, which makes life easier (Linux types should look at, for instance, where there are already some 8.1 packages).

If you're migrating from an old (pre-8.0) version, there are two fundamental issues to consider. First is that you'll generally have to export the data from the old version's database and then import it into the new: although this is pretty simple, it means a few minutes' downtime. Secondly, the tightening-up of some of the underlying functionality may affect applications you've written previously – for example, whereas an empty string assigned to a floating-point field would previously be treated as a zero, in the new version it'll cause an error. Such incompatibilities are detailed in the release notes, though, so it's just a case of reading the docs and deciding if your world is affected.

As with any DBMS, there are three elements to PostgreSQL: the core server, the client query application, and a set of intermediate packages that provide the link between a client-end application and the database itself. The server just sits there and hums along, taking its configuration information at startup time from a postgresql.conf file into which you enter one-liners that tell the system everything it needs to know – where to put its data directories, how to run the query optimiser, where the log files live, local information such as timezones and date formats, network information, what system resources it's allowed, and so on. Although the config file looks scary, you get a pre-built one with sensible defaults and a bunch of commented-out stuff, so you can just uncomment and tweak the things you need to.

Once up and running, you'll want to throw commands at the DBMS. The in-built way to do this is via a command line, though it's no surprise that you can get more friendly, GUI-based tools that make the client end a lot more friendly such as the freebie PGAdmin and the commercial offering from Navicat.

The third set of components to PostgreSQL is the intermediate widgets for providing the filling in a client-server application. APIs are available for a raft of programming languages, not least Perl, Java, Tcl and C++, and there's a native .NET data provider and a generic ODBC library to deal with anything that doesn't fit one of the specific APIs. Server-side procedures (akin to stored procedures) are supported via interfaces to programming languages such as PHP and Java – an interesting idea, and in some ways better than using a native, proprietary stored procedure language and giving the developer yet another syntax to learn.

I've used PostgreSQL over the years, and have never failed to be impressed. The advent of version 8.x shows that it's undergoing constant improvement, and in terms of support for external packages (such as client-end programming systems) it's up there with both the commercial offerings and the other open source products (the most obvious of the latter being MySQL). Okay, it doesn't ship as standard with quite as many operating systems as MySQL, but don't let this put you off – if's a very good DBMS which is well documented, well-supported and scaleable.


An excellent choice if you don't mind using a non-commercial database, as it combines low cost with decent client-end support.