All Hail PostgreSQL

There is certainly, for better of worse, a pecking order of programmers. Somewhere near the top of that order are the kernel developers. While I certainly wouldn't mind being among this elite group, I probably don't have the requisite level of skill and I certainly don't, at this point in my life, have either the inclination or time to acquire the necessary knowledge. Nonetheless, I admire their skill.

Somewhere further down the pecking order, and hopefully not too near the bottom, are those of us who build business applications using various procedural languages and, for database access, SQL, or Structured Query Language. It's not as glamorous or complex as kernel hacking, where that term in meant in its tradition and non-criminal sense, but given my interest in both programming and application design, I do find it rewarding and appropriately challenging. And, while I have read where some are sharply critical of SQL, I have for the fourteen years of so that I've worked with it found it a remarkably flexible, simple, and expressive language.

While the kernel hackers may get the majority of the software development limelight, one of the projects I led was recongized by CIO magazine as being one of the 50 most innovative intranet projects during the year 2000. The honor of receiving the 50/50 award remains one of the highlights of my life. Additionally, an odd sequence of events unrelated to the project resulted in PostgreSQL becoming my favorite software. Indeed, I am guilty of being a database geek, as charged.

I've been a Unix system administrator since 1991 or so and had started playing with Linux some time in 1998 based on the good things I had read about it. I did not initally have a production use for it, but liked the idea of having a non-production system around on which to further my skills. I was quickly impressed with Linux and soon thereafter replaced the Macintosh hosted Web*Star systems under my control with Apache running on Linux. As noted earlier, I have long had an interest in SQL database systems, so I started searching for open source RDBMS (relational database management systems). I stumbled across a project known as PostgreSQL.

I have a vague recollection of PostgreSQL 6.4 or so being the first version that I attempted to install. I always do installations of critical software from source code and I seem to recall having a lot of difficulty getting the software running. Furthermore, while PostgreSQL even at that time had a reputation for having many advanced features, back in those days it also had a reputation for being somewhat buggy, which is not good in a database system. So, it was fun to play with, but with disappointment I found that Linux raised my expectations for other open source projects higher than the projects could actually achieve. I relegated PostgreSQL to a classification of "interesting, but not ready for prime time."

One of the consistent loves of my life has been the game of soccer, and, after having not played for a number of years after my son was born, I decided in the spring of 1999 to dust off my cleats and play again. By October of that year I had torn the anterior cruciate ligament in my right knee. Due to a related cartilege tear, I needed surgery anyway, so it was easy to decide to have the ACL reconstructed as well. Based on the impending arrival of January 1, 2000 and my role as my organization's Y2K guru (my head was on the chopping block), the surgery was scheduled for the week after the first of the new year. In November of 1999, however, the aforementioned award winning project had been started by persons other than myself. They quickly ran into snags that would have been insurmountable had we stayed with the technology they had chosen for the project. The system required a "real" database backend. Furthermore, since the project needed to be multiuser from the start, it was decided that it would utilize a Web-based frontend. At the time, I was the only person in the organization with expertise in both of these technologies, thus I became the new project leader.

My initial instinct was to develop the application using an Oracle backend as I had done on many previous projects. The problem was, however, that my knee surgery was going to keep me out of the office for four to five weeks and, while it could be accommodated, we would have had to open up security holes to allow me to access the Oracle database remotely to do the type of development I was attempting. Thus, I decided to start the project in PostgreSQL, the progress of which I had been following, as the developers had previously released v6.5.3 with the proclamation that they had finally achieved mastery of the source code. I could not use Oracle on any of the machines I had in my home, but I could certainly get Linux and PostgreSQL going. The application was critically needed, though, so we decided to initally run development versions of the project on PostgreSQL in a production environment as I added functionality as needed from home while I was recuperating. However, upon regaining my mobility and returning to the office, I planned to port the application to Oracle so as to get it on to a "stable" foundation for the long term.

