PostgreSQL vs. SQL Server, Oracle: Enterprise-ready and able to compete

Why are you paying so much in licensing costs and annual maintenance when you could use PostgreSQL for free, and get community support and upgrades for free as well? This is the question that Neil Matthew and Richard Stones pose to smaller companies regarding their less critical applications.

More on PostgreSQL: Why PostgreSQL can best SQLServer, Oracle Face-off: MySQL, PostgreSQL and SQL Server go head to head

Matthew and Stones, authors of Apress' Beginning Databases with PostgreSQL, discuss the advantages and drawbacks of using this open source relational database management system instead of Microsoft SQL Server or Oracle Database Management System. They also explain why the choice of database toolsets dictates database choices.

What are the most compelling reasons to dump or keep Microsoft SQL Server and bring in PostgreSQL?

Neil Matthew: For applications where downtime seriously threatens a company's financial results, executives are always going to want to deal with a big player who has the resources -- both technical and financial -- to 'be there' should things go wrong. The risk involved in the failure of a company's invoice and payment system for any significant period of time is huge. In an emergency, having companies the size of Microsoft or Oracle to call on may significantly mitigate that risk.

For many smaller, less critical applications, ask yourself why you are paying so much in licensing costs and annual maintenance when you could use PostgreSQL for free, and also get community support and upgrades free?

How does PostgreSQL's feature set compare to that of proprietary databases?

Richard Stones: In terms of standard SQL support, it's very good indeed. If a feature is in the SQL92 standard, you can be pretty sure that PostgreSQL is going to support it correctly.

Normally, Neil and I advise developers to stay away from extensions to the SQL standard in any case.

What are the advantages and weaknesses of PostgreSQL?

Stones: Technically, the main disadvantages of using PostgreSQL are in three areas. First, the ability to write functions and stored procedures is somewhat more limited than you would get with Oracle's PL/SQL or Sybase's T-SQL. Unless you are doing some extremely sophisticated work in stored procedures, this is not a major limitation.

Secondly, features for very large databases like table spaces, partitioned tables and highly complicated locking are still strongest in the proprietary databases vendors' offerings; however, PostgreSQL is moving forward in these areas all the time.

Finally, proprietary development tools are stronger. Microsoft particularly has excellent tools, which not surprisingly work best with a Microsoft product set. This toolset advantage does have an effect on the choice of database product.

Matthew: The advantages of PostgreSQL are cost and the ability to look at the source code to understand what's going on. Very few developers will ever make changes to the source or, even better, submit fixes. I do believe the ability to examine the code in order to understand why something doesn't behave as you expect is a great benefit.

Is PostgreSQL capable of competing, feature-wise, with Microsoft SQL Server or Oracle? It is enterprise ready?

Stones: In my opinion, PostgreSQL is enterprise ready. For many uses, PostgreSQL is just as suitable as Microsoft SQL Server or Oracle, but with a big cost advantage. The features that you need 95% of the time are there and work as expected. The underlying engine is very stable and copes well with a good range of data volumes. It also runs on your choice of hardware and operating system, not just whatever some big vendor might insist you buy to run your database .

Neil: Absolutely. Of course, it's not the solution to all database needs, any more than any other vendor's product would be. For a large, multi-terabyte data warehouse you still need a specialized database product with some advanced features, specifically for handling those kinds of data volumes.

What are some situations where PostgreSQL might be used in conjunction with Microsoft SQL Server or Oracle or MySQL?

Matthew: A wide range of needs naturally leads to a choice of solutions. By sticking to standard SQL92 functionality, companies can mix and match their database solutions to best fit the problem while minimizing costs and complexity. You can have large, expensive highly featured products for large complex problems and cost-effective, but still very reliable, products for more everyday needs.

Stones: It's difficult to see why you would use MySQL and PostgreSQL side-by-side. MySQL has historically traded some functionality for performance. For most purposes, you don't need to make that trade, and PostgreSQL performance is more than adequate.