"Go" MySQL and PostgreSQL: which open source database should I choose? Which one is better?

Reprint Address: Http://www.infoq.com/cn/news/2013/12/mysql-vs-postgresql

If you plan to choose a free, open source database for your project, 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 can be very difficult and costly to change the decision later. You want to choose right from the start. 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 appears for the first 1994 years. It claims to be the most popular open source database. MySQL is the M in the lamp (for web development packages, including Linux, Apache, and Perl/php/python). Most applications built on the lamp stack use MySQL, including those that are well-known applications such as WordPress, Drupal, Zend, and phpBB.

At first, MySQL was designed to become a fast Web server backend, using a fast indexed sequence access method (ISAM), which does not support acid. After an 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 temporary table functionality (using the memory storage engine), implements a high-speed read database through the MyISAM engine, and other core storage engines and third-party engines.

MySQL's documentation is plentiful, with a number of good quality free reference manuals, books and online documentation, as well as training and support from Oracle and third-party vendors.

MySQL has undergone changes in ownership and some dramatic events in recent years. It was originally developed by MySQL AB and then sold to Sun for $1 billion in 2008, 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 charged. The core code is based on the GPL license, and commercial licenses are available to developers and vendors who do not want to use GPL licenses.

Now there are more databases to choose from, based on the original MySQL code, because several of the core MySQL developers have already released the MySQL branch. One of the original MySQL creators, Michael "Monty", Widenius seems to regret selling MySQL to Sun, and has developed his own MySQL branch mariadb, which is free, based on the GPL license. The branch drizzle created by well-known MySQL developer Brian Aker has been extensively rewritten, especially for multi-CPU, cloud, network applications and high concurrency.

PostgreSQL

PostgreSQL prides itself on being the world's most advanced open source database. Some of PostgreSQL's fans say it is comparable to Oracle and has less expensive price and arrogant customer service. It has a long history, originally developed in 1985 at the University of California, Berkeley, as a successor to the Ingres database.

PostgreSQL is a fully community-driven open source project maintained by more than 1000 contributors worldwide. It provides a single, full-featured version, unlike MySQL, which offers a number of different community, commercial, and Enterprise editions. PostgreSQL is based on a free Bsd/mit license, and the organization can use, copy, modify, and redistribute the code, only to provide a copyright notice.

Reliability is the highest priority for PostgreSQL. It is known for rock-solid quality and good engineering, supporting high-transaction, mission-critical applications. PostgreSQL's documentation is excellent, provides a large number of free online manuals, and provides an archived reference manual for older versions. PostgreSQL community support is great, and business support from independent vendors.

Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports the acid feature, providing a powerful security guarantee for database access, leveraging 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, a flexible, high-availability feature that provides the ability to create hot backups and snapshots and restores for failed recoveries. But this is not the whole of PostgreSQL, and the project offers several ways to manage PostgreSQL for high availability, load balancing and replication, so you can use features 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 handle billions of events per 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 open source and free, so the only cost to test them is your time and hardware. They are flexible and scalable, and can be used on small systems and large distributed systems. MySQL is going a step further in one area than PostgreSQL, which is that its tentacles extend to the embedded realm, which is achieved through LIBMYSQLD. PostgreSQL does not support embedded applications and remains on the traditional client/server architecture.

MySQL is often thought of as a fast database backend for websites and applications, capable of fast reads and a large number of queries, but is less than satisfactory in terms of complex features and data integrity checks. PostgreSQL is a serious, well-functioning database for transactional enterprise applications that supports strong acid characteristics and many data integrity checks. Both of them have very fast speeds on certain tasks, and MySQL has a much different behavior than the other storage engines. The MyISAM engine is the fastest because it performs very little data integrity checks and is suitable for sites with more back-end reads, but it is a disaster for read/write databases that contain sensitive data because the MyISAM table can eventually become corrupted. MySQL provides the tools to fix MySQL tables, but for sensitive data, innodb that support the acid feature 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, or you can adjust queries and transactions. The PostgreSQL documentation provides a very detailed introduction to performance tuning.

MySQL and PostgreSQL are highly configurable and can be optimized for different tasks. They all support the ability to add additional functionality through extensions.

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

Standard compatibility

PostgreSQL is designed to achieve SQL compatibility (the current standard is ansi-sql:2008). MySQL is compatible with most SQL, but it has its own extensions that can support NoSQL features, as described in the reference manual. There are pros and cons to each of these approaches. Compatibility standards make it more comfortable for database administrators, database developers, and app developers, because that means they only have to learn a set of standards, a set of features, and commands. This saves time, improves efficiency, and is not locked into a particular vendor.

People who support the use of non-standard custom features believe that this can quickly take on new features without waiting for standard processes to complete. Ansi/iso standards are evolving, so standard compatibility is also a changing goal: well-known relational databases Microsoft SQL Server, Oracle and IBM DB2 are only partially compatible with standards.

Conclusion

Although there are different histories, engines and tools, there is no clear reference to which of the two databases can be used in all cases. Many organizations like to use PostgreSQL because of its reliability, good at protecting data, and a community project that is not trapped in a vendor's cage. MySQL is more flexible and offers more options for tailoring to different tasks. Most of the time, it is more important for an organization to be proficient in a particular software than it is for an attribute.