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.

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.

Difference Between MySQL and PostgreSQL

Below is the main difference between PostgreSQL and MySQL:

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.

MySQL vs PostgreSQL Trends

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

PostgreSQL vs MySQL

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

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

Which is better MySQL or PostgreSQL?

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.

Summary

MYSQL is a popular and widely used DBMS system. It is an RDBMS (Relational Database Management System) and works primarily on the relational database model. It makes database administration easier and more flexible. 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. MySQL was created by a Swedish company called MySQL AB 1995. PostgreSQL was released as Postgres95 in -1995 MySQL is a community-driven DBMS system. PostgreSQL has an active community that is accelerating its development.