PostgreSQL vs. MySQL — which is best for me?

PostgreSQL and MySQL are two of the most widely used open source database management systems. But what exactly are their differences and similarities and which of the two tools is best suited for different deployment scenarios?

PostgreSQL or MySQL?

Both PostgreSQL and MySQL are databank management systems (DBMS). For the most part, the systems tend to be more similar than different. However, a comparison of PostgreSQL vs. MySQL highlights some subtle differences that may or may not be beneficial to your project. 

Tip

Find out more about the two systems in our detailed articles:

Both systems use the SQL programming language as a central interface for interaction with databases and their contained data. The best known is probably the SELECT statement for executing queries. This allows data to be found and output within the database. Furthermore, there are various SQL commands to control the DBMS.

The functional scope of SQL is defined in various standards. Common implementations cover the standard quite well. When comparing PostgreSQL vs. MySQL, PostgreSQL is more powerful and supports a wider range of functions than MySQL.

Tip

Not familiar with SQL? Check out this introduction to SQL.

An important difference between PostgreSQL and MySQL is their basic architecture. Both systems are relational database management systems (RDBMS). However, PostgreSQL can do a lot more, because it’s an object-relational DBMS (ORDBMS).

Both DBMSs are based on a storage engine as an interface for storing data on physical data carriers. For high-performance access, indexes are used that refer to the individual database entries. There are various storage engines and index methods, each with their advantages and disadvantages.

Tip

PostgreSQL and MySQL are open source database management systems which makes them different from proprietary products of large providers such as Microsoft and IBM. In addition, there are many other open source DBMSs. Find out more about the most important open source databases in our comparison.

MySQL — the classic open source RDBMS

MySQL was developed in the mid-1990s by MySQL AB in Sweden. The company was acquired by Sun Microsystems in 2008 and by Oracle in 2010. Because of a mistrust of the open source community surrounding Oracle, “MariaDB” became the commercial fork of MySQL. This was to ensure that the project remained under an open source license.

During the boom years of the World Wide Web, MySQL became a popular component of the ubiquitous LAMP stack. The database software is now added alongside Linux, Apache and PHP as part of most web hosting plans. MySQL soon became the standard for web projects based on relational databases.

PostgreSQL — the powerful object-relational alternative

PostgreSQL was originally conceived as “Postgres”, a successor to the DBMS Ingres. It was developed in the mid-1980s at the University of California, Berkeley. The code was published under the “Berkeley Software Distribution” (BSD) license. In the mid-1990s, the switch to SQL as a uniform interface took place alongside the name change to “PostgreSQL”. Both names are still used today.

In the words of IBM, PostgreSQL is:

Quote

“One of the most compliant, stable and mature relational databases available today and can easily handle complex queries.” — Source: www.ibm.com/cloud/blog/postgresql-vs-mysql-whats-the-difference

A comparison of PostgreSQL vs. MySQL

Both PostgreSQL and MySQL simplify working with relational databases. Both systems understand SQL commands to create, modify and fill tables, and execute queries. A comparison of PostgreSQL vs. MySQL shows basic differences in their functionality which are also apparent from their architectures.

While MySQL is a pure relational databank management system (RDBMS), PostgreSQL is an object-relational DBMS (ORDBMS). PostgreSQL supports a range of concepts known from object-oriented programming. These include user-defined data types, combined data types and inheritance. PostgreSQL is more powerful than MySQL, but also more complex.

DBMS feature

PostgreSQL / ORDBMS

MySQL / RDBMS

More data per field

Arrays supported

Requires separate table and join

m:n relation

Directly moldable

Requires additional table and join

Inheritance

Directly moldable

Requires complex solution/views, multiple tables, etc.

Hierarchical data

Via JSON, HStore and XML

JSON only

Boolean values

Custom data type

Implementation as TINYINT(1)

Data types

Data types are the foundation for solid database design and the productive use of a database. When designing tables, you specify the type of data contained in individual columns.

Data type

PostgreSQL

MySQL

Boolean values

PostgreSQL knows its own Boolean data type.

MySQL takes a detour. Instead of implementing Boolean values as their own data type, Booleans are stored as TINYINT(1) type numbers.

Ranges

PostgreSQL provides wide support for range types, which simplifies working with ordinal values.

