What is Relational Database?
A relational database is a digital database whose organization is based on the relational model of data, as proposed by E. F. Codd in 1970. The various software systems used to maintain relational databases are known as a Relational Database Management System (RDBMS).
In this article we will be comparing the two well known open-source relational database engines:
MySQL, the most popular open-source SQL database management system, is developed, distributed, and supported by Oracle Corporation. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.
Offshoots of MySQL (also known as forked version of MySQL) are:
PostgreSQL is a powerful, open source object-relational database system. It is also known as most advanced open-source relational database engine. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL is currently used by few of the technology giants like Apple, Red Hat, Juniper Networks, etc.
Databases like IBM Netezza and Amazon RedShift are the forked versions of PostgreSQL. A complete list of PostgreSQL derived forks and rebranded distributions is available on PostgreSQL derived databases.
MySQL v/s PostgreSQL
In the Stack Overflow Developer Survey 2017, MySQL was the most commonly used database engine which was expected. But the results showed that PostgreSQL is much more loved and is wanted by the Developer community as compared to the MySQL.
Here’s the summary of the most commonly evaluated features and abilities between MySQL and PostgreSQL:
|ACID Compliance||MySQL is only ACID compliant when using InnoDB and NDB Cluster Storage engines.||PostgreSQL is completely ACID compliant.|
|SQL Compliance||MySQL is partially compliant on some of the versions (e.g does not support CHECK constraints).
(Refer document for more details)
|PostgreSQL is largely SQL compliant.
(Refer document for more details)
|Concurrency Support (MVCC)||MySQL only has MVCC support in InnoDB.||PostgreSQL has a very efficient MVCC implementation which achieves very high levels of concurrency.|
|Geo-spatial Query Support||Available with OpenGIS extension.||Available with PostGIS extension.
Geo-spatial support of PostgreSQL is far better if compared with MySQL.
|NoSQL Features/JSON Support||MySQL has JSON data type support but no other NoSQL feature. It does not support indexing for JSON.||PostgreSQL supports JSON and other NoSQL features like native XML support and key-value pairs with HSTORE. It also supports indexing JSON data for faster access.|
|Replication methods||Master-master replication
|Security||MySQL implements security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers.||PostgreSQL has ROLES and inherited roles to set and maintain permissions. PostgreSQL has native SSL support for connections to encrypt client/server communications. It also has Row Level Security.|
|Supported on Operating Systems||FreeBSD
|License||Open-source: GPL version 2.||Open-source: BSD.|
|DB Engine Ranking||#2||#4|
Performance is an area that can only be gauged by evaluating the metrics of potential scenarios, as it depends on the pure requirements of the specific user and the nature of the application.
- PostgreSQL is widely used in large systems where read and write speeds are crucial and data needs to validated. In addition, it supports a variety of performance optimizations that are available only in commercial solutions such as Geospatial data support, concurrency without read locks, and so on (e.g. Oracle, SQL Server).
- Overall, PostgreSQL performance is utilized best in systems requiring execution of complex queries.
- PostgreSQL performs well in OLTP/OLAP systems when read/write speeds are required and extensive data analysis is needed.
- PostgreSQL also works well with Business Intelligence applications but is better suited for Data Warehousing and data analysis applications that require fast read/write speeds.
- MySQL is a widely chosen for web based projects simply needing a plug and play database to store and read/write data.
- MySQL performs well when quick setup is needed, such as plug and play systems. It is common for MySQL to underperform when strained by a heavy loads or when attempting to complete complex queries.
- MySQL performs well in OLAP/OLTP systems when only read speeds are required.
- MySQL + InnoDB provides very good read/write speeds for OLTP scenarios. Overall, MySQL performs well with high concurrency scenarios.
- MySQL is reliable and works well with Business Intelligence applications, as business intelligence applications are typically read-heavy.
Below is the performance difference between MySQL v/s MariaDB v/s PostgreSQL (having default configuration) based on different types of queries:
Performance comparison with time taken in millisecond:
Performance comparison with time taken in natural log(millisecond):
This information is based on the benchmarking performed in Ng Heng Lim’s blog.
- Wikipedia: Relational Database
- About MySQL, About PostgreSQL and Featured PostgreSQL Users
- DB-Engines: System Properties Comparison MySQL vs. PostgreSQL
- 2ndQuadrant: PostgreSQL vs MySQL
- Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6
- Ng Heng Lim’s PostgreSQL [9.5.0] vs MariaDB [10.1.11] vs MySQL [5.7.0] year 2016