MySQL and PostgreSQL: which open source database to choose? Which one is better?

Naresh Kumar is a software engineer and enthusiastic blogger, passionate and interested in programming and new things. Recently, Naresh wrote a blog, the open source world's two most common database MySQL and PostgreSQL characteristics of the detailed analysis and comparison.

On update cascade postgresql

If you're going to choose a free, open source database for your project, postgresql update from select you may be hesitant between MySQL and PostgreSQL. MySQL and PostgreSQL are free, update postgresql open source, powerful, and feature-rich databases. Your main question may be: which is the best open source database,postgresql update  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 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 the two open source databases will help you choose the one that best suits your needs.

Postgresql update join

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 lamp (a software package for Web development, including Linux, Apache, and Perl/php/python). Most applications built on the lamp stack use MySQL, including well-known applications such as WordPress, Drupal, Zend, and phpBB.

At first, MySQL's design goal was to become a fast Web server backend, python postgresql update using fast indexed sequence access methods (ISAM) and not supporting acid. After early and rapid development, MySQL began to support more storage engines, and through the InnoDB engine to achieve acid. MySQL also supports other storage engines, providing temporary table functionality (using the memory storage engine), a high-speed read database through the MyISAM engine, and other core storage engines and Third-party engines.

MySQL's documentation is very rich, with a lot of good quality free reference manuals, books and online documentation, and training and support from Oracle and Third-party vendors.

MySQL has undergone a change in ownership and some dramatic events in recent years. It was originally developed by MySQL AB and then sold to Sun Company in 2008 at 1 billion dollars, and sun was acquired by Oracle in 2010. Oracle supports multiple versions of MySQL: Standard, Enterprise, Classic, Cluster, embedded, and community. Some are downloaded for free and others are for a fee. Its core code is licensed under the GPL and has a commercial license for developers and vendors who do not want to use the GPL license.

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

PostgreSQL

PostgreSQL the world's most advanced Open-source database. Some of PostgreSQL's fans say it is comparable to Oracle, and it has less expensive prices 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 version of the full functionality, unlike MySQL, which offers a number of different community, business, and Enterprise editions. PostgreSQL based on the free Bsd/mit license, the organization can use, copy, modify, and redistribute code, and only a copyright notice can be provided.

Reliability is the highest priority for PostgreSQL. It is renowned for its rock-solid quality and good engineering, and supports high business, 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's community support is great and business support from independent vendors.

Data consistency and integrity are also high priority features of PostgreSQL. PostgreSQL, which fully supports the acid feature, provides 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 for failed restores, as well as snapshots and restores. But it's not all PostgreSQL, and the project provides several ways to manage PostgreSQL for high availability, load balancing, and replication, so you can use features that fit your specific needs.

Platform

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 hundreds of millions of events a day, as well as Reddit and Disqus

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 price 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 goes further than PostgreSQL in one area, and that is its tentacles extend to the embedded domain, which is implemented through LIBMYSQLD. PostgreSQL does not support embedded applications, and still adheres to traditional client/server architectures.

MySQL is often considered to be a fast database backend for Web sites and applications, capable of fast reading and a large number of query operations, but not very satisfactory in terms of complex features and data integrity checks. PostgreSQL is a serious, full-featured database for transactional enterprise applications that support strong acid performance and many data integrity checks. Both of them have a fast speed on certain tasks, and MySQL differs greatly in the behavior of different storage engines. The MyISAM engine is the fastest because it performs very little data integrity checks and is suitable for sites with more backend read operations, but 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 the MySQL table, but for sensitive data, InnoDB, which supports ACID properties, is a better choice.

PostgreSQL, by contrast, 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 the query and transaction. The PostgreSQL document provides a very detailed introduction to performance tuning.

MySQL and PostgreSQL are highly configurable and can be optimized for different tasks. They all support adding additional functionality 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 virtually the same.

Standard compatibility

PostgreSQL is designed to implement SQL compatibility (the current standard is ansi-sql:2008). MySQL is compatible with most of the SQL, but it has its own extensions that can support the NoSQL feature, as described in the reference manual. Each approach has advantages and disadvantages. Compatibility standards make database administrators, database developers, and application developers more comfortable, because it means they need to learn a set of standards, set of features, and commands. This saves time, improves efficiency, and does not lock down on specific vendors.

Those who support the use of non-standard customization features are thought to be able to quickly adopt new features without waiting for the standard process to complete. ANSI 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 any of these two databases that can be applied to all situations. Many organizations like to use PostgreSQL because of its reliability, good at protecting data, and a community project that is not stuck in a manufacturer's cage. MySQL is more flexible and offers more options for tailoring to different tasks. Most of the time, for an organization, the proficiency of a particular software is more important than the reason for the feature.