PostgreSQL: The Industrial-Strength DB with the Tricky Name
No matter how you pronounce it, it may bend—but it won't break—under heavy pressure
By Steve Renaker
This is the third in a series of stories on free-of-charge, open-source databases, describing their various attributes and offering a comparison to more conventional products.
Today: PostgreSQL
Great Bridge Software
Norfolk, Va.
Tel: (757) 233-5500
PostgreSQL Organization
History of PostgreSQL Development
PostgreSQL, pronounced "post-grez-Q-L," bills itself as "the most advanced open-source database available anywhere," and that assertion is tough to dispute. Once you get past the tricky name, you'll find that PostgreSQL has advanced features you might not expect in an open-source DBMS, including transactions, triggers, sub-selects, and user-defined types and functions. In this article, I'll take a look at some of PostgreSQL's distinctive features and what they can do for you, and provide some code examples to get your development started.
As compared to its main competitor, MySQL (it's debatable whether "competitor" is an appropriate term when speaking of open-source products, but let's leave that aside for now), PostgreSQL is relatively complex to install and configure. A glance at the PostgreSQL.org Web site reveals the organization's lack of interest in being user-friendly; the site is spare, almost austere, and difficult to navigate. The online Developer's Guide is well-organized and very readable, once you find it, and there's a tutorial with a thorough discussion of how to set up the software and create an initial database.
PostgreSQL's advanced features come with a steep learning curve for the inexperienced, and database developers with more basic requirements may never need to learn how to use its most powerful capabilities, and casual users will probably never miss them. For industrial-strength projects, however, it's essential that the DBMS have rollback and transaction log functionality. A financial services application, for example, would be unthinkable on a system without airtight data integrity in heavy multi-user read-write situations, in which case a simpler system such as MySQL wouldn't fit the bill. Then it's time to look at heavier-duty solutions, such as Sybase, Oracle, or PostgreSQL.
When it comes to performance, developer consensus is that MySQL can't be beat for sheer rapid-fire read-only access. If you've got a bunch of static data on a Web site, for instance, MySQL will serve it up at blistering speeds while using remarkably little system memory. On the other hand, if you find yourself with 200 concurrent users all doing inserts, updates, and deletes, MySQL tends to degrade catastrophically—that is, to stagger and then die—whereas PostgreSQL degrades gracefully. It slows down under stress, but it doesn't give up.
On the surface, PostgreSQL and MySQL look fairly similar. PostgreSQL has some terminology quirks; in the documentation you'll sometimes see database records referred to as "tuples" and tables referred to as "relations." Both PostgreSQL and MySQL have extensive sets of command-line tools for managing administrative tasks, they both have an interactive monitor mode, and they both work nicely with PHP and Perl's DBI module. When you start to get into PostgreSQL's advanced features, however, you'll start to notice some important differences. User-defined functions and triggers are a good place to start.
Developers will appreciate PostgreSQL's flexibility when it comes to creating new functions. While other systems such as Oracle and Sybase speak only one language, with PostgreSQL it's possible to create user-defined functions in a number of languages, including SQL, Perl, C, and PL/pgSQL, a procedural language similar to Oracle's PL/SQL. Here's a simple example of a user-defined function in SQL:
test=# CREATE FUNCTION greet (text) RETURNS text AS '
test'# select ''Hello, '' || $1 || ''!''
test'#' LANGUAGE 'sql';
