If you’re planning to pick a free, open-source database for your project, you’ve likely found yourself torn between MySQL and PostgreSQL. Both MySQL and PostgreSQL are free, open-source, powerful, and feature-rich databases. Your main question probably boils down to: Which is the best open-source database, MySQL or PostgreSQL? Which open-source database should you choose?
Choosing a database is a long-term decision, because changing your mind later will be extremely difficult and costly. You want to get it right the first time. The two popular open-source databases, MySQL and PostgreSQL, often end up as the final contenders. A high-level overview of these two open-source databases will help you pick the one that best fits your needs.
MySQL
MySQL is relatively young, first appearing in 1994. It claims to be the world’s most popular open-source database. MySQL is the “M” in LAMP (a software stack for web development that includes Linux, Apache, and Perl/PHP/Python). Most applications built on the LAMP stack use MySQL, including well-known ones like WordPress, Drupal, Zend, and phpBB.
Initially, MySQL was designed to be a fast web server backend, using a fast Indexed Sequential Access Method (ISAM) without ACID support. After a period of rapid early development, MySQL began supporting more storage engines and achieved ACID compliance through the InnoDB engine. MySQL also supports other storage engines, providing the ability to create temporary tables (using the MEMORY storage engine), high-speed read databases via the MyISAM engine, and various other core and third-party engines.
MySQL’s documentation is very extensive, with plenty of high-quality free reference manuals, books, and online docs, in addition to training and support from Oracle and third-party vendors.
MySQL has seen several ownership changes and dramatic events in recent years. It was originally developed by MySQL AB, then sold to Sun Microsystems for $1 billion in 2008, and Sun was subsequently acquired by Oracle in 2010. Oracle supports multiple editions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded, and Community. Some are free to download, while others are paid. Its core code is under the GPL license, with commercial licenses available for developers and vendors who prefer not to use the GPL.
Today, there are even more database options based on the original MySQL code, as several core MySQL developers have released MySQL forks. One of the original creators of MySQL, Michael "Monty" Widenius, seemingly regretted selling MySQL to Sun, and subsequently developed his own MySQL fork called MariaDB, which is free and under the GPL license. The fork Drizzle, created by well-known MySQL developer Brian Aker, has been significantly rewritten and optimized specifically for multi-CPU, cloud, web applications, and high concurrency.
PostgreSQL
PostgreSQL brands itself as the world’s most advanced open-source database. Some PostgreSQL fans claim it rivals Oracle, but without the expensive price tag and arrogant customer service. It has a long history, initially developed at the University of California, Berkeley in 1985 as the successor to the Ingres database.
PostgreSQL is a fully community-driven open-source project, maintained by over 1,000 contributors worldwide. It offers a single, fully-featured version, unlike MySQL which provides multiple distinct community, commercial, and enterprise editions. PostgreSQL is under a liberal BSD/MIT license, allowing organizations to use, copy, modify, and redistribute the code with only a copyright notice required.
Reliability is PostgreSQL’s highest priority. It has a reputation for rock-solid quality and excellent engineering, supporting high-transaction, mission-critical applications. PostgreSQL’s documentation is exceptionally well-done, offering a vast collection of free online manuals, along with archived reference manuals for older versions. PostgreSQL’s community support is fantastic, and commercial support is also available from independent vendors.
Data consistency and integrity are also high-priority features for PostgreSQL. PostgreSQL is fully ACID-compliant and provides strong security guarantees for database access, fully leveraging enterprise security tools like Kerberos and OpenSSL. You can define your own checks to ensure data quality based on your business rules. Among its many administrative features, point-in-time recovery (PITR) is a standout, flexible high-availability feature that provides capabilities like creating hot standby backups, snapshots, and recovery from failure. But that’s not all—the project also provides several methods for managing PostgreSQL for high availability, load balancing, and replication, so you can use the features that fit your specific needs.
Platforms
Both MySQL and PostgreSQL power some high-traffic websites:
MySQL: Slashdot, Twitter, Facebook, and Wikipedia
PostgreSQL: Yahoo uses a modified PostgreSQL database to handle hundreds of millions of events daily, along with Reddit and Disqus
MySQL and PostgreSQL both run on multiple operating systems, such as Linux, Unix, Mac OS X, and Windows. They are both open-source and free, so the only cost to test them is your time and hardware. They are both flexible and scalable, suitable for use on small systems and large distributed systems alike. MySQL pushes further than PostgreSQL in one area: it extends into the embedded field via libmysqld. PostgreSQL does not support embedded applications, sticking firmly to the traditional client/server architecture.
MySQL is generally considered a fast database backend for websites and applications, capable of quick reads and numerous query operations, though it falls a bit short on complex features and data integrity checks. PostgreSQL is a serious, full-featured database for transactional enterprise applications, supporting strong ACID features and many data integrity checks. Both have fast speeds on certain tasks, but the behavior of MySQL’s different storage engines varies greatly. The MyISAM engine is the fastest because it performs very few data integrity checks, making it suitable for backend sites with heavy read operations. However, it can be a disaster for read/write databases containing sensitive data, as MyISAM tables can eventually become corrupted. MySQL provides tools to repair MySQL tables, but for sensitive data, the ACID-compliant InnoDB is a much better choice.
In contrast, PostgreSQL is a completely integrated database with just a single storage engine. You can improve performance by adjusting parameters in the postgresql.conf file, and you can also tune queries and transactions. The PostgreSQL documentation provides a very detailed introduction to performance tuning.
MySQL and PostgreSQL are both highly configurable and can be optimized for different tasks. They both support extensions to add extra functionality.
A common misconception is that MySQL is easier to learn than PostgreSQL. Relational database systems are very complex, and the learning curve for both databases is actually about the same.
Standards Compliance
PostgreSQL aims for SQL compatibility (the current standard is ANSI-SQL:2008). MySQL is mostly compatible with SQL but also has its own extensions, including support for NoSQL features, which are documented in its reference manual. Each approach has its pros and cons. Standards compliance makes database administrators, database developers, and application developers more comfortable, as it means they only need to learn one set of standards, one set of features, and one set of commands. This saves time, boosts efficiency, and prevents vendor lock-in.
Proponents of using non-standard, custom features argue that it allows for rapid adoption of new features without waiting for the standards process to conclude. The ANSI/ISO standards are constantly evolving, so standards compliance is a moving target: well-known relational databases like Microsoft SQL Server, Oracle, and IBM DB2 are only partially compliant with the standard.
Conclusion
Despite different histories, engines, and tools, there is no clear indicator as to which database suits all situations. Many organizations prefer PostgreSQL for its reliability, its proficiency in safeguarding data, and the fact that it’s a community project with no risk of vendor lock-in. MySQL is more flexible, offering more options to tailor it for different tasks. Quite often, for an organization, proficiency with a particular piece of software matters more than feature-based reasons.