PostgreSQL vs MySQL: An Analysis

Are you considering using either MySQL or PostgreSQL for your next project? Read this article to find out the differences between these two databases and help decide which one to use.

PostgreSQL vs MySQL

Both PostgreSQL and MySQL are free, open-source database management systems. They are often compared to each other because of this.

In this article, you’ll learn what the differences are between PostgreSQL and MySQL. We’ll take a look at many different criteria of each database and examine how they are different.

There’s also a summary table at the end of the article.

 

Object Relational and Relational

PostgreSQL was built to handle both relational databases (tables and rows) as well as object-relational data (document storage or JSON). The features included in PostgreSQL are thorough and more than MySQL.

PostgreSQL also has a JSONB data type, which allows for:

• faster to read
• supports indexing
• slightly slower input due to conversion

MySQL does support JSON data (as of 5.7) and several enhancements have been made in 8.0. MySQL was initially developed only to be a relational database but has been enhanced to include JSON support.

 

Open Source

Both MySQL and PostgreSQL are open source. This means the source code is publicly available and free to use and enhance. Rather than being supported by an organisation if you choose to use Oracle or SQL Server, the open-source alternatives are supported by developers around the world.

PostgreSQL is developed by the PostgreSQL Global Development Group, which is a group of multiple companies and individual contributors. It has its own license called the PostgreSQL license which is similar to the MIT license.

MySQL is owned by Oracle, but the MySQL source code is available under the GNU General Public License. There are also paid versions available.

 

Data Types

PostgreSQL and MySQL both support a range of different data types.

PostgreSQL includes the standard SQL data types, as well as many others:

• boolean
• cidr
• circle
• uuid
• point

You’re also able to create your own data types in PostgreSQL.

MySQL supports the standard SQL data types, as well as the following data types:

• spatial data types (e.g. geometry, point)

 

Indexes

The indexes available in MySQL and PostgreSQL are slightly different.

PostgreSQL has the following types of indexes:

• B-tree
• Hash
• GiST
• SP-GiST
• GIN (inverted index)
• BRIN (Block Range Indexes)

MySQL uses the following types of indexes:

• B-tree
• Hash
• R-trees (for spatial data)
• Inverted lists

PostgreSQL also allows you to create your own type of index. It also allows indexing on JSONB data.

 

ACID and Concurrency

ACID stands for Atomicity, Consistency, Isolation, and Durability. They are a set of properties of database transactions. If a transaction has these properties then it is ensured that no data is lost or corrupted.

Concurrency features allow multiple users to have access to the same data at the same time. If data is committed then many users can see it, but transactions in progress should not be viewed by other users.

Postgres is compliant with the ACID properties and includes concurrency controls.

MySQL is compliant with the ACID properties if the InnoDB storage engine is used. If you use the MyISAM storage engine (which is older), then transactions and ACID compliance are not supported (among many other features). MySQL also supports concurrency controls when using InnoDB.

So, InnoDB on MySQL has similar support for ACID and concurrency to PostgreSQL.

 

Replication

Replication of a database means that data is copied or made available on multiple databases. This means that if the main database is offline, the other databases can operate, resulting in no or minimal downtime.

Both PostgreSQL and MySQL offer replication solutions.

PostgreSQL offers several solution options:

• Shared Disk Failover
• File System Replication
• Write-Ahead Log Shipping
• Logical Replication
• Trigger-Based Master-Standby Replication
• Statement-Based Replication Middleware
• Asynchronous Multimaster Replication
• Synchronous Multimaster Replication

More information on these solutions can be found on the PostgreSQL manual here.

MySQL offers several solution options:

• Binary Log File Position Based Replication
• Replication with Global Transaction Identifiers
• Multi-Source Replication

MySQL supports both types of synchronisation: asynchronous and synchronous. For more information, refer to MySQL’s manual here.

 

Cloud Hosting

You may be interested in hosting your database with a cloud provider, instead of on-premise or with a web hosting company.

