Database offers a feature-rich open source alternative
What is it?
PostgreSQL is the leading alternative database to MySQL in the Lamp (Linux, Apache, MySQL, PHP/Perl/Python) open source development and deployment platform.
Where MySQL places a high value on ease of use and takes a minimalist approach to ensure simplicity and fast performance, Postgres focuses on providing rich features and standards compliance.
The open source database market is becoming crowded and big players are getting in on the act, with Ingres (owned by Computer Associates) and Cloudscape, championed by IBM. About 12 companies are involved in PostgreSQL development, including Red Hat, database supplier Pervasive and Fujitsu.
With the release of PostgreSQL 8.0 in January, PostgreSQL is becoming a serious option for enterprise databases. The latest features include Java stored procedures, point-in-time recovery, asynchronous replication and nested transactions.
Where did it originate?
PostgreSQL began in 1986 at the University of California, Berkeley. Its original author was Michael Stonebraker, the creator of the Ingres relational database management system. Postgres means "after Ingres".
Where Ingres was a classic relational database, Postgres began as an exploration of the potential of object/relational technologies. Postgres was repackaged as Illustra, later bought by Informix and integrated into its Universal Server.
What is it for?
PostgreSQL is a credible competitor to proprietary enterprise databases such as Oracle. It is highly scalable, both in the volume of data it can manage and the number of concurrent users it can support. Some installations manage in excess of 4Tbytes of data.
PostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL.
What makes it special?
Its standard library includes hundreds of built-in functions, ranging from basic maths and string operations to cryptography and Oracle compatibility.
Triggers and stored procedures can be written in C and loaded into the database as a library, allowing great flexibility in extending its capabilities.
PostgreSQL includes a framework that allows developers to define and create their own custom data types, with supporting functions and operators.
PostgreSQL 8.0 offers native Windows support.
How difficult is it to master?
Developers will need experience of Structured Query Language to work with PostrgeSQL. There are two-day courses for database administrators.
Where is it used?
PostgreSQL take-up is slower in the UK than elsewhere. In the US in 2004, it was estimated to be the fifth most popular database for new projects.
It is said to be even more popular in Japan, although, being open source, exact user numbers are impossible to obtain.
The open source database is also well established in mainland Europe.
What systems does it run on?
Linux, most versions of Unix, Mac OS X and Windows. There are native programming interfaces for C/C++, Java, Perl, Python, Ruby, Tcl, and ODBC
What is coming up?
PostgreSQL 8.1, in which the new features will be stabilised, is in beta. An open source business intelligence architecture for PostgreSQL is in development. The Bizgres Project will use ETL (extract-transform-load) technologies from Kinetic Networks and a datawarehouse based on PostgreSQL.
Rates of pay
Perl or PHP developers with PostgreSQL skills can earn between £21,000 and £40,000, depending on experience, seniority and the rest of their skills portfolio.
Given PostgreSQL's growing popularity, the UK seems seriously underserved by training companies. However, you can find resources as follows:
This was first published in November 2005