News Stay informed about the latest enterprise technology news and product updates.

PostgreSQL vs. SQL Server: PostgreSQL is right for the Microsoft stack

Is open source database PostgreSQL ready to replace Microsoft's SQL Server in the enterprise? Web application developer and author W. Jason Gilmore tackles the question in this column.

Hasta la revolución!

Any doubt about the growing influence of open source should be erased by Microsoft's recent capitulations in the field. Even the Redmond, Wash.-based software giant is now making friends with open source software vendors like SugarCRM and getting involved, in its own way, in the open source community.

With Microsoft opening the door to open source, let's take advantage and slip as much open source software through it as we can. In this article, I'll investigate the pros and cons of swapping out SQL Server, a component of the Microsoft stack, for PostgreSQL, an equivalent open source alternative. I'll examine whether PostgreSQL offers the features a SQL Server user would require. Consider this a follow-up to my much-read piece, Five reasons why you should never use PostgreSQL. Ever.

But first, some background

As a technologist who has a deep appreciation for technology's facilitating role in business, the philosophical and sociological ramifications of open source software have always been less interesting to me than the profound impact of open source software within the business environment.

Indeed, it seems that many members of the open source community love to choose sides. They're either definitively pro-business or pro-revolution, despite the evidence that success will ultimately hinge on a balanced mixture of the two viewpoints. After all, it's no dirty secret that corporate behemoths such as IBM, Google and Sun have all eagerly embraced, funded and marketed open source projects. And most of the rockstar developers, Linus Torvalds, Greg Stein and Rasmus Lerdorf to name a few, are all (rightfully) paid to work on projects they love.

Likewise, open source projects such as Firefox and Ruby on Rails would have, arguably, never taken off without the efforts of some very savvy marketers. Who knew that sandals and suits could go so well together?

Microsoft kept out of the open source scene for a long time. That made sense. The company has built an empire with the Windows operating system and other proprietary applications. And, by all accounts, Microsoft's sales are stronger than ever; the company recently reported the highest quarterly revenue in its history. The .NET initiative is proving to be a significant success and, despite the delays, the next versions of Windows and Office are both due out in early 2007 and will most likely sell very well.

Then, Microsoft came out with an endorsement of open source software at the Microsoft Technology Summit this past April.

I was one of several active members of the open source and Java community invited to this event. CollabNet Inc. founder Brian Behlendorf, Technorati Inc.'s chief technologist Tantek Çelik, Web 2.0 Central founder Reg Cheramy, and many others were there. We were told about LINQ, IIS 7 and Project Longhorn. The message was clear: Microsoft makes good money selling Windows and Office.

Along the way, however, Microsoft revealed its departure from its lifelong focus on selling its entire stack as one package.

In one session, presenter Douglas Purdy said, "[Longhorn] is going to be the most interoperable platform in the world because we think we can make more money that way." As an example, Purdy cited JBoss and MySQL as acceptable alternatives for Microsoft's respective middleware and database offerings. In a later talk, IIS, ASP.NET and Visual Web Developer 2005 development leader Scott Guthrie and a colleague guided attendees through the process of installing PHP on an IIS 7 Web server.

Ready to replace SQL Server with PostgreSQL?

Taking a cue from Microsoft, I decided to try out PostgreSQL as a replacement for SQL Server. My first step was to determine whether PostgreSQL offers business reporting and replication services. Here's what I found out.

Business reporting services
Old Unix hats tend to frown upon graphical interfaces, having long since designated the command line as the preferred method for interacting with the computer for both the operating system and the applications. Accordingly, PostgreSQL and MySQL sport powerful command-line-based psql and mysql interfaces, respectively. Armed with these interfaces and knowledge of complex SQL statements, you can derive information about any aspect of your data.

Unfortunately, this steadfast disdain for all things graphical has perpetuated a notion that open source databases lack sophisticated business-reporting tools. In reality, the opposite is true. You can buy -- at no cost or very low cost -- several powerful reporting solutions.

If you have already invested in a reporting solution, such as Crystal Reports for example, you might be surprised to know that you can generate PostgreSQL-driven reports in much the same way that you might already be doing with SQL Server. Because Crystal Reports is capable of connecting to a database through ODBC and JDBC, all you have to do is download the desired driver from the PostgreSQL Web site and go from there.

If you are interested in exploring other solutions, one of the most compelling open source reporting tools available today is JasperReports, a Java-based reporting library from JasperSoft Corp. It is capable of producing reports in a variety of formats -- PDF, XML, XLS, HTML and others. Complex reporting requirements such as complicated layouts and localization are also satisfied.

A visual designer called iReport does an excellent job of abstracting the otherwise sticky creation details behind an easy-to-use interface. Figure 1 offers a screenshot of iReport's chart building tool.

Figure 1. Creating charts with iReport.

Given PostgreSQL's ability to interact with popular reporting products such as Crystal Reports and the availability of low-cost solutions such as Jasper Reports, the executive team would not have to forego access to crucial data should the organization move to PostgreSQL.

Replication services

"I hear PostgreSQL is a popular open source database. But can it scale?"

I've had this question posed to me countless times over the years. The answer is yes, it can. And if you don't believe me, ask Afilias Ltd.. Afilias uses PostgreSQL to manage its entire .info Registry. Or ask Apple, the U.S. Department of Labor or Ameritrade, all of whom are prominent PostgreSQL users with obvious scaling requirements.

With that question answered, the immediate follow-up is how. Replication is a common technique employed not only by PostgreSQL but also by other prominent database packages. Replication involves using two or more servers, each possessing a continuously synchronized database. Because each database ideally possesses the same data or at least a very close copy, it's possible to redistribute requests among these servers, thereby decreasing the overall load that would otherwise be assumed by a single server.

While numerous PostgreSQL replication solutions are available, the most prominent is Slony-I. Slony-I is a community-driven replication project supporting a number of versions of PostgreSQL. It uses a trigger-based methodology for propagating table updates. It is capable of promoting a slave to master in the event that the original master fails, cascading replicas, and bringing new slaves online without taking the master temporarily offline. Note that while Slony-I doesn't support synchronous replication, multimaster mode or sporadic synchronization, the developers take such matters into consideration constantly so that a future version could potentially incorporate those features.

The verdict

As evidenced by the number of third-party products referenced throughout the analysis, Microsoft SQL Server offers a much more tightly integrated array of services than does PostgreSQL. But given the open source software mantra of "doing one thing and doing it well," this is to be expected. PostgreSQL has long held its own compared to SQL Server in speed, compliance and price, leaving naysayers searching for other reasons to pooh-pooh the database.

Because the discussion has now turned to debunking PostgreSQL's presumed inability to satisfy enterprise requirements, I hope this short article has helped resolve doubts pertaining to PostgreSQL's reporting and scaling capabilities.

Later installments of this series will compare Apache with IIS 7, ASP.NET with PHP and various other matters that I think will strike your fancy. Remember, this is about the ROI and not La Revolución; therefore, I won't wax lyrical and will instead leave it up to you to determine which is the best solution for your needs.

About the author: W. Jason Gilmore has developed countless Web applications over the past seven years and has dozens of articles to his credit on Internet application development topics. He is the author of three books, including Beginning PHP 5 and MySQL 5: From Novice to Professional, (Apress), now in its second edition, and with co-author Robert Treat, Beginning PHP and PostgreSQL 8: From Novice to Professional (Apress).

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.