Great Insights From PGConf 2008

by Ostatic Staff - Jun. 10, 2008

I have been using PostgreSQL for more than 10 years. During that time, people have consistently asked me why I would use it, when MySQL is better known, has a larger community, more support, and a commercial company (formerly MySQL AB, now Sun Microsystems) behind it. Besides, is there really a difference between database servers?

This is one of those questions that can get you into a lot of trouble, the open-source equivalent of a bar fight. I have used MySQL many times over the years, and know how to use it -- but if I'm allowed to choose, then I'll always opt for PostgreSQL. My main reason for this preference is its huge feature list; in almost every way, PostgreSQL offers a superset of MySQL's features. Every table, in every database, has access to transactions, views, subselects, and triggers. You can write server-side functions in a number of languages, from Perl and Python to more esoteric options, such as R. But I also like the fact that I can rely on SQL standards for just about everything, that people are employing PostgreSQL for all sorts of huge databases, and that I can scale databases up to truly ridiculous proportions. The existence of add-ons, such as PostGIS for geographic information, is just icing on the cake.

Many of the best-known PostgreSQL hackers joined together several weeks ago for the annual PGCon, a conference dedicated to all things PostgreSQL. I've finally had a chance to review some of the talks and slides from that conference, and it not only gives me confidence in what PostgreSQL can do today, but also where it is headed in the coming years.

For example, newcomers to PostgreSQL often wonder (and worry) about the large number of configuration options. True, you can ignore most of them when you first start -- but understanding those options, and learning to use and tune them, is an important skill for any PostgreSQL DBA to have. I was thus happy to read through the notes from the three-hour tutorial that Josh Berkus gave. It should be noted that Berkus, along with several other presenters at the conference, work for Sun, which bought MySQL earlier this year. Contrary to many expectations, including my own, Sun continues to fund and encourage PostgreSQL development, remaining true to its word about supporting a variety of open-source solutions.

Anyone who has ever ported an application from one database to another knows that it can be a difficult, and even grueling, experience. I was thus fascinated by what I read in a talk about porting Oracle applications to PostgreSQL. The talk not only described syntactic differences, but also interfaces and extension languages.

Perhaps the most interesting talk, at least for my current interests, was about the issues involved with scaling PostgreSQL to very large databases. True, there are ways to cluster or partition a database to work across multiple servers. But as the presentation pointed out, what used to be seen as a huge database is now a medium-sized or even small one. PostgreSQL is adapting, and the development team is even considering ways to make use of multicore systems for purposes of "vacuum" and index management.

If you use PostgreSQL, then you should check out the conference site. Many of the presentations' slides are available for public viewing, sometimes with additional documents that can make it even easier to understand the content of the talk.