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.

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.

Do you think PostgreSQL can compete with SQL Server or Oracle? Tell us why or why not in an email.

Reader feedback:

Jared J. writes:

There is a very simple reason that PostgreSQL, MySQL and the like are not yet suitable for enterprise use. It isn't performance (though PostgreSQL is not a speed demon by any measure, MySQL is fast enough); it isn't advanced features like horizontal and vertical partitioning; and it isn't the lack of security features at the server level. It is something more basic.

If you have ever shared living quarters, you are aware that a certain amount of housekeeping is required to keep said quarters usable. No one I know likes to clean a bathroom or a stove but, if left undone, the living space quickly loses utility. It is the same with FOSS database engines: as a means of moving blocks onto and off of disk, developers have put significant thought and effort into their efficiency; but few, if any, have put much thought into backup and restore operations, data transportability or other housekeeping applications. Consider Oracle's Recovery Manager (or even Sybase's Backup Server) -- can anyone point to an analog in the FOSS world? While replication is slowly making itself available, has anyone considered other forms of transport? Consider Oracle's DataPump utility: I would have to write a good bit of Python or Perl to duplicate its ability to transport tablespaces without regard to whether the target is the same -endian architecture. Even mighty Microsoft suffers here in comparison to the old-line database vendors such as Oracle or IBM.

When I can get the same span of control with MySQL that I can with a commercial product, I will consider deploying it. Enabling that span of control will require these bits and pieces of housekeeping software. Otherwise one is investing in a product that will need a significant amount of after-purchase babysitting and scripting to make it at all usable in a large environment. It is difficult and expensive to hire people (even more so to fire them), and corporations are understandably reticent to staff up an 'overhead' function. So, for now, I don't see FOSS databases as a viable alternative.