Introduction
This document answers the question that some clients ask: "Why use PostgreSQL (as opposed to MySQL, Oracle, MS-Access, FoxPro, etc.) for web application development?" PostgreSQL is a Relational Database Management System (RDBMS) that Summersault prefers as the back-end data management component for the database driven websites that we develop. As there are other popular choices available, it's good to clarify why we make that particular choice.
It's worth noting right away that the choice of RDBMS is often highly dependent on the parameters and requirements of an individual project. So while we express our preference of PostgreSQL here for the typical Summersault project, we in no way mean to imply that PostgreSQL is the right choice for every project.
Which Databases to Consider?
We'll be comparing PostgreSQL to the other common database server packages that are choices for web application development: MySQL, Oracle, and Microsoft SQL Server.
There are also a number of desktop database packages that some use for web application development; these include Access, FoxPro, FileMaker Pro, and others. While these applications are often inexpensive and user-friendly for simple desktop or workgroup database management, they are rarely suited for server-based application deployment. These points, adapted from About.com, cite some primary reasons for this:
- Desktop database applications usually only support one or a small number of simultaneous users. This makes them unusable for web applications where multiple simultaneous connections are needed.
- Desktop database applications usually have weak security. Most web applications need much more robust security systems than these desktop-based packages provide.
- Desktop database packages are usually not designed for web application deployment or other networked services. Their architecture, scalability, and overall performance are rarely optimized for use in an Internet-based application, making them a liability for serious database driven site development.
Given the above, we won't be including the desktop-based systems in our evaluation, as they are generally not considered suitable options by professionals in our industry, and our experience with them confirms that trend.
Choosing PostgreSQL
We'll address a couple of key factors that one should look at in choosing an RDBMS for a production-quality web application, and why we think PostgreSQL comes out on top for our typical application needs:
- General Advantages
- Features
- Performance
- Commercial Support
- Industry Acceptance / Wide-spread Usage
- Our History and Expertise with PostgreSQL
There are several key advantages of using PostgreSQL:
- PostgreSQL is free, Open Source software
- PostgreSQL has excellent commercial and community support options
- PostgreSQL has legendary reliability and stability
- PostgreSQL is very scalable and extensible
- PostgreSQL is cross platform
- PostgreSQL is designed for high volume environments
- PostgreSQL is easy to administer
Here is a more detailed list of features that are offered by these top RDBMS packages:
| Feature | SQL Server |
Oracle |
MySQL |
PostgreSQL |
| Open Source | X | X | ||
| Free / No License Costs | X | X | ||
| ACID Compliant | X | X | X | X |
| ANSI SQL Compliant | X | X | X | |
| Referential Integrity | X | X | X | X |
| Replication | X | X | X | X |
| Rules | X | X | X | |
| Views | X | X | X | X |
| Triggers | X | X | X | X |
| Unicode | X | X | X | |
| Sequences | X | X | X | |
| Inheritance | X | X | ||
| Outer Joins | X | X | X | X |
| Sub-selects | X | X | X | X |
| Open API | X | X | ||
| Stored Procedures | X | X | X | X |
| Native SSL Support | X | X | X | X |
| Procedural Languages | X | X | X | |
| Indexes | X | X | X | X |
This information is believed to be current as of this writing (Mar 21, 2008) but is not guaranteed to be accurate. Sources include: http://www.postgresql.org/about/advantages, http://www.microsoft.com/sql/, http://www.mysql.com/products/mysql/index.html
Most all of the above features are key for developing robust, scalable web-based applications, and PostgreSQL clearly provides excellent value in this regard.
PostgreSQL, like its companions, will always require tuning and optimizing based on the particular application. And so each of the systems -- SQL Server, Oracle, MySQL, and PostgreSQL -- will be have particular areas of performance where they excel "out of the box".
PostgreSQL is generally optimized and faster than others for scenarios involving high transactional loads, high numbers of users (especially non-read-only applications), and complex queries. Other related features -- like views, ACID compliance, etc. -- also contribute to PostgreSQL's overall performance.
Database benchmarking appears to be an area where little industry-wide data is available, again mostly due to the degree to which each database system can be tuned to meet certain performance expectations.
There exists an extensive network of companies and individuals providing commercial consulting and support for PostgreSQL. One listing of those entities is available at http://techdocs.postgresql.org/companies.php. This is in contrast to proprietary support contracts needed for systems like SQL Server and Oracle.
In addition to the formal support channels, there are a wide variety of mailing lists, support groups, and other online collaborations where one looking for technical support and consultation can turn. Our experience is that any advanced PostgreSQL questions can usually be resolved very quickly at little or no material cost.
5. Industry Acceptance / Wide-spread Usage
With any mission critical application, it's important to ensure that the technologies in use are established within their industry and accepted by others as a trusted name. In addition to PostgreSQL's large network of commercial support (above), it enjoys significant usage by many notable organizations employing it for mission critical or large scale applications. A partial list of those organizations is available at http://techdocs.postgresql.org/techdocs/supportcontracts.php
6. Our History and Expertise with PostgreSQL
One of the most important factors in choosing a database system for a project is the application developer's level of expertise with that system; even the best database system can perform poorly if not used properly. Summersault has been working with PostgreSQL in one form or another since 1996. In addition to using the software heavily, we also make contributions to its growth, including contributioning to software interfaces, user support activities, and more. We've successfully deployed and managed PostgreSQL software on a variety of platforms, and have successfully managed data migration and software upgrade projects in a variety of contexts, particularly related to web application development.
In short, we have the expertise and experience that allows us to use PostgreSQL effectively and efficiently for our clients, saving time/expense and optimizing performance.
Conclusion
As noted, PostgreSQL is not the right choice for every project, but we know that its advanced feature set, extremely low deployment cost, and open source community support make it the best choice for Summersault's database driven website development.
If you have any questions or comments on this document, please feel free to contact us via e-mail: info@summersault.com.
