I have log been a fan of PostgreSQL over MySQL, believe that PostgreSQL is more feature complete and generally as fast or faster, with obvious caveats about being used appropriately, of course, and not to mention no real comparative testing. Every body gets to have an untested opinion, right?

I did end up doing some performance testing though. What I learned is that both are reasonable fast at simple queries. Great. However opening a new connection to MySQL is much faster than opening a new connection to PostgreSQL. Once the connection is open, but seem equally fast for very simple tests.

Why this matters though is that simple web development in many languages with the most common tools don’t do connection pooling. If you want to just whip up an example PHP program using mod_php, then every page load will result in a new connection. The same goes for mod_python or mod_wsgi (as well as frameworks sitting on top of those plugins). Using each of these common tools with PostgreSQL results in a slow web site. This was driven home when I upgraded from a single 550mhz UltraSPARC II to a dual 1.1Ghz UltraSPARC3 III, and still certain web apps I’ve been tinkering with writing using PostgreSQL for the database are slow.

Certainly there are ways around this. Using a database connection pooling tool for starters, would certainly cure the problem. Also, choosing something that keeps your script running (or a least your database connections open) would also help. Or even writing your application as stand alone program that keeps the database connections open and talks to the web server via JSON-RPC or XML-RPC. But, to quickly whip something out MySQL may be simpler.

Of course, for some applications Sqlite could be a contender. Certainly it is very fast, and very simple to use. For a scalable web site though, it is probably out of the question. There is a reason that Django defaults to using Sqlite first though. And there are also, those less traditional database servers like CouchDB or memcachedb which seem to generally have very fast connection times.

This is a bit disappointing though. AOLServer used to offer connection pooling built into the web server. Of course, I certainly don’t want to use TCL as my development language, but still that would be nice to have.

Meanwhile, can anyone suggest a good Solaris and PostgreSQL connection pooling library?