Developing an application as others are using it in a production environment violates not only the learned experience of years of business software development, but common sense as well. Nonetheless, as noted earlier, there was no chance of being successful with the originally chosen technology. Similarly, the delivery timeframe could not be moved. Nonetheless, before leaving to have knee surgery, I delivered enough working code and functionality that people were able to begin using the system. In the next couple of months, a significant part of which found my recuperating on my living room couch, I managed to deliver build after build of new functionality with only one build that I can recall really "messing things up". The process was almost completely wrong, but the result was overwhelmingly right.

In the meantime PostgreSQL seemed to be mostly chugging along nicely. We did have one report of data loss, though I could not ascertain whether or not that was user error or a database problem. Regardless, I was impressed by how well PostgreSQL was holding up.

At the beginning of February, 2000 my physical therapist approved my first venture back into the real world by giving me the okay to travel down to LinuxWorld in New York City for a day. The dot com frenzy was still in full force, the Internet stock market bubble had yet to pop, and newly created open source companies had resources far beyond what made rational sense. But, rationality had taken a vacation, so life was good and I very much wanted to experience the frenzy first hand, so I gathered my crutches and my father and we boarded a train for New York.

There were a handful of things I wanted to see at LinuxWorld, chief among them was the "booth" of Great Bridge, a company formed with the intention of growing a RedHat-type business around PostgreSQL. They had been nice enough to send me a PostgreSQL t-shirt as a gift for completing a survey, so I donned it for the day with the intention of stopping by and saying thanks. I also knew that some of the core developers were employed by Great Bridge, so I thought perhaps I might have the opportunity to say "thanks" to them as well.

Upon approaching their exhibit, I immediately recognized Bruce Momjian from pictures I had seen of presentations he had made about PostgreSQL. He was alone, so I wandered over to let him know of the success I was having with the product and how impressed I was with the difference between 6.4 and 6.5.3. We talked for a few minutes, during which time he acknowledged how critical the contributions of Tom Lane had been to their new-found stability. He also pointed out Tom, who was about twenty feet away, and suggested that he, too, would be appreciative of my comments. Indeed he was, and he asked if I had been using v7.0, which was in beta at the time. I responded that I was not in the habit of installing beta software on production systems, but he insisted that he considered the v7.0 beta to be more dependable than the v6.5.3 production release, so I promised to install it and give it a try.

After testing the beta on my development server for just a bit, I put it into production use. It ran great and addressed a handful of issues that had concerned me about v6.5.3. Any thought of swapping out PostgreSQL for Oracle were abandoned shortly after PostgreSQL 7.0 was declared a production release.

Since that time I have not started a single new project in Oracle, though I still maintain a number of Oracle-based applications. I have, however, started many new projects using PostgreSQL and have not regretted that decision even once. The pace at which PostgreSQL is developing exceeds that of any other technology I have ever used. And, while I have knowingly "worked around" functionality I had become accustomed to in Oracle that was missing in PostgreSQL, at this point in time the core developers have addressed all those deficiences while simultaneously greatly increasing performance and reportedly improving stability. I say "reportedly" as I have not had a single stability issue with PostgreSQL since the release of v7.0.

While all of the improvements that PostgreSQL has seen over the past few years are most impressive and appreciated, the aspect of the PostgreSQL project I find most compelling is the respect the development team displays for each other and their user community, their willingness to provide assistance and guidance, and the level of honesty, decency, and integrity with which they conduct their stewardship of the project. In an age where spin and marketing-speak has become the norm, the PostgreSQL project is a remarkable breath of fresh air and it gives me pride to consider myself a small part of the community.

In the future, I hope to find time in my schedule to dust off my C programming skills and contribute code to the project. I have a couple of PL/PGSQL itches I would like to scratch and will do that if I can take some better control of my time. In the interim, I will continue to assist by building pre-release versions of PostgreSQL on three different platforms and reporting problems. In fact, some twenty minutes ago I noticed that my compile of 7.4beta2 finished, so I have some new software to install and test. If you build applications which require a relational database management system and have not looked at PostgreSQL lately, I strongly urge you to consider spending a little time "kicking the tires". I suspect that you will like what you find.