MySQL and PostgreSQL: Which open source database should I choose

If you plan to choose a free, open-source database for the project, then you may be hesitant between MySQL and PostgreSQL. MySQL and PostgreSQL are free, open-source, powerful, and feature-rich databases. Your main question may be: Which is the best open-source database, MySQL or PostgreSQL? Which open-source database should I choose?

When choosing a database, what you do is a long-term decision, because it will be very difficult and costly to change the decision later. You want to choose the right one from the beginning. The two popular open source databases MySQL and PostgreSQL are often the last products to choose. A high-level overview of these two open source databases will help you choose the one that best suits your needs.

MySQL

MySQL is relatively young and first appeared in 1994. It claims to be the most popular open source database. MySQL is the M in LAMP (a software package for web development, including Linux, Apache, and Perl/PHP/Python). Most applications built on the LAMP stack will use MySQL, including those well-known applications, such as WordPress, Drupal, Zend, and phpBB.

In the beginning, MySQL was designed to be a fast Web server backend, using a fast index sequence access method (ISAM), which does not support ACID. After early and rapid development, MySQL began to support more storage engines, and achieved ACID through the InnoDB engine. MySQL also supports other storage engines, provides the function of temporary tables (using the MEMORY storage engine), and implements a high-speed read database through the MyISAM engine, in addition to other core storage engines and third-party engines.

MySQL documents are very rich, there are many good quality free reference manuals, books and online documents, as well as training and support from Oracle and third-party vendors.

MySQL has experienced ownership changes and some dramatic events in recent years. It was originally developed by MySQL AB, and then sold to Sun in 2008 for $1 billion, and Sun was acquired by Oracle in 2010. Oracle supports multiple versions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded, and Community. Some of them are free to download, while others are paid. The core code is based on the GPL license, and there are commercial licenses available for developers and manufacturers who do not want to use the GPL license.

Now, based on the original MySQL code, there are more databases to choose from, because several core MySQL developers have released the MySQL branch. Michael “Monty” Widenius, one of the original MySQL creators, seemed to regret selling MySQL to Sun, so he developed his own branch of MySQL, MariaDB, which is free and licensed under the GPL. Drizzle, a branch created by the well-known MySQL developer Brian Aker, has rewritten a lot of it, especially optimized for multi-CPU, cloud, network applications and high concurrency.

PostgreSQL

PostgreSQL advertises itself as the most advanced open source database in the world. Some fans of PostgreSQL say that it is comparable to Oracle, and it does not have such an expensive price and arrogant customer service. It has a long history, originally developed at the University of California, Berkeley in 1985 as a successor to the Ingres database.

PostgreSQL is a community-driven open source project, maintained by more than 1,000 contributors around the world. It provides a single full-featured version, instead of providing multiple different community, business, and enterprise versions like MySQL. PostgreSQL is based on a free BSD/MIT license, and organizations can use, copy, modify, and redistribute code, just providing a copyright notice.

Reliability is the highest priority of PostgreSQL. It is known for its rock-solid quality and good engineering, supporting high-transaction, mission-critical applications. The PostgreSQL documentation is very sophisticated, providing a large number of free online manuals, as well as archived reference manuals for older versions. PostgreSQL’s community support is great, as well as commercial support from independent vendors.

Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports the ACID feature, it provides a strong security guarantee for database access, and makes full use of enterprise security tools, such as Kerberos and OpenSSL. You can define your own checks to ensure data quality according to your business rules. Among the many management features, point-in-time recovery (PITR) is a great feature. This is a flexible and highly available feature that provides capabilities such as creating hot backups for failure recovery and snapshot and recovery. But this is not all of PostgreSQL. The project also provides several methods to manage PostgreSQL to achieve high availability, load balancing, and replication, so that you can use the functions that suit your specific needs.

Platform

Both MySQL and PostgreSQL appear on some high-traffic Web sites:

• MySQL: Slashdot, Twitter, Facebook and Wikipedia
• PostgreSQL: Yahoo uses a modified PostgreSQL database to process hundreds of millions of events every day, as well as Reddit and Disqus

Both MySQL and PostgreSQL can run on multiple operating systems, such as Linux, Unix, Mac OS X and Windows. They are all open source and free, so the only price when testing them is your time and hardware. They are all flexible and scalable, and can be used on small systems and large distributed systems. MySQL is one step further than PostgreSQL in one field, that is, its tentacles extend to the embedded field, which is achieved through libmysqld. PostgreSQL does not support embedded applications and still sticks to the traditional client/server architecture.

MySQL is generally considered to be a fast database backend for websites and applications. It can perform fast reading and a large number of query operations, but it is not very satisfactory in terms of complex features and data integrity checks. PostgreSQL is a serious and well-functioning database for transactional enterprise applications, supporting strong ACID features and many data integrity checks. Both of them are very fast in certain tasks, and the behavior of different storage engines of MySQL is quite different. The MyISAM engine is the fastest because it only performs few data integrity checks and is suitable for sites with many back-end read operations, but it is a disaster for read/write databases that contain sensitive data because the MyISAM table It may eventually be damaged. MySQL provides tools to repair MySQL tables, but for sensitive data, InnoDB that supports ACID features is a better choice.

In contrast, PostgreSQL is a fully integrated database with a single storage engine. You can improve performance by adjusting the parameters of the postgresql.conf file, and you can also adjust queries and transactions. The PostgreSQL documentation provides a very detailed introduction to performance tuning.

Both MySQL and PostgreSQL are highly configurable and can be optimized for different tasks. They all support adding additional features through extensions.

A common misconception is that MySQL is easier to learn than PostgreSQL. Relational database systems are very complex, and the learning curve of these two databases is actually similar.

Standard compatibility

PostgreSQL aims to achieve SQL compatibility (the current standard is ANSI-SQL:2008). MySQL is compatible with most SQL, but there are also its own extensions that can support NoSQL features, which are introduced in the reference manual. Each method has advantages and disadvantages. Compatible standards will make database administrators, database developers, and application developers more comfortable because it means they only need to learn a set of standards, a set of features, and commands. This will save time and improve efficiency, and will not be locked in a specific manufacturer.

People who support the use of non-standard custom functions believe that this way new features can be quickly adopted without having to wait for the standard process to complete. The ANSI/ISO standard is constantly evolving, so standard compatibility is also a goal of change: the well-known relational databases Microsoft SQL Server, Oracle and IBM DB2 are only partially compatible with the standard.

In conclusion

Although there are different histories, engines, and tools, there is no clear reference to indicate which of the two databases is suitable for all situations. Many organizations like to use PostgreSQL because of its reliability, good at protecting data, and it is a community project that will not fall into the prison of the manufacturer. MySQL is more flexible and provides more options for tailoring to different tasks. Many times, for an organization, proficiency in the use of software is more important than the reasons for the characteristics.

Thanks for reading this.

Pankaj K.(www.pankajconnect.com)