Fortunately, both MySQL and PostgreSQL are offered and supported by Amazon, Google, and Microsoft:

• Amazon AWS
• Google Cloud Platform
• Microsoft Azure

There are many other providers that offer hosting for these two databases. I’ve used Heroku for a side project and they offer both MySQL and PostgreSQL.

 

Materialized Views

A materialized view is a view that stores the data from an associated query. The data can then be queried from the view itself rather than the underlying tables. It takes up more room on the database but can speed up queries. It’s a little like a cache for a query.

PostgreSQL includes support for creating materialized views, but MySQL does not.

 

Temporary Tables

A temporary table is a table created for the life of a session. The table is created and data is populated, and the table is destroyed at the end of the session. Temporary tables help with query performance.

Both MySQL and PostgreSQL support temporary tables.

 

Common Table Expressions (CTEs)

A Common Table Expression, or CTE, allows you to give a name to a subquery and use this name in your main query. It’s a great feature to simplify the structure of your query and sometimes improve the performance. It’s a feature available in both Oracle and SQL Server.

Both MySQL (as of v8.0) and PostgreSQL support Common Table Expressions.

PostgreSQL enhances this feature to include a RETURNING clause, which allows you to use data modification queries (Insert, Update, or Delete) in a single query.

 

Constraints

A constraint is a rule that can be placed on columns in a table to ensure the data in the table adheres to certain rules.

PostgreSQL and MySQL support the five main types of constraint:

• Primary Key
• Foreign Key
• Unique Key
• Check Constraint
• Not Null

MySQL only recently added support for the Check Constraint (in v8.0.16). If you are using a version older than that, then the Check Constraint is parsed but not enforced. You won’t get any syntax errors, but any rules in the check constraint will not be enforced.

 

Set Keywords

Set keywords allow you to combine the results of two queries in different ways.

PostgreSQL has several keywords:

• UNION [ALL]: returns the results of both queries
• INTERSECT [ALL]: returns rows in query1 and query2
• EXCEPT [ALL]: returns rows in query1 but not in query2.

MySQL has several keywords:

• UNION [ALL]: returns the results of both queries

MySQL does not include an INTERSECT keyword or the EXCEPT keyword, but there are several ways to achieve this without the keyword.

The keyword ALL can be added to these keywords to ensure duplicates remain (e.g. UNION ALL instead of UNION).

 

Performance

The performance of both MySQL and PostgreSQL is said to be pretty similar. Various tests have been done online with different results pointing to each database.

The thing with performance comparisons is that it’s very dependent on your application, your server setup, and your user base. There are a lot of factors involved in getting a comparison between the performance of MySQL and PostgreSQL. So, I would not consider performance to be a factor.

 

Summary

This table shows the summary of differences for PostgreSQL vs MySQL:

Criteria	PostgreSQL	MySQL
Object Relational or Relational	Both	Primarily relational but has some object features
Open Source	Yes: supported by PostgreSQL Global Development Group	Yes: owned by Oracle
Data Types	Follows SQL standard but includes several others. Can create custom types	Follows SQL standard but includes several others
Indexes	B-tree, hash, GiST, SP-GIST, GIN, and BRIN	B-tree, hash, R-tree, and inverted lists
ACID	Yes	Yes, only when using InnoDB storage
Concurrency	Yes	Yes, only when using InnoDB storage
Replication	Yes, many options available	Yes, many options available
Cloud hosting	Yes, including Amazon, Google, and Microsoft	Yes, including Amazon, Google, and Microsoft
Materialized views	Yes	No
Temporary tables	Yes	Yes
Common Table Expressions	Yes	Yes
Constraints	Yes	Yes (only supports Check Constraint from 8.0.16)
Set Keywords	Union, Intersect, Except	Union

 

Conclusion

I hope this guide to the differences between PostgreSQL and MySQL has been useful for you. Having the right information helps you make better decisions, and while both PostgreSQL and MySQL are similar in many ways, there are several differences between them.