MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with propriety database software. MySQL has long been assumed to be the faster but featureless of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.
These assumptions, however, are mostly outdated and incorrect. MySQL has come a long way in adding advanced functionality while PostgreSQL dramatically improved its speed within the last major releases. Many, however, are unaware of the convergence and still hold on to stereotypes based on MySQL 4.1 and PostgreSQL 7.4.
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.
Table A
|
MySQL and PostgreSQL comparison
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).
When to use PostgreSQL
Not many Web developers use PostgreSQL because they feel that the additional features degrade performance. 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
- Transactions
- Use of stored procedures
- Use of geographical data
- R-Trees (i.e., used on indexes)
MySQL is widely popular among various open-source web development packages. The MyISAM engine often is the only database engine included in webhosting providers. Many web developers use MySQL. Thus, MySQL became widely popular in web development, and MySQL calls itself “The world’s most popular open source database,” a claim that may be spurious given the broad deployment of other open source DBMSes such as SQLite.
Thus, MySQL is stereotyped as “easier” to use than PostgreSQL because of its popularity.
Tags:choose, comparison, database replication, mysql, open source development, open source version, performance issues, postgresql, proven solutions, replication, side comparison, sql standards, traditional database applications
you can also grab the RSS feed or Subscribe to Techgurulive by Email
November 5th, 2008 at 3:07 am
good one , covered allmost all , helped me to find out what i was looking for
November 5th, 2008 at 7:41 am
good article ,very organised, and systematic
January 20th, 2009 at 12:37 am
MySQL is faster than PostgreSQL at some things and slower at others. Often, if you write for PostgreSQL like MySQL, it’s slower. If you use the tools PostgreSQL gives you, it can be much faster.
I don’t agree that database design is simpler in MySQL. For example, PostgreSQL has only three string types (char, varchar, and text) and automatically stores data in the most efficient format. I guess a database is “simpler” if you don’t use things like foreign keys. But, an application is simpler if you can rely on the database to enforce integrity.
In my experience, Slony-I is just as reliable as MySQL replication (both good, neither perfect).
PostgreSQL has automatic vacuuming since 8.1, a few years ago.
To be fair to MySQL, it supports a lot of things your chart says it doesn’t, at least partially.
March 11th, 2009 at 6:45 am
Opravil bych autora. Při použití InnoDB i MySQL umí cizí klíče. Běžně je využívám. Faktem ovšem je, že jsem zatím nenašel klienta, který by je uměl nastavit. Možná proto se o nich ve spojení s MySQL moc nemluví.