Biggest Obstacle to PostgreSQL Adoption: It is not Database X

We've been fighting to get clients we have thinking of upgrading or creating new apps to also choose PostgreSQL in the process. Here I'll just itemize some of the obstacles we've run into in making the sale. All of these fall under the It is not Database X line item. By database X I mean SQL Server, MySQL, and Oracle and for us in exactly that order. Our obstacle focus is probably a bit different from others since we are consultants to mostly Windows shops or consultants to ISVs who have to sell their applications to U.S. government agencies or units of agencies.

  1. Can our IT Staff support PostgreSQL?

    This is actually more of a concern with MySQL and SQL Server folks because they've been trained to click thru wizards for most things or they use such little of the database that the extra knobs of PostgreSQL scare them off. For the SQL Server folks, we can make things less painful by pointing them at pgAdmin which is enough like SQL Server Management Studio not to be too frightening. SQL Server folks are already used to things like schemas, very granular permissioning, and more advanced SQL constructs that the additional constructs like regex are more of a sell than a detraction.

    One thing that PostgreSQL doesn't have out of the box are Database tuning and Scheduled Job Agent wizards which SQL Server folks have come to depend on a lot. For Database Tuning wizard you give it a set of workload queries,and it makes recommendations on what tables could benefit from more indexes and even creates the index script for you. This missing feature means a PostgreSQL developer needs to be more attune at reading explain plans than a SQL Server developer does. All in all it's a very beneficial skill to have regardless of which database you use.

    For Schedule Job Agent -- pgAgent often fits the bill nicely as a replacement, though it lacks the backup job wizard SQL Server provides.

  2. If I don't like PostgreSQL, can I switch back?

    By this we mean you can write the same SQL statement for PostgreSQL and it will work the same in Database X. This is much easier of a task with SQL Server and Oracle than it is for MySQL since both support many of the advanced ANSI-SQL constructs PostgreSQL supports. With MySQL for an advanced app, you'll be doing so much dumbing down that you'll wonder why you bothered. In the MySQL case, we just respond, your new app requires advanced querying features that MySQL simply lacks and if you insist multiply your development cost by at least a factor of 2.

    Other things you can do to ensure this:

    • Don't create column names with spaces or mixed case and if you have a SQL Server app that you are thinking of migrating to PostgreSQL, get rid of use of [] around column names.
    • stay away from text type when you know a data field should never have more than 255 characters. This screws you not only with database data sharing but also lots of underlying tools that make assumptions about what you can do with text. E.g. in SQL Server you can't group by a text field, nor can you in MS Access if you are using MS Access as a front-end client to your PostgreSQL/Oracle/MySQL database. Not sure about Oracle though I suspect it has similar limitations.
  3. Can I share data with Database X?

    This is a similar issue to the Cross Compatibility. The more you can stay in the confines of what the other databases you have to be neighbors with support, the better your chances of being adopted. So first is at least for the end points you need to share data with don't use unsupported types like ARRAY or Avant Garde types like JSON and be cautious when using timestamp with time zone. This is not to say you can't use these internally, but you should also have a view or something that makes them more palpable to less sophisticated databases.

    Foreign Data Wrappers (FDW) -- though PostgreSQL implementation of FDW has still got some serious wrinkles like not being able to push updates to other databases, query planner smartness, packaging of FDW binaries for various OS, it still serves a lot of this need.

  4. Will this integrate with our Existing User Management? -

    This issue is pretty non-existent for a web application, but more of an issue if your database needs to be accessed by other means. One big selling point that SQL Server has for Windows shops is that it is intimately integrated with Active Directory - you can choose standard security or NT Security. You can define permissions for an Active Directory group and leave the day to day user/add stuff to the less skilled dedicated IT user management group. I believe Oracle IDM does something similar for Oracle.

    Although you can use Active Directory in PostgreSQL using SSPI or GSSAPI Authentication, in practice it ends up not saving any time since you still need to setup the user in PostgreSQL and PostgreSQL user groups so you still need the support from the PostgreSQL Db Admin. This is a hugy for a windows department with hundreds of users and where IT Management roles are very granularly defined.