PostgreSQL vs MySQL – Difference Between Them
Juniper Willow
Key Difference between PostgreSQL and MySQL
- PostgreSQL is an Object Relational Database Management System (ORDBMS), whereas MySQL is a community-driven DBMS system.
- PostgreSQL supports modern application features like JSON, XML, etc., while MySQL only supports JSON.
- Comparing PostgreSQL vs MySQL performance, PostgreSQL performs well when executing complex queries, whereas MySQL performs well in OLAP & OLTP systems.
- PostgreSQL is complete ACID compliant, while MySQL is only ACID compliant when used with InnoDB and NDB.
- PostgreSQL supports Materialized Views, whereas MySQL doesn’t support Materialized Views.
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:
| Parameter | MYSQL | PostgreSQL |
|---|---|---|
| Open Source | The MySQL project has made its source code available under the terms of the GNU General Public License. | PostgreSQL is released under the PostgreSQL license which is free Open Source license. This is similar to the BSD & MIT licenses. |
| Acid compliance | MySQL is ACID compliant only when it is used with InnoDB and NDB Cluster Storage engines. | PostgreSQL is complete ACID compliant. |
| SQL compliant | MySQL is partially SQL compliant. For example, it does not support check constraint. | PostgreSQL is largely SQL compliant. |
| Community Support | It has a large community of contributors who Focus mainly on maintaining existing features with new features emerging occasionally. | Active community constantly improves is existing features while its innovative community strives to ensure it remains the most advanced database. New cutting-edge features and security enhancements regularly released. |
| Performance | It is mostly used for web-based projects that need a database for straightforward data transactions. | It is highly used in large systems where to read and write speeds are important |
| Best suited | MySQL performs well in OLAP & OLTP systems when only read speeds are needed. | PostgreSQL performs well when executing complex queries. |
| Support for JSON | MySQL has a JSON data type support but does not support any other NoSQL feature. | Support JSON and other NoSQL features like native XML support. It also allows indexing JSON data for faster access. |
| Support for materialized views | Supports temporary tables but does not offer materialized views. | Supports materialized views and temporary tables. |
| Ecosystem | MySQL has a dynamic ecosystem with variants like MariaDB, Percona, Galera, etc. | Postgres has had limited high-end options. However, it is changing with new features introduced in the latest version. |
| Default values | The default values can be overwritten at the session level and the statement level | The default values can be changed at the system level only |
| B-tree Indexes | Two or more B-tree indexes can be used when it is appropriate. | B-tree indexes merged at runtime to evaluate are dynamically converted predicates. |
| Object statistics | Fairly good object statistics | Very good object statistics |
| Stack Overflow questions | 532K | 89.3K |
| Join capabilities | Limit join capabilities | Good join capabilities |
| GitHub Stars | 3.34k | 5.6k |
| Forks | 1.6k | 2.4k |
| Prominent Companies using the product | Airbnb, Uber, Twitter | Netflix, Instagram, Groupon |
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
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.
