Some people think PostgreSQL 8.0 is just a backend database for Web sites. They're as well informed as people who think the moon is made of cheese, according to Robert Treat and Jason Gilmore, co-authors of Apress' Beginning PHP and PostgreSQL 8: From Novice to Professional.
In many situations, the authors say, the PostgreSQL open source database management system (DBMS) can be used instead of, or as a complement to, MySQL, Oracle, Microsoft and other enterprise databases.
In this interview, Treat and Gilmore explain how and when PostgreSQL can be used in enterprise environments, as well as why end users have more control with this open source DBMS.
What sets PostgreSQL apart from other databases?
Robert Treat: As all DBMS make an attempt at implementing both the relational model and the SQL standards, there are certainly a number of similarities between PostgreSQL and all of the other database systems available. Over the years, the key differentiators I found are the structure of the PostgreSQL project, the community around it and its BSD licensing. *media.techtarget.com/searchOpenSource/images/spacer.gif
*media.techtarget.com/searchOpenSource/images/spacer.gif *media.techtarget.com/searchOpenSource/images/spacer.gif
Every other major database player has a single controlling company behind it, whether it be a database vendor like Oracle, a technology conglomerate like IBM or Microsoft or even an open source system like MySQL and Ingres. Because of this arrangement, PostgreSQL is the only database system that can offer you a full range of quality support companies, the ability to integrate PostgreSQL into proprietary systems without fee and a transparent process for getting your features added to the next release. This puts you, the end user, firmly in control of your database technology.
Is PostgreSQL a one-trick pony, suitable only for Web apps?
Jason Gilmore: Those unfamiliar with PostgreSQL tend to perceive it, like MySQL, as a database used primarily as a backend for Web sites. No doubt, there is a significant user base relying on PostgreSQL for this purpose, however its applications are much more wide-reaching.
One of the highest-profile instances of PostgreSQL's capability is the registry service provider Afilias, which relies on PostgreSQL to power both the .ORG and .INFO domain databases. As the .ORG and .INFO top level domains (TLDs) collectively account for roughly 9% of total registered domains --according to the Verisign Domain Report of August, 2005 -- we're talking about a rather significant data store, not to mention one that simply must adhere to the highest standards of reliability.
What are some other examples of PostgreSQL's capabilities?
Gilmore: Another interesting PostgreSQL implementation is Apple's Remote Desktop 2, which gathers diagnostic information about Macs deployed on a network, making it available for retrieval through any number of clients, be it the psql command-line tool, through a website or through a desktop application.
But of course, given the focus of our book, I wouldn't feel right not including a great example of a PHP- and PostgreSQL-driven website, Radio Paradise. [It's] an Internet-only radio station boasting over 29,372 users and streaming a large database of diverse music. A client of PostgreSQL services and consulting provider Command Prompt, Inc., the station is presently enjoying a great deal of success, with almost 9,000 listeners presently streaming music.
Can you compare the performance of PostgreSQL versus Oracle, MySQL or SQLServer?
Gilmore: Attempting to draw conclusions as to which database offers the best performance is often akin to debating whether vi [a screen oriented text editor] or emacs is better. (Of course, vi is.) Ultimately, such comparisons are drawn from specific environments and intent.
To be sure, a database just can't dominate every environment and intent. Just search Google for terms like "PostgreSQL MySQL benchmark" for an idea of the raging debates that arise every time a user or vendor attempts to bring a conclusive end to the matter. What is clear however, if from anything else the extraordinary number of high-profile organizations using PostgreSQL (Research in Motion, NOAA, ADP, Ameritrade and Fujitsu all come to mind), that PostgreSQL provides a very capable, not to mention cost-effective solution for many situations.
What sort of in-house skills would an IT organization/staff of any size need to use PostgreSQL? Would the skill set be similar to or less intensive than what's needed for implementing/managing MySQL, Oracle or SQLServer?
Robert Treat: Generally, most organizations find it easier to setup and administer a PostgreSQL database than commercial database systems. There are packages available for many operating systems (like Windows, Linux and Solaris), that make installation a breeze. Also, PostgreSQL tries its best to keep the number of switches and knobs to a minimum so as not to overwhelm new users.
This isn't to say that PostgreSQL doesn't provide a whole lot of functionality under the hood to help administrators obtain extremely high levels of availability and scaleability, but the philosophy of the PostgreSQL developers is that these features should not get in your way if you don't want to use them.
From a developer's standpoint, most developers coming into PostgreSQL get really excited about the complete feature set that PostgreSQL offers, as well as it's adherence to the SQL standards. Where we have seen some developers stumble is when they have to "un-learn" non-compliant tricks they may have learned from other databases, like allowing invalid dates to be accepted into the database. Once you get past these initial minor differences, it usually takes no time at all to get moving on PostgreSQL, no matter what your previous background.
Can you recommend some best practices for IT admins working with PostgreSQL 8.0?
Treat: Some of the key factors for working as a successful PostgreSQL administrator are to study up on the features that PostgreSQL offers you for maximizing performance, such as autovacuum, it's advanced indexing options, and using the explain tool to analyze SQL query performance.
The other suggestion I have is to get to know your operating system. PostgreSQL takes the standpoint that your database system should work with your operating system rather than attempt to re-implement functionality, so if you are going to maintain a PostgreSQL server, you'll want to be comfortable with the operating system it is running on.
