When choosing a database, you are making a long-term decision, because changing your decision later would be very difficult and costly. You want to get it right from the start. Two popular open source databases, MySQL and PostgreSQL, are often the last products to be chosen. 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, having first appeared in 1994. It claims to be the most popular open source database, and is the M in LAMP (the package for web development that includes Linux, Apache, and Perl/PHP/Python.) Most applications built on top of the LAMP stack use MySQL, including well known applications such as WordPress, Drupal, Zend, and phpBB, Zend and phpBB, among others.
In the beginning, MySQL was designed to be a fast web server backend that used a fast index sequence access method (ISAM) and did not support ACID. after some early and rapid development, MySQL began to support a wider range of storage engines and implemented ACID with the InnoDB engine. mySQL also supports other storage engines, provides temporary table functionality (using the InnoDB engine), and provides the ability to use temporary tables. MySQL also supports other storage engines, provides temporary table functionality (using the MEMORY storage engine), and implements a read-only database with the MyISAM engine, as well as other core storage engines and third-party engines.
MySQL is well documented, with many good quality free reference manuals, books, and online documentation, as well as training and support from Oracle and third-party vendors.
MySQL has undergone a change of ownership and some dramatic events in recent years. It was originally developed by MySQL AB, then sold in 2008 for $1 billion to Sun, which was acquired by Oracle in 2010. Oracle supports several versions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded, and Community. Some of these are free to download, while others are available for a fee. The core code is licensed under the GPL, and commercial licenses are available for developers and vendors who don't want to use the GPL.
Now, there are more databases available based on the original MySQL code, as several of the core MySQL developers have released offshoots of MySQL. One of the original MySQL creators, Michael "Monty" Widenius, seems to regret selling MySQL to Sun and has developed his own branch of MySQL, MariaDB, which is free and licensed under the GPL. Drizzle, the branch created by well-known MySQL developer Brian Aker, has rewritten it extensively, optimizing it especially for multi-CPU, cloud, and web applications with high concurrency.
PostgreSQL
PostgreSQL touts itself as the world's most advanced open-source database, and some fans of PostgreSQL say it rivals Oracle without the steep price tag 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 completely community-driven open source project maintained by over 1,000 contributors worldwide. It offers a single, fully functional version, unlike MySQL, which offers several different community, commercial, and enterprise editions. PostgreSQL is based on a free BSD/MIT license, which allows organizations to use, copy, modify, and redistribute the code, as long as they provide a copyright notice.
Reliability is PostgreSQL's highest priority. It is known for its rock-solid quality and well-engineered support for highly transactional, mission-critical applications.PostgreSQL's documentation is excellent, with a large number of free online manuals and archived reference manuals for older versions.PostgreSQL's community support is fantastic, and there is also commercial support from independent vendors.
Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports the ACID feature, which provides strong security guarantees for database access, taking advantage 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 administrative features, point-in-time recovery (PITR) is a great feature, a flexible high-availability feature that provides capabilities such as creating hot backups for failed recoveries and snapshots and restores. But that's not all PostgreSQL has to offer. The project provides several ways to manage PostgreSQL for high availability, load balancing, replication, and so on, so that you can use the features that fit your specific needs.
Platforms
MySQL and PostgreSQL both appear on some high-traffic Web sites:
MySQL: Slashdot, Twitter, Facebook, and Wikipedia
PostgreSQL: Yahoo uses a modified version of the PostgreSQL database to handle hundreds of millions of events per day, as well as Reddit and Disqus
MySQL and PostgreSQL both run on multiple operating systems, such as Linux, Unix, Mac OS X, and Windows. they're both open source and free, so the only cost of testing them is your time and hardware. They are open source and free, so the only cost of testing them is your time and hardware. They are both flexible and scalable, and can be used on small systems as well as large distributed systems. one area where MySQL goes further than PostgreSQL is in its reach into the embedded world, which is achieved through libmysqld. PostgreSQL doesn't support embedded applications and still sticks to the traditional client/server architecture.
MySQL is often thought of as a fast database backend for Web sites and applications, capable of fast reads and large numbers of queries, though less so in terms of complexity and data integrity checking. PostgreSQL is a serious, full-featured database for transactional enterprise applications, supporting strong ACID features and many data integrity checks. They are both very fast at some tasks, and the behavior of the different MySQL storage engines varies considerably. the MyISAM engine is the fastest because it performs very few data integrity checks, which makes it suitable for sites with a lot of back-end reads, but is a disaster for read/write databases that contain sensitive data, as MyISAM tables can end up corrupted. MySQL provides tools for repairing MySQL tables, but for sensitive data, InnoDB with its ACID support is a better choice.
In contrast, PostgreSQL is a fully integrated database with a single storage engine. You can improve performance by tweaking parameters in the postgresql.conf file, as well as tweaking queries and transactions, and the PostgreSQL documentation provides a very thorough introduction to performance tuning.
MySQL and PostgreSQL are both highly configurable and can be optimized for different tasks. They both support adding additional functionality through extensions.
A common misconception is that MySQL is easier to learn than PostgreSQL. Relational database systems are both very complex, and the learning curve for both databases is actually about the same.
Standards compatibility
PostgreSQL aims for SQL compatibility (the current standard is ANSI-SQL:2008). MySQL is compatible with most SQL, but has its own extensions to support NoSQL features, which are covered in the reference manual. Each approach has advantages and disadvantages. Standards compatibility makes database administrators, database developers, and application developers more comfortable because it means they only have to learn one set of standards, one set of features, and one set of commands. This saves time, increases efficiency, and doesn't lock them into a specific vendor.
Those who support the use of non-standard customizations feel that they can quickly adopt new features without having to wait for the standards process to complete. ANSI/ISO standards are constantly evolving, so standards compatibility is a moving target: well-known relational databases Microsoft SQL Server, Oracle, and IBM DB2 are only partially standards-compliant. with the standards.
Conclusion
While there are different histories, engines, and tools, there is no clear reference to which of these two databases will work in all situations. Many organizations prefer to use PostgreSQL because it is reliable, is good at protecting data, and is a community project that doesn't get caught in a vendor's cage.MySQL is more flexible and offers more options to tailor it to different tasks. Often, proficiency with a particular piece of software is more important to an organization than the reasons for its features.
MySQL is more flexible, offering more options to tailor it to different tasks.