PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. MySQL is an open-source relational database management system (RDBMS).
They have competed favorably with commercial closed-source database programs for many years. Each has developed a reputation for specific strengths and weaknesses.
MySQL is perceived to be much quicker but offers fewer features. PostgreSQL is believed to have a deeper feature set. Some programmers think of PostgreSQL as being similar to Oracle, and it is often favored by those who know Oracle products.
MySQL has a strong following that likes its speed and ability to create new projects quickly. The truth is that the current releases of both products, MySQL 5.6 and PostgreSQL 9.2 (depending on the distribution version), have plenty of speed, power and dynamic features. Let's take a closer look.
PostgreSQL has developed strong features to boost performance. They include:
- Several indexing functions including functional, index-only scans, partial, functional and multiple-index-counting.
- Asynchronous commit.
- Cost-based optimizer.
- Asynchronous as well as synchronous replication.
Recent releases have added more than 70 new performance enhancements. They are the direct result of a commitment to improve performance by taking out identifiable problem areas. The program can compress and decompress the information as it runs to optimize disk space. Another advanced feature developed recently is the ability to set asynchronous commits for the whole system, or per user or transaction. This lets different kinds of transactions run together without the need to choose in advance which storage engine type to use for each table.
PostgreSQL is very strict in coding--MySQL not so much. While PostgreSQL was created as an academic project made to mimic a huge database, MySQL started out small and has evolved to be big enough to handle large websites.
MySQL is the world’s most widely used database. The name is derived from a girl name My, a daughter of one of the cofounders, Michael Widenius. The source code is available under the GNU GPL (General Public License). The project is now owned and administered by Oracle Corporation. Previous to Oracle's involvement, it was run by a Swedish company named MySQL AB.
MySQL is widely used as part of the LAMP (Linux, Apache, MySQL, PHP) stack of open source programs that form the backbone of many websites on the Internet including:
- Google (excluding Search)
The core MySQL program does not have any Graphic User Interface tools to operate the software or create and maintain databases. There are several front-end programs available to make MySQL easier to use. The official program for this purpose is MySQL Workbench, owned and developed by Oracle but free to use for everyone.
MySQL works on many Windows, Linux and Mac platforms including:
- MAC OS X
- Novell Netware
- SCO Unixware
- Microsoft Windows
MySQL features include cross-platform support, updatable views, SSL support, stored procedures, information schema, query caching, partitioned tables and full text indexing through the MyISAM program.
Should I use MySQL or PostgreSQL?
For new projects, determine if you are going to port to closed software later on. In that case, PostgreSQL is closer to Oracle, and any code made for PostgreSQL will port to Oracle easily. MySQL has moved away from the SQL standard somewhat. PostgreSQL is more reliable because it is ACID (Atomicity, Consistency, Isolation, and Durability) compliant which means queries will maintain data integrity, and return the same output without error.
MySQL is less reliable and not ACID compliant: The way it handles foreign key references, auditing and transactions make it less reliable. MySQL is good if you are thinking you may use code from other open source projects. Since it is widely used in smaller websites, there are plenty of add-ons, plugins and modules to optimize MySQL from popular software like Wordpress, Drupal and Joomla.