MySQL has no support for ranges on board; if you need them, you have to make do with self-made alternatives.

Geodata

PostgreSQL has the open source PostGIS extension, which is considered one of the most mature GIS implementations.

MySQL supports geodata and the associated queries since version 8. However, the range of functions is smaller than with PostgreSQL.

Arrays

PostgreSQL supports arrays as ORDBMS. PostgreSQL arrays allow to store multiple values in one field.

MySQL does not support this data type.

Hierarchical Data / JSON

PostgreSQL supports JSON as a data type. This allows a complex, hierarchically nested data structure to be accommodated in a single field.

MySQL also supports JSON as a data type, but it is not as powerful as PostgreSQL.

Performance

Database performance is a complex topic. Different DBMSs have certain advantages and disadvantages depending on usage. In general, MySQL is considered extremely performant, especially when the database is accessed “read-heavy”, i.e. in read mode. This is the case with content management systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, PostgreSQL often delivers better performance for write-heavy operations. Furthermore, the ORDBMS is better suited for data warehousing solutions and other systems for “Online Analytical Processing” (OLAP). PostgreSQL supports multiple connections but has higher memory requirements.

Database performance is a complex topic. Different DBMSs have certain advantages and disadvantages depending on usage. In general, MySQL is considered extremely performant, especially when the database is accessed “read-heavy”, i.e. in read mode. This is the case with content management systems like WordPress, which mainly read content from the database and deliver it to visitors.

Unlike MySQL, PostgreSQL often delivers better performance for write-heavy operations. Furthermore, the ORDBMS is better suited for data warehousing solutions and other systems for “Online Analytical Processing” (OLAP). PostgreSQL supports multiple connections but has higher memory requirements.

Security and availability

Relational DBMSs ensure the consistency and availability of stored data. This is also referred to as the “ACID” properties. PostgreSQL supports the ACID properties; with MySQL this depends on the storage engine used.

The situation is similar regarding “Multiversion Concurrency Control” (MVCC), which ensures data consistency in the event of simultaneous database access. With PostgreSQL MVCC is a given, while with MySQL it depends on the storage engine. In terms of security, MySQL providesTLS encryption. PostgreSQL still uses the older SSL standard.

Administration

An important aspect of working with DBMS is the support of different admin interfaces. Both PostgreSQL and MySQL have a command line interface (CLI) with psql and mysql, respectively. Using the CLI tools, you can connect to the database and execute SQL code via direct input or a script file.

In addition to command line interfaces, PostgreSQL and MySQL have web-based and native graphical user interfaces (GUI). Their dedicated import and export tools allow database backups to be created and restored. PostgreSQL comes with pg_dump and pg_restore and is more powerful in terms of backup than the MySQL backup using MySQL dump.

Admin tool

PostgreSQL

MySQL

CLI client

psql

mysql

Web GUI

phpPgAdmin

phpMyAdmin

Native GUI

pgAdmin

MySQL Workbench

When do you use PostgreSQL vs. MySQL?

By comparing PostgreSQL vs. MySQL, it’s clear that they’re very different, but which of the two database management systems should you be using for your project? Fortunately, the answer is simple. Use PostgreSQL if you have special database requirements. Where that’s not the case, MySQL is sufficient.

In other words, you’d use PostgreSQL to implement the website of a bank or a critical institution. The full ACID compliance pays off here. Greater demand for stability and data consistency justifies the higher complexity of the ORDBMS. Furthermore, sufficient resources are available for a high-performance PostgreSQL environment.

Another application of PostgreSQL is where a project architecture requires the management of sophisticated data models. To map complex object hierarchies or where inheritance is required as a central component of the data model, the use of the powerful ORDBMS is a good option. This may save the use of object-relational mapping (ORM).

For small to medium web projects,MySQL is the better choice. The RDBMS is less demanding in terms of server resources. It’s easier to find an experienced and affordable MySQL admin. Strong performance when reading data means it’s a good option for websites and small online stores.

Lastly, it should be noted that PostgreSQL and MySQL can be used in tandem. This is particularly attractive for data warehousing solutions. Usually, one or more outward-facing MySQL instances are used in such a setup. They collect data and pass it to a central PostgreSQL installation running evaluations and analyses.

Tip

Check out our comparison of MariaDB vs. MySQL.