MySQL vs. PostgreSQL

Which is better, PostgreSQL or MySQL? Both are excellent products with unique strengths, and the choice is often a matter of personal preference. Read on for a useful comparison of these two open source database systems.

Actually many people asked, “Which is the best PostgreSQL or MySQL?” The 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.

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.

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. (No doubt, with InnoDB storage engine we can use the transactions as well as foreign key in MySQL 5.0)

I explained here difference as per my knowledge.

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).

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 geographical data
• R-Trees (i.e., used on indexes)

Comparison

The comparsion of the newest, stable and production version of PostgreSQL 8.0 and MySQL 4.1 (MySQL has also development version 5.0).

	PostgreSQL 8.0	MySQL 5.0
Operating System	Windows, more than 2 dozen Unix-like operating systems (Linux, all BSDs, HP-UX, AIX, OS X, Unixware, Netware…)	Linux, Windows, FreeBSD, MacOS X, Solaris, HP UX, AIX, and many others.
ANSI SQL compliance	ANSI-SQL 92/99	Possible; user can run MySQL in more ANSI compatible (ANSI mode)
Performance	Slower	Faster
Sub-selects	Yes	Yes
Transactions	Yes	Yes
Database replication	Yes	Yes
Foreign key support	Yes	Yes
Views	Yes	Yes
Stored procedures	Yes (pl/SQL)	Yes (procedural)
Triggers	Yes	Yes
Unions	Yes	Yes
Full joins	Yes	No planned for 5.1
Constraints	Yes	No Planned for 5.1
Cursors	Yes	Partial (read only)
Procedural languages (PLs)	Yes PL/pgSQL, PL/Tcl, PL/Perl, PL/Python PL/PHP, PL/Java or user defined	Yes Supports stored procedures (persistent modules) languages as defined by ANSI SQL 2003
Vacuum (cleanup)	Yes	Yes by OPTIMIZE TABLE
Different table types	No (PostgreSQL has its own inbuilt table types and doesn’t use any alternative ones)	Yes MyISAM, InnoDB, MEMORY, BerkeleyDB, MERGE, Archive and NDB (Cluster), Federated (InnoDB has additional functionality)
ODBC	Yes	Yes
JDBC	Yes	Yes
Other APIs	Most of languages (i.e. Perl, C/C++, .NET, OLE-DB, Tcl/Tk, Python, PHP, …)	Most of languages
IPv6 support	Yes	No Planning in 5.1
WWW	- Homepage, - Manual, - Features	- Homepage, - Manual, - Features, - What’s new in 5.0,
Installation	PostgreSQL installation	MySQL installation
Download	Download page	Download page