I’m often asked, “Do you prefer PostgreSQL or MySQL?” My answer is always the same: “It’s a matter of preference.” You could ask many developers the same question, and their responses will all be different. Here is a comparison of MySQL and PostgreSQL databases, offered not for the sake of voicing my opinion, but to help you make your own decision.
Both systems have much to offer in terms of stability, flexibility, and performance. MySQL has features that PostgreSQL lacks, and vice versa. However, my primary focus is to help you determine which of the two databases to use in your own development.
Before getting into the side-by-side comparison, I need to make it clear I’m referring to default installations. MySQL has many different table types that support transactions and foreign keys and are compliant with ACID. However, some of the configurations of these table types are quite complex. Not many Web developers or programmers use the additional table types found in MySQL. With that said, let’s compare these two products.
Table A is a side-by-side comparison of some of the more frequently used features of MySQL and PostgreSQL.
Table A is not an exhaustive list of features, data types, or performance issues relating to these two database systems—it just gives a view of what each has to offer. From the table we see that PostgreSQL offers overall features for traditional database applications, while MySQL focuses on faster performance for Web-based applications. Open source development will bring more features to subsequent releases of both databases.
When to use MySQL
Why would you use MySQL over PostgreSQL? First, we need to consider the needs of the applications in terms of database requirements. If I want to create a Web application and performance is an issue, MySQL will be my choice because it’s fast and designed to work well with Web-based servers. However, if I want to create another application that demands transactions and foreign key references, PostgreSQL is the choice.
As an open source developer, I work with both databases on a regular basis, and I typically use all the features of a given database in the design and development process. It wouldn’t suffice for me to use PostgreSQL for a database-driven Web site when my application requires performance.
Even though MySQL is not fully compliant with the ANSI SQL standard, I should mention that, while PostgreSQL is closer to the ANSI SQL standard, MySQL is closer to the ODBC standard.
Let me suggest some reasons for using MySQL over PostgreSQL:
- MySQL is relatively faster than PostgreSQL.
- Database design will be simpler.
- You can create a basic Web-driven Web site.
- MySQL’s replication has been thoroughly tested.
- There’s no need for cleanups in MySQL (Vacuum).
Learn more about MySQL
When to use PostgreSQL
Not many Web developers use PostgreSQL because they feel that the additional features degrade performance. The article "Many Web developers prefer MySQL" offers a glimpse into the mentality of Web developers and their reasons for using MySQL. However, PostgreSQL offers many advantages over MySQL.
For example, some of the features I use are foreign key references, triggers, and views. They allow me to hide the complexity of the database from the application, thus avoiding the creation of complicated SQL commands. I know many developers who prefer the rich functionality of PostgreSQL’s SQL commands. One of the most notable differences between MySQL and PostgreSQL is the fact that you can’t do nested subqueries of subselects in MySQL. PostgreSQL follows many of the SQL ANSI standards, thus allowing the creation of complex SQL commands.
Let me suggest some reasons for using PostgreSQL over MySQL:
- Complex database design
- Moving away from Oracle, Sybase, or MSSQL
- Complex rule sets (i.e., business rules)
- Use of procedural languages on the server
- Use of stored procedures
- Use of geographical data
- R-Trees (i.e., used on indexes)
When to use both
You’ll have to choose which database is “perfect” for your application or Web site. And you may want to use both. I recently worked for the Quality Assurance Department for Enterasys Networks. One of my responsibilities was to create a database-driven intranet site as a repository for all the QA testing done at our facility. From the outset, I was convinced that PostgreSQL was the only database to use. I was wrong. I ended up using both databases for the intranet site. I used MySQL to handle the entire Web front-end and PostgreSQL to keep track of all the testing metrics that we stored. I found that MySQL and PostgreSQL helped in their own ways. My conclusion is that one isn't necessarily better than the other, but that each one has its place in the world of open source databases.