PostgreSQL vs MySQL: What's the Difference?

What is MySQL?

MYSQL is a popular and widely used DBMS system. The name is taken from the girl name My who is the daughter of the co-founder Michael Widenius. The source code of MYSQL is available under the GNU GPL. The project is owned and maintained by Oracle Corporation.

It is an RDBMS (Relational Database Management System) and works primarily on the relational database model. It makes database administration easier and more flexible.

What is PostgreSQL?

Postgre is an object-relational database management system (ORDBMS). It was developed at the Computer Science Department in the University of California. Postgres pioneered many concepts.

Postgre is an Enterprise-class relational database system. It is easy to setup and installs. It offers support for SQL and NoSQL. It has a great community which happy to serve you when you are facing issues while using PostgreSQL.

In this tutorial you will learn more about -

• History of MySQL
• History of PostgreSQL
• Why use MySQL?
• Why use PostgreSQL?
• Features of MySQL
• Features of PostgreSQL
• Key Differences Between MySQL and PostgreSQL
• Disadvantages of using MySQL
• Disadvantages of using PostgreSQL
• What is Better?

History of MySQL

• MySQL was created by a Swedish company called MySQL AB 1995
• Sun acquired MySQL AB for 1 billion dollars in the year 2008
• Oracle bought Sun in 2010 and thereby acquiring MySQL
• In 2012, MySQL was forked into MariaDB by founder Michael Widenius under the company Monty Program Ab
• MariaDB replaces MySQL for most distributions in the year 2013
• Monty Program Ab merged with SkySQL- 2013
• SkySQL Ab renamed to MariaDB Corporation- 2014

History of PostgreSQL

• INGRES was developed-1977
• Michael Stonebraker and his colleagues developed Postgres- 1986
• Support for real ACID and PL/pgSQL - 1990
• Released as Postgres95 in -1995
• Re-released Postgres95 as PostgreSQL 6.0 - 1996
• MVCC, GUC, Join syntax Controls and Procedural Language Loader added- 1998-2001
• Version 7.2 to 8.2: Included features like Schema support, Nonblocking VACUUM, Roles and dblink – 2002-2006
• PostgreSQL 8.4 released in 2009
• PostgreSQL 9.0 released in 2010
• NYCPUG (New York City PostgreSQL User Group) joins PgUS (United States PostgreSQL association)- 2013
• PGconf organised-2014

Why use MySQL?

Here, are some important reasons for using MYSQL:

• Supports features like Master-Slave Replication, Scale-Out
• It supports Offload Reporting, Geographic Data Distribution, etc.
• Very Low overhead with MyISAM storage engine when used for read-mostly applications
• Support for Memory storage engine for frequently used tables
• Query Cache for repeatedly used statements
• You can easily learn and troubleshoot MySQL from different sources like blogs, white papers, and books

Why use PostgreSQL?

Main reasons for using PostgreSQL are:

• Offers useful features like Table partitioning, Point in Time Recovery, Transactional DDL, etc.
• Ability to utilize 3rd party Key Stores in a full PKI infrastructure
• Developers can modify open source code as it is licensed under BSD without the need to contribute back enhancements
• Independent Software Vendors can redistribute it without the fear of being "infected" by an open source license
• Users and Roles can be assigned Object level privileges
• Supports AES, 3DES and other data encryption algorithms.

Features of MySQL

• MySQL is a community-driven DBMS system
• Compatible with various platforms using all major languages and middleware
• It offers support for Multi-version concurrency control
• Compliant with the ANSI SQL standard
• Allows Log-based and trigger-based replication SSL
• Object-oriented and ANSI-SQL2008 compatible
• Multi-layered design with Independent modules
• Fully multi-threaded, using Kernel Threads
• Server available in embedded DB or client server model
• Offers Built-in tools for query analysis and space analysis
• It can handle any amount of data, up to as much as 50 million rows or more
• MySQL runs on many varieties of UNIX, as well as on other non-UNIX systems like Windows and OS/2

Features of PostgreSQL

• An active community that is accelerating its development
• Most common alternative to Oracle, DB2 and SQL Server
• Runs on all major OS platforms that you may have
• MVCC supports large numbers of concurrent users
• Extensive indexing for high-performance reporting
• Support for modern applications (XML and JSON)
• ANSI SQL support for transportable skills/code
• Foreign keys support for efficient storage of data
• Table joins and views for flexible data retrieval
• Triggers/Stored Procedures for complex programs and transactions
• Replication for data backup and read scalability

Differences Between MySQL and PostgreSQL

Disadvantages of using MySQL

• Transactions related to system catalog are not ACID compliant
• Some time A server crash can corrupt the system catalog
• No pluggable authentication module preventing centrally managed account
• No support for roles so it is difficult in maintaining privileges for many users
• Stored procedures are not cacheable
• Tables used for the procedure or trigger are always pre-locked

Disadvantages of using PostgreSQL

• The current external solutions require a high learning curve
• No upgrade facility for major releases
• The data need to be exported or replicated to the new version
• Double storage is needed during the upgrade process
• indexes cannot be used to directly return the results of a query
• Query execution plans are not cached
• Bulk loading operations may become CPU bound
• Sparse Independent Software Vendor support

What is Better?

After comparing both we can say that MySQL has done a great job of improving itself to keep relevant, but on the other side for PostgreSQL, you don't need any licensing. It also offers table inheritance, rules systems, custom data types, and database events. So, it certainly edges above MySQL.