Why choose or not choose PostgreSQL?

Many of our customers ask us this question so we thought we'd lay down our thoughts. The last couple of our articles have been how to do this and that in PostgreSQL, SQL Server, MySQL or having PostgreSQL coexist with an existing SQL Server install. A major reason for that is that in many of our projects we have a choice of what database to choose for a new piece of an application as long as it can play nicely with the existing infrastructure. Our core database competencies are still PostgreSQL, SQL Server, and MySQL with it leaning more toward PostgreSQL each day. We are perhaps somewhat unique in the PostgreSQL community in that Oracle never comes into our equation of decisions (though Oracle and PostgreSQL are perhaps more similar than the others). Oracle is too expensive for most of our clientele so it's a non-issue, and when our clients do have Oracle -- it's thrust upon them by thier ERP/CRM vendor and is essentially off limits to them.

Historically a lot of these new projects we have favored SQL Server over any of the other databases we use for a couple of reasons:

• The clientele we service are predominantly Windows shops to the tune of about 70-80%. These people already have a SQL Server on site and feel very comfortable with it so why install yet another database? The issue of SQL Server cost also isn't a concern for them because they've already invested in the licensing, backup infrastructures, and training to manage their SQL Server boxes.
• The SQL Server security management -- if you are running a Windows shop and assigning permissions based on Windows groups is still much easier with SQL Server. I know PostgreSQL has LDAP/SSPI and all that but most people assign permissions based on Windows user groups not users and they want one place to do that.
• The SQL Server management wizards are nice plus the reporting services, SSIS thrown in. Management tools, maintenance plans are lifesavers for diagnosing problems, doing automated tunining and they are right there and easy to train people how to use. One can argue Reporting Services, SSIS, Maintenance plan wizards may not be the best of the breed and are not really part of the database. The fact they are packaged in means no need to look for other tools. As much as people like to say they like choices, they more value being relieved of the task of making decisions than having a better menu of options. That's why we have approved stacks.
• SQL Server drivers already packaged with Windows. While PostgreSQL ODBC drivers are a simple 5 minute or less install, they still require installation and a scary thing when you are talking about deploying on 500 or more computers.

And of course we've had those clients who are slowly finding out about open source and their first thought is MySQL. Why:

• There are still far more MySQL consultants than PostgreSQL consultants which means a MySQL consultant is more replaceable than a PostgreSQL consultant. Replaceability is the most important criteria for many customers, particularly with new engagements. There is an old saying that goes "If you don't have competition, you don't have a market." This works on a myriad of levels. On the one hand Oracle, DB2, SQL Server, MySQL, PostgreSQL are what I call cut throat competitors. The MySQL, SQL Server, PostgreSQL, or Oracle consultant next door fighting for the same customers as you are is an enemy, but they are all relational and the existence of the competition ensures a vibrant genetic relational pool and more importantly the growth of the customer base.

  In a NoSQL argument -- they shed their differences and unify against the NoSQL common enemy :). Same goes for PostGIS, DB2 Spatial blades, Oracle Spatial, SQL Server 2008/2010 spatial. Most spatial DBAs I know have one favorite pastime in common: comparing battle stories about ArcSDE :). PostGIS very existence depends on the competition it has with its other spatial database brethren. An Oracle Spatial / DB2 Spatial / SQL server spatial consultant has a much better idea of PostGIS merits than a general PostgreSQL consultant and we all have generally the same kind of customer base. The spatial DBAS are probably more close knit and respectful of each other than other competitive groups just because they are a smaller highly specialized group and are more likely to have to cross database boundaries.

  The fact that I can say SQL Server /PostgreSQL can replace one another or that the knowledge in one is same as the knowledge in another is a huge selling point because it means people don't have to be so concerned that there are more SQL Server than PostgreSQL consultants and so on. So the fact you are not unique makes you more attractive. I would go as far as to say if you are very unique, you want to play it down a bit so you don't sound too different from the rest of the pack. Once the sell is made, then you can pull out the uniqueness gun to keep the sale :).

• You want to take advantage of Drupal, Wordpress and all those things you've heard about. Those really aren't supported well on SQL Server. They are supported in PostgreSQL a bit better, but still MySQL owns the lions share in that regard. You need an expert on Drupal or Wordpress to help you out, chances are they don't know anything but MySQL and probably wouldn't touch PostgreSQL or SQL Server with a 10 foot pole.
• MySQL has historically had better support on Windows than PostgreSQL, of course this has changed considerably so is more of a historical footnote with many lingering consequences.

There are a couple of huge reasons why we are choosing PostgreSQL more and more over the others these days or at worst creating infrastructures that are a cross combination.

• A lot of our clients are beginning or thinking about deploying on cloud (or VM) servers for many reasons: to make read-only distributions of their in house SQL Server / PostgreSQL data or for redundancy. PostgreSQL on the cloud is way cheaper than SQL Server on the cloud, and for more advanced queries, PostgreSQL is a much easier drop-in replacement for SQL Server than MySQL is. With the upcoming SQL/MED enhancements in PostgreSQL 9.1 -- it might even be a better datawarehouse tool than any of the others with the ability to better aggregate data from various sources.
• Some of our work involves processing data in PostgreSQL -- e.g doing spatial transformations with PostGIS (that SQL Server 2005 or 2008 can't do), and various other gyrations (not just for spatial but for financial apps and automated data categorization as well) that are made simpler with PostgreSQL array, advanced built-in text manipulation support (like regex,soundex, trigrams, levenshtein etc), and pushing this data back to a SQL Server database which is the database that drives much of the internal business processing. Most of the reason for not pulling out the existing SQL Server system is that it's more cost effective and expedient to push this data back (or using SQL Server's linked query support to query into the PostgreSQL server) than rewrite these applications to use something other than SQL Server.
• With PostgreSQL you own your stack. If you use MySQL you need to be concerned about how you are deploying it and distributing or pay Oracle licensing ransom. Much less concern with SQL Express, but you need to worry about not going over the memory, processor, and database size limits. With PostgreSQL you can completely own it and can distribute it with the rest of your custom stack. This is really critical when you are building a vertical market product that you are deploying at client sites and need to include the cost of the database in your pricing.
• We have a couple of interesting projects we are working on now where being able to deploy quickly, owning your own stack, and having a very extensable system is critical. PostgreSQL is a perfect fit for that set of criteria. Much of this stuff you just can't get on SQL Server or MySQL (e.g. integration with Python, Advance spatial support, deployment on any OS, advanced string support etc) and the subset you can get is usually the newer versions like SQL Server 2008/2010 that many of our clients haven't shelled out money for and not sure if they want to.