What to choose between PostgreSQL and MySQL

What to choose between PostgreSQL and MySQL (Best Opensource Cloud RDBMS offerings)

What to choose, If two products are nearly identical in quality and functionality, we will probably choose the free one, as the primary factor would be the cost, free is very hard to beat!  😇
But, what If both are free :)? We have to consider all parameters and facilities in terms of availability, performance, functionality, support, updates, or additional features between both the systems.

When we need to migrate and modernize an on-prem legacy brown-field database environment, the key factor to be considered is the compatibility of source and proposed target database engine and code conversion complexity. Most of the RDBMS are ~95% compatible for Storage Objects (Tables, Views and Indexes) but code objects compatibility varies anywhere from 60% to 80% and complexity is depending on how much customization or typical code stored in Stored procedures, Functions and Triggers. There are multiple Schema conversion tools including AWS SCT, Azure DMA, GCP DMS, ispirer, migVisor, StarM, DMAP and the most popular and free Ora2PG to assess the source database systems to choose the target database engine.

This blogpost is more to choose between PostgreSQL and MySQL for a green-field database system to be developed/designed with no tag of license costs :)

We are living in a new scale i.e. Hyperscale and in this cloud world, open source databases PostgreSQL and MySQL stand out as the two most popular choices as both are supported by DBaaS systems today in all Hyperscale but PostgreSQL is an edge over MySQL. Lets take Amazon Aurora, is the premier PaaS/DBaaS offering from Amazon, supports both of these databases, but GCP AlloyDB is only PostgreSQL variant. Although both databases offer robust features and share many similarities, deep inside the capabilities, they possess noteworthy differences in the features and functionalities, and one has edge over other RDBMS for specific workloads.

Both PostgreSQL and MySQL are widely used open-source databases that power and suit a variety of real-time applications. MySQL is recognized as the world’s most popular RDBMS, which was created by a Swedish company, MySQL AB, founded by Swedes David Axmark, Allan Larsson and Finnish Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994, and the other side PostgreSQL is often described as the world’s most advanced Object relational database management system (ORDBMS) and the implementation of POSTGRES began in 1986 almost 8 years before the MySQL.

 

Let’s review and compare key factors, functionalities of these TWO most popular open-source RDBMS services (Key differences: PostgreSQL vs MySQL)

MySQL and PostgreSQL are two of the most widely used and service offered open-source relational database management systems. MySQL is known for its speed and ease of use, making it ideal for web applications and read-heavy workloads. PostgreSQL offers advanced features, new data types and extensions by making it suitable for complex queries and transactions. Below are the some of the key differences:

Ø  ACID compliance (Winner: PostgreSQL)

Atomicity, consistency, isolation, and durability (ACID) are database properties that ensure a database remains in a consistent state even after system failures.

MySQL offers ACID compliance only when you use it with InnoDB storage engine or software modules. BTW, PostgreSQL is fully ACID compliant in all variants.

Ø  Concurrency control (Winner: PostgreSQL)

Multiversion concurrency control (MVCC) is an advanced database feature that creates multiple copies of the records of a table, to safely read and update the data in parallel. With MVCC, multiple users can read (SELECT) and modify (DML) the same data simultaneously without locking the table yet preserving data integrity.

MVCC varies by Storage Engine in MySQL. MVCC is fully supported with the InnoDB storage engine, but not supported in the MyISAM storage engine. Other side, PostgreSQL supports MVCC in all variants/configurations.

Ø  Indexes (Winner: PostgreSQL)

Indexes are database objects that can be created for a table to get direct access to specific data rows thus improve the performance. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data.

MySQL supports B-tree and R-tree indexing where as PostgreSQL supports multiple types of Indexes include trees, expression indexes, partial indexes, and hash indexes to fine-tune your database performance.

Ø  Data types (Winner: PostgreSQL)

MySQL is a relational database provides various data types of a typical RDBMS to cater regular business needs , but PostgreSQL is an object-relational database supports to store data as objects with properties, just like in many programming languages like Java. PostgreSQL supports all MySQL data types plus additional data types like geometric, enumerated, network address, arrays, ranges, XML, hstore, and composite to facilitate optimal storage for new data entities and a clear winner.

Ø  Views (Winner: PostgreSQL)

A view is a subset of one or more tables i.e.  an alternative way of looking at the data in one or more tables to enforce better joins or security to restrict access to base tables.

MySQL supports regular views, but PostgreSQL offers advanced view options like materialized views. Materialized views improve database performance for queries repeatedly access same set of data.

Ø  Stored procedures (Winner: PostgreSQL)

Stored procedures are structured query language (SQL) queries or a named collection of SQL statements or control-of-flow language. We can create stored procedures for commonly used functions, and to improve performance.

MySQL and PostgreSQL both support stored procedures, but the versatility of PostgreSQL allows you to call stored procedures written in languages other than SQL.

Ø  Triggers (Winner: PostgreSQL)

A trigger is a stored procedure that runs automatically when a user attempts a specified data modification statement on a specified table to enforce integrity constraints.

MySQL database supports both AFTER and BEFORE triggers for DML statements i.e. the associated procedure will run automatically before or after user modifies the data. In contrast, PostgreSQL supports the INSTEAD OF trigger, so we can run complex SQL statements using functions.

Ø  Ease of Use (Winner: MySQL)

MySQL is relatively easy to install and configure when compare with PostgreSQL.

 

PostgreSQL vs MySQL – what to choose?

Based on the above classification of functionality, we need to choose the right RDBMS. The following factors also play key roles in choosing the right RDBMS.

1.    Workload type

More Selects/READs                –             MySQL,

More DMLs/Inserts                 –              PostgreSQL.

2.    Application scope

PostgreSQL is better suited for enterprise-level applications with frequent write operations and complex queries.

However, MySQL is the best fit to create internal applications with fewer users for the workloads with more reads and infrequent data updates.

3.    Database development experience

MySQL is much simpler and easier to start with or learn, hence it’s more suitable for beginners. My SQL needs less time as it’s simple to set up MySQL database environment.

PostgreSQL, on the other hand, is a bit more complex than MySQL for beginners as PostgreSQL requires more experience to setup and configure the database environment.

 

Final word:

ü  If we need to build relatively a small database system with more reads than writes and to maintain/manage/administer the database with less experienced manpower then, MySQL is the best bet.

ü  If we need to build a complex OLTP database system with frequent DMLs i.e. typically an OLTP system, with workload of generic reads (Not OLAP kind of reports) but frequent writes and moderate experienced DBAs then PostgreSQL is enterprise level RDBMS, oh! no, ORDBMS.