PostgreSQL vs. MySQL: How to Select the Right Open-Source Database - Page 3

MySQL features and functionality

MySQL has the reputation for being the most popular open-source database-a reputation resulting from its legacy of performance and simplicity. From the onset, MySQL was designed to be a fast indexed sequential access method (ISAM) data store for Websites. This type of work load-which is characterized as a read-mostly load with many small queries-has led to features such as a query cache that improves MySQL's performance even further. This concentration on performance has inspired features such as MySQL Cluster, which allows the database to scale beyond a single physical server.

PostgreSQL is not the only open-source database that allows external extensions to add to the functionality of the database. One of the greatest strengths of MySQL is its pluggable storage engines. MyISAM, the default storage engine of MySQL, provides the performance for read-mostly environments, and the InnoDB storage engine provides the transaction robustness necessary for write-intensive applications.

Additionally, there are a number of third-party storage engines such as Brighthouse and DB2 that add even more capabilities to MySQL. This flexibility allows administrators to tune a MySQL instance based on the needs of the individual tables. For example, a read-mostly table such as a country code table can use a MyISAM storage engine, while a transactional table such as a sales order table can use InnoDB.

Although PostgreSQL and MySQL have distinct reputations for supporting specific types of applications, both databases are frequently used across a wide spectrum of applications. For example, PostgreSQL is known for its strength behind transactional enterprise applications, but it is also used to support many Web applications. Conversely, MySQL, the traditionally strong database for Web applications, is also used by applications requiring transactional support. The key is that either option has the flexibility to handle a large range of uses, but one may be a better technical or business choice based on individual circumstances.