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.
This is incredibly insightful and highly informative.
ReplyDelete