Hot skills: PostgreSQL

What is it? Sun may own MySQL, but the company has put more resources into integrating the rival open source database PostgreSQL into its Solaris operating system.

What is it?

Sun may own MySQL, but the company has put more resources into integrating the rival open source database PostgreSQL into its Solaris operating system.

Traditionally PostgreSQL has been regarded as the heavyweight of the two, the Oracle equivalent, in contrast to the cheap and cheerful, SQL Server-like MySQL. PostgreSQL has been the choice for on line transaction processing, MySQL for websites.

PostgreSQL had the reputation of performing poorly in web applications, which was blamed on its feature-heaviness, but performance has improved dramatically in recent releases.

PostgreSQL is also available in two commercial implementations which emphasise its mission-critical capabilities: Greenplum's massively parallel version for data warehousing, and EnterpriseDB's Oracle clone Postgres Plus, which claims 99.99 per cent availability.

IBM and NTT are major investors in EnterpriseDB. The Postgres version of SQL, PL/pgSQL, has always been similar to Oracle's PL/SQL, and the PostgreSQL team and supporting companies have been pushing PostgreSQL to Oracle migration for years.

Even with 24/7 commercial support from Sun, Unisys and other service companies, PostgreSQL can be rolled out at a fraction of the cost of Oracle. Once other factors such as tools and skills availability are included, the cost advantage is less clear, however, and Gartner Group suggests that "total cost of ownership" of open source databases may still be higher than their commercial equivalents.

Where did it originate?

Michael Stonebraker, creator of Ingres, began work on Postgres in 1986 at UC Berkeley, California. An early commercial version of Postgres, Illustra, became part of the Informix Universal Server.

What is it for?

PostgreSQL's "enterprise-class" features include a query optimiser (also recently added by Sun to MySQL), Multi-Version Concurrency Control, point-in-time recovery, asynchronous replication and hot backups. As well as its own PL/pgSQL, PostgreSQL can run stored procedures in Java, C/C++, Perl, Python, Ruby and Tcl. PostgreSQL includes a framework allowing developers to define and create their own custom data types along with supporting functions and operators that define their behaviour. PostgreSQL 8.3, which became generally available last spring, added ANSI standard SQL/XML support.

For administrators, there are a number of graphical alternatives to the psql command line, including phpPgAdmin, based on an interface written for MySQL.

Sun has optimised PostgreSQL to take advantage of Solaris technologies such as native DTrace Probes, Solaris Containers (which allow Linux and other applications to run on Solaris), and predictive self-healing.

What makes it special?

According to analyst Robin Bloor, 2008 was the year that petabyte databases became manageable. Bloor says Greenplum, based on the PostgreSQL engine, is "architected for the petabyte world and can load data at a speed above 4.5 terabytes per hour - which is neat, given that it doesn't just write the data to disk, but actually organises it to enable fast queries".

How difficult is it to master?

You will need a good grasp of SQL. There are two-day retraining courses for experienced DBAs.

Where is it used?

Major users include BASF, Yahoo, Skype and NTT. PostgreSQL is widely used in the public sector in the US and across Europe.

What systems does it run on?

Linux, all major versions of Unix, Mac OSX, and a recent port to Windows with Visual C++ compilation.

What's coming up?

A community-driven PostgreSQL skills certication project is under way.

Rates of pay

Developers and DBAs earn £25,000 to £45,000.


For free resources start here, or for PostgreSQL on Solaris, see here.

Read more on IT technical skills